Invalid Procedue call or Argument

[Firasath]

Hi,

Can somebody please advise why I am getting Run Time Error 5 while running below simple code. When I run the same code with a single cell it works fine but not with Range.

Sub test()

Range("A24:T24").AddComment ("Test")

End Sub

 

[maabadi]

You can Use

Sub test()Dim j as long
For j=1 to 20
Cells(24,  j).AddComment ("Test")
Next j
End Sub

 

[Firasath]

Thank you very much maabadi.

I would like to know the reason for that code not working. Does the AddComment doesn't work with range of cells?

Actually I am trying to build a code to add comments to KPIs based on the formula (hlookup) result. It is a big database with number of different KPIs. I want to use name references instead of cell references to avoid any manual work on codes on inserting any new rows. I will attach xl2bb sheet below for your kind reference. Appreciate if you can support on me on this as I am struggling or long time now.

Comments to be added to SLA Adherence KPI. Target row is the target result of hlookup which needs to be updated in SLA Adherence columns' comments.

Book12.xlsm
	H
12
Sheet1

First time trying to attach xl2bb. Not sure how it would work.

 

[maabadi]

First select the range you want upload then Click on MiniSheet and accept popup
Then Paste Here to we see what you want.

 

[Firasath]

Please find below.

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%
8
9
10
11											Month	Jun-20	Aug-20	Nov-20
12											Target	90%	95%	97%
13
14
Sheet1

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:T3	Expression	=B3<HLOOKUP(B1,$L$11:$S$12,2)	text	NO

 

[maabadi]

What about this for row 3:

Sub test()
Dim j as long
For j=1 to 20
if Cells(3, j).value < Cells(2, j).value then
Cells(3,  j).AddComment ("Test")
End if
Next j
End Sub

 

[Firasath]

I am trying below code, can you help me why I am getting error "Application-defined or object-defined error".

Sub AddComment1()

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

 

[maabadi]

Yu don't need add $ sign at VBA window, at usual way, remove them.

 

[Firasath]

Thanks. Removed it and it worked by specifying a text as a comment but when I am trying to put "Cmt Variable" to update the corresponding hlookup result in cell, it is still giving same error. What could be the reason for it.

Error: Application-defined or object-defined error

Sub AddComment1()

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

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

'On Error Resume Next

    For i = 1 To 10
    
                Cmt = Application.WorksheetFunction.HLookup(dt.Offset(0, i - 1), range("K11:N12"), 2)
                
                rng.Offset(0, i - 1).AddComment [B]Cmt[/B]
              
    Next i
'On Error GoTo 0

rng.Select
  
End Sub

 

[maabadi]

What means Cmt Variable?
Is it Range? Text? Values?