Getting range right in VBA statement

[levy8450]

Help!

The following statement works fine:
lookupval = Application.WorksheetFunction.VLookup(Company, Workbooks(main).Worksheets("Comp").Range("A1:G890"), 1)

However, this equivalent statement produces an error:
lookupval = Application.WorksheetFunction.VLookup(Company, Workbooks(main).Worksheets("Comp").Range(Cells(1,1),Cells(890,7)), 1)

Since the size of the array matrix gets calculated on the fly, I need to refer to columns by number, not by letter - that's why I need the second form to work.

Suggestions?

Thanks.

 

[JoeMo]

There is no apparent difference between the two statements you have posted. Check that you have no typos in the actual code.

 

[levy8450]

I've checked it several times. THe first one works, the second gets this error message:

Run time error '1004'

Application-defined or object-defined error

I can't think of what is wrong!

 

[Ruddles]

In the first one you use a single string as the range name; in the second you're using two values (the contents of cells A1 and G890) separated by a comma.

In other words, in the first you use "A1" followed by a colon followed by "G890". That makes "A1:G890" which is a valid range name. In the second you use the contents of cell A1 and the contents of G890 separated by a comma (i.e. two values). Only you can know what that makes as I can't see the contents of A1 and G890.

I'm sure you can use .Resize somehow to do this but it's not a technique I know much about... something like Range("A1").Resize(890,7).

 

[levy8450]

Well, I don't think the Cells command is referring to the content of the cell.

For instance, this function works fine:
ActiveSheet.Range(Cells(1, 1), Cells(outputrow, totalcols)).Sort Key1:=Cells(1, totalcols), Order1:=xlAscending

In this case, the variable "outputrow" is just a number, as is the variable "totalcols." Say each has the value "10", This function uses the range from cell 1,1 (A1) to cell 10,10 (J10). It works just fine.

That's why I don't understand why my second form is not working.

I can look into the resize command, but I've never used it.

Thanks for looking into it!

 

[Ruddles]

Well, I don't think the Cells command is referring to the content of the cell.

I stand corrected! I couldn't see how Cells(1,1) could return anything other than the value in Cells(1,1) but Excel obviously knows to expect a range in that position and not a value.

Let me have another go at this...

How about: you're not qualifying Cells(1,1) and Cells(890,7), so it's taking them from ActiveSheet rather than from Workbooks(main).Worksheets("Comp").

Try:-

With Workbooks(main).Worksheets("Comp")
lookupval = Application.WorksheetFunction.VLookup(Company, [SIZE=4][COLOR=red][B].[/B][/COLOR][/SIZE]Range([SIZE=4][COLOR=red][B].[/B][/COLOR][/SIZE]Cells(1,1),[B][SIZE=4][COLOR=red].[/COLOR][/SIZE][/B]Cells(890,7)), 1)
End With

 

[levy8450]

I stand corrected! I couldn't see how Cells(1,1) could return anything other than the value in Cells(1,1) but Excel obviously knows to expect a range in that position and not a value.

Let me have another go at this...

How about: you're not qualifying Cells(1,1) and Cells(890,7), so it's taking them from ActiveSheet rather than from Workbooks(main).Worksheets("Comp").

Try:-

With Workbooks(main).Worksheets("Comp")
lookupval = Application.WorksheetFunction.VLookup(Company, [SIZE=4][COLOR=red][B].[/B][/COLOR][/SIZE]Range([SIZE=4][COLOR=red][B].[/B][/COLOR][/SIZE]Cells(1,1),[B][SIZE=4][COLOR=red].[/COLOR][/SIZE][/B]Cells(890,7)), 1)
End With

You are absolutely, 100% right!

Thank you so much. I was already working on a very clunky work-around, now I can do it correctly!

 

[Ruddles]

That's good news... I can stop guessing now!