Evaluate Concat Formula: Help getting "." delimeters inserted

The Reaper

New Member
Joined
Mar 21, 2012
Messages
14
I have the following already performing the concatenation of the columns I need, and its working flawlessly, however I need to insert a period "." in between each concatenated item (addresses) but I'm new to Evaluate & having difficulty. Every time I try to put & ""."" & it gives me a #Value error.

Any help is appreciated

Here's my code:
Code:
Dim WLDfla as long, WLDperf as long
Dim r as range
WLDperf = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
WLDfla = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row
Set r = Sheet1.Range("A" & WLDperf, "A" & WLDfla)

r.Value = Evaluate(r.Offset(0, 3).Address & "&" & r.Offset(0, 7).Address & "&" & r.Offset(0, 6).Address & "&" & r.Offset(0, 1).Address & "&" & r.Offset(0, 2).Address)
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Can't you use single quotes?

Code:
r.Offset(0, 3).Address & "." & r.Offset(0, 7).Address...
 
Upvote 0
Can't you use single quotes?

Code:
r.Offset(0, 3).Address & "." & r.Offset(0, 7).Address...

Unfortunately no, the way Evaluate is using single quotes is for the "&" where it concatenates with the other cell address, so it doesn't know what to do with a "." as its not applying a decimal to the other cell. The cells being concatenated are text.

I've tried single quotes, double quotes etc. to no avail. I'm new to the Evaluate method & hope someone more well versed can help out. I really like how fast it is working with massive amounts of data.
 
Upvote 0
The evaluate works just fine as the code is written, its when I try to introduce a ""."" inbetween each address it returns an error. The two cells for "r" are the start & end cells (dynamic on rowcount).
 
Upvote 0
I have the following already performing the concatenation of the columns I need, and its working flawlessly, however I need to insert a period "." in between each concatenated item (addresses) but I'm new to Evaluate & having difficulty. Every time I try to put & ""."" & it gives me a #Value error.

Any help is appreciated

Here's my code:
Code:
Dim WLDfla as long, WLDperf as long
Dim r as range
WLDperf = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
WLDfla = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row
Set r = Sheet1.Range("A" & WLDperf, "A" & WLDfla)

r.Value = Evaluate(r.Offset(0, 3).Address & "&" & r.Offset(0, 7).Address & "&" & r.Offset(0, 6).Address & "&" & r.Offset(0, 1).Address & "&" & r.Offset(0, 2).Address)

Well I figured it out, well mostly. There's not a way to input the "." directly into the evaluate formula, however by putting a "." in a cell before the code runs and referencing the address that contains the "." it includes it. Example

Code:
 r.Value = Evaluate(r.Offset(0, 3).Address & "&" [B]Sheet1.Range("A4").Address[/B] & "&" & r.Offset(0, 7).Address & "&" [B]Sheet1.Range("A4").Address [/B]& "&" & r.Offset(0, 6).Address & "&" [B]Sheet1.Range("A4").Address[/B] & "&" & r.Offset(0, 1).Address & "&" [B]Sheet1.Range("A4").Address[/B] & "&" & r.Offset(0, 2).Address)
 
Upvote 0
Another option is
Code:
r.Value = Evaluate(r.Offset(0, 3).Address & "&"".""&" & r.Offset(0, 7).Address & "&"".""&" & r.Offset(0, 6).Address & "&"".""&" & r.Offset(0, 1).Address & "&"".""&" & r.Offset(0, 2).Address)
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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