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").AdvancedFilter 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!

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" <YoungGuy.21wsiz_1137719701.7008@excelforum-nospam.com> wrote in
message news:YoungGuy.21wsiz_1137719701.7008@excelforum-nospam.com...
>
> 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("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
>

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("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!!!

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" <YoungGuy.249zfb_1141694402.3582@excelforum-nospam.com> wrote in
message news:YoungGuy.249zfb_1141694402.3582@excelforum-nospam.com...
>
> 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("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
>

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