Hi,

I'm trying to use an hlookup function in vba to search for data in
another open workbook. However, I seem to keep getting errors back as
results. My only problem is that I can only specify the range for the
hlookup as a range from an active cell that I am searching for on the
initial spreadsheet (named RData below)



I am using the below syntax, but it doesn't seem to work (I just get
the error message "unable to get the hlookup property of the worksheet
function class")

One thing I thought it might be is that my range has an activecell
reference in it. I then change spreadsheet and use an activecell
reference on this new spreadsheet, but still use the above range in the
same hlookup - but I'm not sure if this would really matter?

If anyone can see what I am doing wrong it would be gratefully
appreciated or is there an easier way of doing this rather than using
hlookup?

Thanks


Joe








Dim riskd
Dim RiskE As String
RiskE = "ER (Pure)%"


Worksheets("RData").Activate
Range("b7").Select


Do
If ActiveCell = RiskE Then
ActiveCell.Offset(3, 1).Select
Else: ActiveCell.Offset(9, 0).Select
End If
Loop Until ActiveCell = RiskE
ActiveCell.Offset(3, 1).Select

riskd = ActiveCell.Range("A1:AA2").Select


Workbooks("Risk Dec").Activate
Worksheets("Equity").Activate
ActiveCell.Offset(0, 1).Select


ActiveCell = Application.HLookup(ActiveCell.End(xlUp),
riskd, 1, False)


--
jlejehan
------------------------------------------------------------------------
jlejehan's Profile: http://www.excelforum.com/member.php...o&userid=33950
View this thread: http://www.excelforum.com/showthread...hreadid=541137

set riskd = ActiveCell.Range("A1:AA2")

HTH
--
AP

"jlejehan" a écrit
dans le message de news:
...

Hi,

I'm trying to use an hlookup function in vba to search for data in
another open workbook. However, I seem to keep getting errors back as
results. My only problem is that I can only specify the range for the
hlookup as a range from an active cell that I am searching for on the
initial spreadsheet (named RData below)



I am using the below syntax, but it doesn't seem to work (I just get
the error message "unable to get the hlookup property of the worksheet
function class")

One thing I thought it might be is that my range has an activecell
reference in it. I then change spreadsheet and use an activecell
reference on this new spreadsheet, but still use the above range in the
same hlookup - but I'm not sure if this would really matter?

If anyone can see what I am doing wrong it would be gratefully
appreciated or is there an easier way of doing this rather than using
hlookup?

Thanks


Joe








Dim riskd
Dim RiskE As String
RiskE = "ER (Pure)%"


Worksheets("RData").Activate
Range("b7").Select


Do
If ActiveCell = RiskE Then
ActiveCell.Offset(3, 1).Select
Else: ActiveCell.Offset(9, 0).Select
End If
Loop Until ActiveCell = RiskE
ActiveCell.Offset(3, 1).Select

riskd = ActiveCell.Range("A1:AA2").Select


Workbooks("Risk Dec").Activate
Worksheets("Equity").Activate
ActiveCell.Offset(0, 1).Select


ActiveCell = Application.HLookup(ActiveCell.End(xlUp),
riskd, 1, False)


--
jlejehan
------------------------------------------------------------------------
jlejehan's Profile:
http://www.excelforum.com/member.php...o&userid=33950
View this thread: http://www.excelforum.com/showthread...hreadid=541137

thanks for the above - but even doing this I still get the "unable to
get the hlookup property of the worksheet function class" message?

Any other suggestions gratefully received.


--
jlejehan
------------------------------------------------------------------------
jlejehan's Profile: http://www.excelforum.com/member.php...o&userid=33950
View this thread: http://www.excelforum.com/showthread...hreadid=541137