Calculating cell references
 

I have a VisualBasic macro in Excel that copies some data from one data
sheet to another. The code is:

Sheets("Calculations").Range("A4:X200").AdvancedFi lter
Action:=xlFilterCopy, _
CriteriaRange:=Range("A19:A34"), CopyToRange:=Range("C19:D34"),
Unique:= _
False

Now, I would like to replace the .Range("A4:X200") with a calculated
range but I can't seem to figure out how. I have tried things like
..Range(Cells(4,1),Cells(200,24)) but that doesn't seem to work.
Eventually I want to calculate myRow and myColumn and use them in the
Cell reference (e.g., Cells(myRow,myColumn)). Is there any way to do
this?
Thanks!


--
YoungGuy
------------------------------------------------------------------------
YoungGuy's Profile: http://www.excelforum.com/member.php...o&userid=29654
View this thread: http://www.excelforum.com/showthread...hreadid=503153

Calculating cell references
 

Should work, this is right out of help:

Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True
--
Damon Longworth

2006 East Coast Excel User Conference
April 19/21th, 2006
Holiday Inn, Boardwalk
Atlantic City, New Jersey
Early Bird Registration Now Open!!
www.ExcelUserConference.com

2006 UK Excel User Conference
Summer, 2006
London, England


"YoungGuy" wrote in
message ...

I have a VisualBasic macro in Excel that copies some data from one data
sheet to another. The code is:

Sheets("Calculations").Range("A4:X200").AdvancedFi lter
Action:=xlFilterCopy, _
CriteriaRange:=Range("A19:A34"), CopyToRange:=Range("C19:D34"),
Unique:= _
False

Now, I would like to replace the .Range("A4:X200") with a calculated
range but I can't seem to figure out how. I have tried things like
.Range(Cells(4,1),Cells(200,24)) but that doesn't seem to work.
Eventually I want to calculate myRow and myColumn and use them in the
Cell reference (e.g., Cells(myRow,myColumn)). Is there any way to do
this?
Thanks!


--
YoungGuy
------------------------------------------------------------------------
YoungGuy's Profile:
http://www.excelforum.com/member.php...o&userid=29654
View this thread: http://www.excelforum.com/showthread...hreadid=503153

Calculating cell references
 

Since I got no further responses to my query I thought that I would post
it again with some additional information. My original macro, in its
entirety is:

Sub FilterData()
'
Sheets("Calculations").Range("A4:X200").AdvancedFi lter
Action:=xlFilterCopy, _
CriteriaRange:=Range("A19:A34"), CopyToRange:=Range("C19:D34"),
Unique:= _
False

End Sub

This part seems to work fine(!!) but when I try to replace
..Range("A4:X200") with .Range(Cells(4,1),Cells(200,24)) it upchucks
both on WinDoze machines as well as the Mac. Can anyone tell me why
this won't work? The specific message that I get from VB is:

Run-time error '1004'
Application-defined or object-defined error

Help!!!


--
YoungGuy
------------------------------------------------------------------------
YoungGuy's Profile: http://www.excelforum.com/member.php...o&userid=29654
View this thread: http://www.excelforum.com/showthread...hreadid=503153

Calculating cell references
 

You could try specifying the worksheet for "Cells(4,1),Cells(200,24)"

Eg

Range(Sheets("Calculations").Cells(4,1),Sheets("Ca lculations").Cells(200,24))

same goes for all range references really - much safer to be specific since
without doing that your results can be unpredictable depending on the
context in which they're run (ie. which sheet is active)

Tim



"YoungGuy" wrote in
message ...

Since I got no further responses to my query I thought that I would post
it again with some additional information. My original macro, in its
entirety is:

Sub FilterData()
'
Sheets("Calculations").Range("A4:X200").AdvancedFi lter
Action:=xlFilterCopy, _
CriteriaRange:=Range("A19:A34"), CopyToRange:=Range("C19:D34"),
Unique:= _
False

End Sub

This part seems to work fine(!!) but when I try to replace
Range("A4:X200") with .Range(Cells(4,1),Cells(200,24)) it upchucks
both on WinDoze machines as well as the Mac. Can anyone tell me why
this won't work? The specific message that I get from VB is:

Run-time error '1004'
Application-defined or object-defined error

Help!!!


--
YoungGuy
------------------------------------------------------------------------
YoungGuy's Profile:
http://www.excelforum.com/member.php...o&userid=29654
View this thread: http://www.excelforum.com/showthread...hreadid=503153

Calculating cell references
 

Tim,
That seems to do the trick. Thanks for the help!!


--
YoungGuy
------------------------------------------------------------------------
YoungGuy's Profile: http://www.excelforum.com/member.php...o&userid=29654
View this thread: http://www.excelforum.com/showthread...hreadid=503153