Using VBA to Copy Text Box to a cell range

[jgj1988]

I am trying to copy the text string that is in a textbox into a variable range. This is what I have been able to come up with but, I can only get it to copy to the first cell in the range, and then I get blanks for the rest. Can anyone tell me what I'm doing wrong?


row_count = Application.WorksheetFunction.CountA(Columns("E:E"))

For Each tbox In Sheets("Exec Summary").TextBoxes

Range("ah3:ah" & row_count).Value = tbox.Text


Next tbox

 

[SirJB7]

Hi, jgj1988!


Firstly that method will work only if in column E there are no empty cells until last entry, otherwise with the For...Next loop you'll be overwriting previous cell content.


Then I'd tweak the code like this:

-----

[pre]

row_count = Application.WorksheetFunction.CountA(Columns("E:E"))
For Each tbox In Sheets("Exec Summary").TextBoxes
row_count = row_count + 1
Range("ah3:ah" & row_count).Value = tbox.Text
Next tbox

[/pre]
-----


Just advise if any issue.


Regards!

 

[jgj1988]

Thanks SirJB7, that fixed my problem

 

[SirJB7]

Hi, jgj1988!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!