May I have help with Range Setting Syntax?

[johnhouk]

I cant figure out how to make this work;

Sub RecordEstamate()
    
    Sheets("Sheet2").Select
    Range("C2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    EstRow = (Application.WorksheetFunction.Lookup(Worksheets(1).Range("B4"), Worksheets(2).Range("A5:A50"), Worksheets(2).Range("H5:H50")))

    [u]Set MyRange = Worksheets("Sheet2").Range("""G" & EstRow & ":G" & EstRow & """")[/u]
   
    Range(MyRange).Select
    ActiveSheet.Paste

End Sub

Any Help would be apriciated.

 

[PHV]

Have you tried this ?
Set MyRange = Worksheets("Sheet2").Range("G" & EstRow)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[johnhouk]

I get error 1004 @
Range(MyRange).Select
[Method 'Range' of object '_Global' failed]

 

[johnhouk]

I fixed it;

Sub RecordEstamate()
    
    Records.Select
    Range("C2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Records.Select
    EstRow = (Application.WorksheetFunction.Lookup(TimeCard.Range("B4"), Records.Range("A5:A50"), Records.Range("H5:H50")))
    [u]Range("G" & EstRow).Select[/u]
    ActiveSheet.Paste

End Sub

I guess there was something wrong with how I was Seting MyRange as Range.
Thanks, you did help!

 

[xlbo]

If you use an object setting ie
set myRange = range("A1")

then you don't need to use the range qualifier as it is already set as a range - using your previous code, instead of
range(myRange).select

you should use

myRange.select

The Range qualifier accepts a STRING as its argument so it is expecting an ADDRESS so to make what you had work, you would need something like:

myRange = range("A1").address
range(myRange).select

Hope this helps

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question