Forum

string as replace f...
 
Notifications
Clear all

string as replace for column

4 Posts
3 Users
0 Reactions
135 Views
(@trias)
Posts: 3
Active Member
Topic starter
 

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

 
Posted : 10/09/2022 1:16 am
(@catalinb)
Posts: 1937
Member Admin
 

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

 

 
Posted : 10/09/2022 6:02 am
(@debaser)
Posts: 838
Member Moderator
 

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

 
Posted : 13/09/2022 4:39 am
(@trias)
Posts: 3
Active Member
Topic starter
 

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.

 
Posted : 15/09/2022 1:47 am
Share: