asked on

VBA IF Statement

Hi,

I need a vba statement to loop through colH and if the cell value ="xyz" or "abc" then assign a comment "Low Risk"

I want the code to count colH first as i dont want it to loop through all 65000 odd cells

Thanks
Seamus

Anthony Berenguel

Seamus,

If there are no blank cells in column H this should work:

'start in H1
range("H1").select
do until activecell = empty
  if activecell = "abc" or activecell = "xyz" then
        msgbox "insert comment"
  endif
  activecell.offset(1,0).activate
loop

Open in new window

what kind of comments are you wanting to insert when the cell = abc or zyx?

Seamus2626

ASKER

Instead of a msgbox i would like to insert a comment saying "Low Risk"

The equivalent of rightclicking and pressing "Insert Comment"

Thanks
Seamus

Anthony Berenguel

Sorry, I should clarify my previous question: Where do you want the comments? An embedded comment in the cell, or in a different cell?

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Berenguel

Try this:

Sub insert_comments()
    'start at H1
    Range("H1").Select
    Do Until ActiveCell = Empty
        If ActiveCell = "abc" Or ActiveCell = "xyz" Then
            'insert comment
            ActiveCell.AddComment
            ActiveCell.Comment.Visible = False
            ActiveCell.Comment.Text Text:="low risk"
        End If
        'next cell down
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Open in new window

Seamus2626

ASKER

That worked a treat Stephen,

Thanks
Seamus