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

 

[Firasath]

It is a the result of hkookup formula. In destination table it is updated in percentage format, which would be number (Integer)

 

[maabadi]

AND I think you should Dim it at Double because Percents has numbers after point.

 

[Firasath]

I even tried that. Can you please copy my excel sheet from above and try the code. I copied ot above. Appreciate your support.

 

[maabadi]

This worked If before don't has comments.

Sub AddComment1()
Dim i As Integer, rng As Range, dt As Range, Cmt As String
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 Cmt
Next i
'On Error GoTo 0
rng.Select
End Sub

 

[Firasath]

Thank you very much brother, it worked. I tried all data types myself but it didn't work, maybe I made some other mistake. Thanks again .

 

[maabadi]

Are you sure you don't have any comments at cells Before running this code with dim as string.
What about this:

Sub AddComment1()
Dim i As Integer, rng As Range, dt As Range, Cmt As String
Set rng = Range("b3")
Set dt = Range("b1")
'On Error Resume Next
Range("B3:T3").ClearComments
For i = 1 To 10
   Cmt = Application.WorksheetFunction.HLookup(dt.Offset(0, i - 1), Range("K11:N12"), 2)
         rng.Offset(0, i - 1).AddComment Cmt
Next i
'On Error GoTo 0
rng.Select
End Sub

 

[Firasath]

Might be, I was trying many different ways at that time. So their might be comments at that time. Thanks.