Calculating cell references

[YoungGuy]

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").AdvancedFilter
Action:=xlFilterCopy, _
CriteriaRange:=Range("A19:A34"), CopyToRange:=Range("C1934"),
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!

 

[Damon Longworth]

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]

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").AdvancedFilter
Action:=xlFilterCopy, _
CriteriaRange:=Range("A19:A34"), CopyToRange:=Range("C1934"),
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!!!

 

[Tim Williams]

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

Eg

Range(Sheets("Calculations").Cells(4,1),Sheets("Calculations").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]

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