ExcelBanter - Using a cell located with the find function

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Using a cell located with the find function (https://www.excelbanter.com/excel-programming/416485-using-cell-located-find-function.html)

jenVBA

Using a cell located with the find function

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

Bernie Deitrick

Using a cell located with the find function

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

jenVBA

Using a cell located with the find function

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

"Bernie Deitrick" wrote:

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

Bernie Deitrick

Using a cell located with the find function

Jen,

It depends :-)

It depends on if myC is the lower right cell, and if you just want to use
the column of myC

SourceData:= "Data!R4C5:R25000C" & myC.Column

Or if you want to use both the column and the row:

SourceData:= "Data!R4C5:" & myC.Address(True, True, xlR1C1)

And if myC is the upper Left cell, and you just want to use the column of
myC

SourceData:= "Data!R4C" & myC.Column & ":R25000C10"

Of use both the column and the row:

SourceData:= "Data!" & myC.Address(True, True, xlR1C1) & ":R25000C10"

But if you know both the upper left and lower right cells, then you could
use different code.....

HTH,
Bernie
MS Excel MVP

"jenVBA" wrote in message
...
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

"Bernie Deitrick" wrote:

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

jenVBA

Using a cell located with the find function

*gulp*
I'll give it a go this afternoon and let you know how I get on!

"Bernie Deitrick" wrote:

Jen,

It depends :-)

It depends on if myC is the lower right cell, and if you just want to use
the column of myC

SourceData:= "Data!R4C5:R25000C" & myC.Column

Or if you want to use both the column and the row:

SourceData:= "Data!R4C5:" & myC.Address(True, True, xlR1C1)

And if myC is the upper Left cell, and you just want to use the column of
myC

SourceData:= "Data!R4C" & myC.Column & ":R25000C10"

Of use both the column and the row:

SourceData:= "Data!" & myC.Address(True, True, xlR1C1) & ":R25000C10"

But if you know both the upper left and lower right cells, then you could
use different code.....

HTH,
Bernie
MS Excel MVP

"jenVBA" wrote in message
...
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

"Bernie Deitrick" wrote:

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

jenVBA

Using a cell located with the find function

Bernie.
You are the man! This works perfectly.
Cheers!! Jen

"Bernie Deitrick" wrote:

Jen,

It depends :-)

It depends on if myC is the lower right cell, and if you just want to use
the column of myC

SourceData:= "Data!R4C5:R25000C" & myC.Column

Or if you want to use both the column and the row:

SourceData:= "Data!R4C5:" & myC.Address(True, True, xlR1C1)

And if myC is the upper Left cell, and you just want to use the column of
myC

SourceData:= "Data!R4C" & myC.Column & ":R25000C10"

Of use both the column and the row:

SourceData:= "Data!" & myC.Address(True, True, xlR1C1) & ":R25000C10"

But if you know both the upper left and lower right cells, then you could
use different code.....

HTH,
Bernie
MS Excel MVP

"jenVBA" wrote in message
...
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

"Bernie Deitrick" wrote:

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

All times are GMT +1. The time now is 03:02 PM.