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.
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.
VBA Code:
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) |