Address of the return value of hlookup

[varan]

Hello,
Im trying to obtain the cell address of the value returned by hlookup and then transfer another value to the cell below it. In the code month_match returns a string. I've defined Mat_add as Range. I get [h=1]Application-defined or object-defined error, Office 1004[/h]error code. Wanted to know what is the mistake in my command?

VB:

Month_match = Application.WorksheetFunction.HLookup(Month, Blad1.Range("B8:N9"), 1, False)
Mat_add = Application.WorksheetFunction.Index(ThisWorkbook.ActiveSheet.Range("B9:N9"), Application.WorksheetFunction.Match(Month_match, ThisWorkbook.ActiveSheet.Range("B8:N9"), 0), 1)

 

[XOR LX]

Application.WorksheetFunction.Match(Month_match, ThisWorkbook.ActiveSheet.Range("B8:N9"), 0), 1)

The lookup_array for MATCH needs to refer to a one-dimensional array, e.g. B8:B9 or B9:N9.

Regards

 

[varan]

Hi XOR LX,
I changed it and even then get the same issue.

 

[XOR LX]

I've defined Mat_add as Range.

Although it is true that INDEX is capable of returning a cell reference as well as a cell value, you cannot achieve this simply by declaring Mat_add as a Range.

Try removing this declaration, which should return the cell value, rather than cell reference, and then we can work from there.

Unfortunately there are several more problems with your code, primarily with your use of worksheet functions. For example, in:

Application.WorksheetFunction.Index(ThisWorkbook.ActiveSheet.Range("B9:N9")...

you are passing a range of just a single row (B9:N9) to INDEX, so the only value for the row_num parameter which would not result in an error is 1.

Which means that, unless:

Application.WorksheetFunction.Match(Month_match, ThisWorkbook.ActiveSheet.Range("B8:N9"), 0)

results in a 1, your INDEX makes no sense whatsoever.

I'm afraid that's not the only issue, but I think we need to start with the declaration of Mat_add to begin with.

Regards