VBA question for a beginner

5 pts.
Tags:
VBA
I am very new to VBA and my boss is having work on a spreadsheet that dynamically updates the comment box with a value from a designated cell. Here is what I am using for the first target address of C39 to be displayed in a text box in C5. Problem is I have to do it for 140 cells. I don’t know the best way to write this code. My next target is C40 with comment cell of C6 and continues on. Private Sub Worksheet_Change(ByVal Target As Range) mystr = Format(5459.4, "##,##0.00") If Target.Address = "$C$39" Then On Error Resume Next ActiveSheet.Range("C5").Comment.Text Text:="MTD Volume Var " & Chr(10) & ActiveSheet.Range("C67").Value If Err.Number = 91 Then ActiveSheet.Range("C5").AddComment Text:="Cell value is " & Chr(10) & ActiveSheet.Range("C5").Value End If End Sub Private Sub Worksheet_Calculate() End Sub

Answer Wiki

Thanks. We'll let you know when a new response is added.

If this is consitent where C39 displays in C5 and C40 in C6 and C41 in C7 etc. then you can use a loop.
I don’t know what Cell C67 is in your code, but if you meant to say C39, then this will work.

Dim x as integer, y as integer
On Error Resume Next
y = 5
For x = 39 to 140
ActiveSheet.Range(“C” & y).Comment.Text Text:=”MTD Volume Var ” & Chr(10) & ActiveSheet.Range (“C” & x).Value
If Err.Number = 91 Then ActiveSheet.Range(“C” & y).AddComment Text:=”Cell value is ” & Chr(10) & ActiveSheet.Range(“C” & y).Value
y=y+1
Next x

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Schmidtw
    Perhaps I am just looking at your code wrong above, but I'm quite sure you need to iterate through the x variable declared in the for loop. -Hope this helps! -Schmidtw
    11,330 pointsBadges:
    report
  • Flame
    This site has several good tutorials if you want to beef up your VBA skills in Excel. This site is one you might find helpful as well. Good Luck! -Flame
    14,925 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following