J
jlejehan
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)
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)