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" wrote in message
...
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:=xlDatab ase, SourceData:= _
"Data!R4C5:R25000C10").CreatePivotTable TableDestination:="Pivot3!R1C1",
TableName:= _
"WeekMod", DefaultVersion:=xlPivotTableVersion10

Sheets("Pivot3").Select
With ActiveSheet.PivotTables("WeekMod").PivotFields("We ek 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("Mo del")
.Orientation = xlColumnField
.Position = 1
End With
End If