Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Using a cell located with the find function
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default 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






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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







Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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







Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help me find a circular reference cells located in unknown multita John7100 Excel Discussion (Misc queries) 2 April 30th 10 01:55 AM
function to populate a cell where formula is NOT located Jim Excel Worksheet Functions 6 April 5th 10 08:38 AM
find value of a cell to the right a cell located with max() 82716 New Users to Excel 2 November 20th 09 11:18 PM
on what page a cell is located Keyur Excel Programming 0 April 20th 04 08:14 PM
How to find the path where a .XLA is located Belinda Excel Programming 2 March 2nd 04 06:37 AM


All times are GMT +1. The time now is 10:14 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement