We help IT Professionals succeed at work.

VBA IF Statement

Seamus2626
Seamus2626 asked
on
450 Views
Last Modified: 2012-08-14
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
Comment
Watch Question

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?

Author

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

The equivalent of rightclicking and pressing "Insert Comment"

Thanks
Seamus
Sorry, I should clarify my previous question: Where do you want the comments? An embedded comment in the cell, or in a different cell?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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

Author

Commented:
That worked a treat Stephen,

Thanks
Seamus

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.