VBA question for a beginner
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
Software/Hardware used:
Software/Hardware used:
ASKED:
September 24, 2008 9:43 PM
UPDATED:
September 30, 2008 1:22 AM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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
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