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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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