Using a cell located with the find function

[jenVBA]

Hi,

I'm currently trying to construct a Pivot Table but the range to use depends
on the location of a certain column header within the sheet (column changes
each time, always the same row). Currently I have a series of 15 If
statements for the various columns the value could be in but I'm sure there
must be an easier way to do this using the "Find" function. (I've put the
current VBA code at the bottom of this message).

Any suggestions as to how this could be improved?

Thanks in advance!! Jen




If Range("J4") = "Week of campaign" Then
'CONSTRUCT THE PIVOT TABLE
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R4C5:R25000C10").CreatePivotTable TableDestination:="Pivot3!R1C1",
TableName:= _
"WeekMod", DefaultVersion:=xlPivotTableVersion10

Sheets("Pivot3").Select
With ActiveSheet.PivotTables("WeekMod").PivotFields("Week of
campaign")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("WeekMod").AddDataField
ActiveSheet.PivotTables("WeekMod" _
).PivotFields("Model"), "Count of Model", xlCount
With ActiveSheet.PivotTables("WeekMod").PivotFields("Model")
.Orientation = xlColumnField
.Position = 1
End With
End If

 

[Bernie Deitrick]

Something like

Dim myC As Range

Set myC = Range("B4:Z4").Find("Week of campaign")

If Not myC Is Nothing Then
MsgBox myC.Address & " has that string."
Else
MsgBox "That string as not found."
Exit Sub
End If

'And then use myC as the range object for further action

HTH,
Bernie
MS Excel MVP

 

[jenVBA]

Thanks Bernie,

That works well to identify the cell with the string but how would I
incorporate myC into the following line of my code to substitute "C10"?

SourceData:= _
"Data!R4C5:R25000C10"

Thanks so much for your help. Jen