Loop through VBA to join two columns prints properly, but I can't write to my destination column

LittleGriff

New Member
Joined
Jan 13, 2012
Messages
11
I'm trying to join the contents of two columns, and have the new string appear in a third. It looks great when I use Debug.Print, but I can't get the new string to appear in the third column.

Here's my print statement, which works great:

Code:
Debug.Print Worksheets("TEST").Range("AJ" & i).Value; " months (" & Worksheets("TEST").Range("AK" & i).Value; ")"

The Column AJ holds the numeric value, AK holds the phrase that starts with "From." I've been able to add the text and parens properly.

Here's what shows up in Immediate, as I loop through my rows:

6 months (From initial closing)
6 months (From final closing)
5 months (From initial closing)
6 months (From commencement date)

How do I get "AJ & i" plus "AK & i" into the third cell, "AL and i"?

Thank you!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
How about
Code:
Worksheets("TEST").Range("AL" & i).Value=Worksheets("TEST").Range("AJ" & i).Value & " months (" & Worksheets("TEST").Range("AK" & i).Value & ")"
 
Upvote 0
I get the same result, a compile error that highlights the closing ")" with the message "Expected end of statement". If l leave the semicolons after each Value, the first sem is highlighted, and when I remove them as you suggest, I get the same error with the last three marks highlighted. Maddening!
 
Upvote 0
Did you Copy & paste that line of code into your routine, or re-type it?
As it works for me
 
Upvote 0
Ugh, you are right! I didn't catch the last ampersand you added. I can't thank you enough. For my own learning's sake, are you able to say why the expression worked in Debug.Print without the ampersand, but the expression needed the ampersand to actually "do the work"?

I flopped around on this for an embarrassingly long time. Thanks again.
 
Upvote 0
When using Debug.Print the ; simply allows you to print more than 1 item at a time. It doesn't actually concatenate the values
To some extent this is shown if you run this
Code:
Sub Chk()
   Debug.Print "xyz", "test", "abc"
   Debug.Print "xyz"; "test"; "abc"
End Sub
Wich will result in
Code:
xyz           test          abc
xyztestabc
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top