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!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Worksheets("TEST").Range("AL" & i).Value=Worksheets("TEST").Range("AJ" & i).Value & " months (" & Worksheets("TEST").Range("AK" & i).Value & ")"
 

LittleGriff

New Member
Joined
Jan 13, 2012
Messages
11
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Did you Copy & paste that line of code into your routine, or re-type it?
As it works for me
 

LittleGriff

New Member
Joined
Jan 13, 2012
Messages
11
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,011
Messages
5,526,263
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top