Variable Range

[gwandalf]

Hi everyone!!!
First of all thanks in advance to help me with this problem

I think I have a problem with the syntax of the code but I dont understand where and why. I am trying to use the application LookUp, which is working nice. The problem is when I am trying to make my range able to change. I mean that Arg2 and Arg3 can change

THIS CODE IS WORKING without any problem but it is not what I need

Sub Set_New()

Arg2 = MPA_Input.Range("A2:AHK2")
Arg3 = MPA_Input.Range("A3:AHK3")
Arg1 = MPA_Mechanical.Range("T9")

X = Application.Lookup(Arg1, Arg2, Arg3)

End Sub


THIS ONE IS NOT WORKING and I will need something like that
Note that is part of a bigger code and I define before all the variables as Long. When I was debugging the variables has their values.

Sub Set_New()

Arg2 = MPA_Input.Range(Cells(rowIndex_T, startColIndex), Cells(rowIndex_T, endColIndex))
Arg3 = MPA_Input.Range(Cells(rowIndex_S, startColIndex), Cells(rowIndex_T, endColIndex))
Arg1 = MPA_Mechanical.Range("T9")

X = Application.Lookup(Arg1, Arg2, Arg3)

End Sub

But is giving me an error in Arg2 ( I guess will be the same for Arg3 )
Method 'Range' of object'_Worksheet'failed


ANOTHER ONE

I also try with this syntax but is giving me an error but in different poing

Sub Set_New()

Arg2 = "='" & wsheet(1) & "'!R" & rowIndex_Time & "C" & startColIndex & ":R" & rowIndex_Time & "C" & endColIndex & ""
Arg3 = "='" & wsheet(1) & "'!R" & rowIndex_Speed & "C" & startColIndex & ":R" & rowIndex_Speed & "C" & endColIndex & ""
Arg1 = MPA_Mechanical.Range("T9")

X = Application.Lookup(Arg1, Arg2, Arg3)

End Sub

but in this case "X" is giving me ERROR 2015

THANKS TO ALL OF YOU
Any suggestion will be apreciate

 

[Andrew Poulsom]

You need to qualify the Cells property as well as the Range property. Example:

Arg2 = MPA_Input.Range(MPA_Input.Cells(rowIndex_T, startColIndex), MPA_Input.Cells(rowIndex_T, endColIndex)).Value

To save repeating it you can use a With ... End With construct:

With MPA_Input
    Arg2 = .Range(.Cells(rowIndex_T, startColIndex), .Cells(rowIndex_T, endColIndex)).Value
End With

You still need the dot qualifier for both properties.

Edit:

If you want to assign the range to an object variable:

Dim Arg2 As Range
With MPA_Input
    Set Arg2 = .Range(.Cells(rowIndex_T, startColIndex), .Cells(rowIndex_T, endColIndex))
End With

 

[gwandalf]

Thanks. That works