Unable to get the HLookup property of the WorkbookFunction class

[Firasath]

Hi,

I am trying to build a code to assign a Comment to cell range based on a formula result. I am getting the subject error whenever I run the code. I am week in building codes, I tried my level best but doesn't able to figure out the reason for the same. Require your assistance, please. Please find below the code and the excel template for your reference.

- Trying to insert KPI Target in SLA Adherence based on formula result. Formula result is there in 2nd row for your reference.

Sub AddComment()

Dim i As Long, rng As range, StartCell As range, StringCmt As Long

Set rng = range("b3")
Set StartCell = rng

'On Error Resume Next

    For i = 1 To 10
    StringCmt = Application.WorksheetFunction.HLookup(rng, range("$K$11:$N$12"), 2)
                 rng.Offset(0, i - 1).AddComment StringCmt
    Next i
'On Error GoTo 0
StartCell.Select
 
End Sub

Book12.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
2	Target	90%	90%	95%	95%	95%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%
3	SLA Adherence	88.0%	90.0%	93.0%	94.0%	95.0%	97.0%	96.0%	88.0%	96.0%	97.0%	99.0%	88.0%	90.0%	95.0%	97.5%	98.9%	99.3%	100.0%	100.0%
8
9
10
11											Month	Jun-20	Aug-20	Nov-20
12											Target	90%	95%	97%
13
Sheet1

Cell Formulas
Range	Formula
B2:T2	B2	=HLOOKUP(B1,$L$11:$T$12,2)

 

[Saurabhj]

Hi,

If you are getting Run Time error it's because the HLOOKUP function didn't find lookup value in the given range.

In the macro lookup value is B3 cell while in sheet the lookup value is cell B1.

Thanks,
Saurabh

 

[Firasath]

Hi Saurabh,

Thanks for your reply. Identified the error and revised the code. But I am getting error "Application defined or Object defined error". Please assist me in correcting the below code.

Dim i As Integer, rng As range, StartCell As range, Cmt As Variant, dt As range

Set rng = range("b3")
Set StartCell = rng
Set dt = range("b1")

'On Error Resume Next

    For i = 1 To 10
    
                Cmt = Application.WorksheetFunction.HLookup(dt.Offset(0, i - 1), range("$K$11:$N$12"), 2)
                
                rng.Offset(0, i - 1).AddComment Cmt
              
    Next i
'On Error GoTo 0

StartCell.Select
  
End Sub

 

[Fluff]

Duplicate to: Invalid Procedue call or Argument

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.