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
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)
