Concordium
New Member
- Joined
- May 19, 2010
- Messages
- 1
I am new to VBA completely and especially as it relates to Excel so I am sure this is something relatively easy to figure out.
I am making a finance spreadsheet that adds comments to cells via macro. These comments contain the date that a particular macro, for that specific subtotal, was executed. My issue is that when VBA gets to the command line to resize the comments it errors out.
FWIW, I use Excel 2007 and my test code is as follows below.
Sub Total_POS()
Dim Query As String
Dim QTotal As Currency
Dim Total As Currency
Total = 0
Worksheets("Defined List").Select
Range("B12").Select
Do Until IsEmpty(ActiveCell)
Query = ActiveCell.Value
Worksheets("Jan '09 - Dec '09").Cells(2, "C").AutoFilter Field:=3, Criteria1:="*" & Query & "*"
QTotal = Worksheets("Jan '09 - Dec '09").Cells(1176, "D").Value
Total = Total + QTotal
ActiveCell.Offset(1, 0).Select
Loop
Worksheets("Jan '09 - Dec '09").Cells(2, "C").AutoFilter Field:=3, Criteria1:="*"
Worksheets("Totals").Select
Range("B4").Select
ActiveCell.Value = (Total)
Range("B4").AddComment
Range("B4").Comment.Visible = True
Range("B4").Comment.Text Text:="Updated on:" & Date & Chr(10) & ""
Selection.Font.Bold = True
Selection.ShapeRange.ScaleWidth 1.32, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft
Range("B4").Select
Columns("A:A").EntireColumn.AutoFit
End Sub
Unfortunately bulletin boards dont like to keep the page formatting.....my apologies. Anyway, any help you guys can give me is greatly appreciated.
I am making a finance spreadsheet that adds comments to cells via macro. These comments contain the date that a particular macro, for that specific subtotal, was executed. My issue is that when VBA gets to the command line to resize the comments it errors out.
FWIW, I use Excel 2007 and my test code is as follows below.
Sub Total_POS()
Dim Query As String
Dim QTotal As Currency
Dim Total As Currency
Total = 0
Worksheets("Defined List").Select
Range("B12").Select
Do Until IsEmpty(ActiveCell)
Query = ActiveCell.Value
Worksheets("Jan '09 - Dec '09").Cells(2, "C").AutoFilter Field:=3, Criteria1:="*" & Query & "*"
QTotal = Worksheets("Jan '09 - Dec '09").Cells(1176, "D").Value
Total = Total + QTotal
ActiveCell.Offset(1, 0).Select
Loop
Worksheets("Jan '09 - Dec '09").Cells(2, "C").AutoFilter Field:=3, Criteria1:="*"
Worksheets("Totals").Select
Range("B4").Select
ActiveCell.Value = (Total)
Range("B4").AddComment
Range("B4").Comment.Visible = True
Range("B4").Comment.Text Text:="Updated on:" & Date & Chr(10) & ""
Selection.Font.Bold = True
Selection.ShapeRange.ScaleWidth 1.32, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft
Range("B4").Select
Columns("A:A").EntireColumn.AutoFit
End Sub
Unfortunately bulletin boards dont like to keep the page formatting.....my apologies. Anyway, any help you guys can give me is greatly appreciated.