Log In
Register
Home
Active Member
September 9, 2022
Offline
Hi all,
My problem is, that my code is too long, as I wanted to crate some calculates in the comments.
I need to have 4044 cells with a calculated comment
to do that I used the following:
Range("N5").Comment.Text Text:=[AV5].Text
But If I use more that 450 commands, then VBA tell me, that the code is too big.
is it possible to make a variable or string for a column?
My table has a dimension of 337x12 cells (for each month 337 cells)
to short this codes, I can use more makros for each month; eg January f�r column "N" and "AV" etc.
what I like to have should look like:
Sub Makro1()
Range(y"5").Comment.Text Text:=[z"5"].Text
Range(y"6").Comment.Text Text:=[z"6"].Text
...
end sub
Sub Makro2()
dim y as string
dim z as string
y = N
z = AV
call Makro1()
end sub
thank you for your help
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Trias,
You can use this:
Sub Makro2()
dim y as string
dim z as string
y = "N"
z = "AV"
Makro1 y, z ' call and pass variables
end sub
Sub Makro1(ByVal y as string, ByVal z as string)
Range(y & "5").Comment.Text Text:=Range(z & "5").Text
Range(y & "6").Comment.Text Text:=Range(z & "6").Text
...
end sub
Trusted Members
Moderators
November 1, 2018
Offline
If your rows are all next to each other (or even just evenly spaced out) you could also add a loop to makro1:
Sub Makro1(ByVal y as string, ByVal z as string)
Dim n as long
for n = 1 to 337
Cells(n + 4, y).Comment.Text Text:=Cells(n + 4, z).Text
Next n
end sub
Active Member
September 9, 2022
Offline
Thank you all
I changed the code as follow:
Sub Makro1()
Dim kom As Comment
Dim n As Integer
Dim Cell As Object
Selection.SpecialCells(xlCellTypeComments).Select
Selection.ClearComments
Range("M3").Select
n = 1
Do While n < 338
On Error Resume Next
For Each Cell In Selection
If Cell.Value > -1 Then
Set kom = Cell.AddComment
kom.Text Chr(10) & ActiveCell.Rows.Offset(0, 34).Value
End If
Next Cell
ActiveCell.Offset(1, 0).Select
n = n + 1
Loop
End Sub
works fine as I needed - thank you for your inspiration.
with the "if cell > -1 then" code, I can jump over the "not needed" cells, as long as I typed in invisible -1 and block the cell for editing.
1 Guest(s)
