Evaluate Concat Formula: Help getting "." delimeters inserted

[The Reaper]

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:

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)

 

[Paul Ked]

Can't you use single quotes?

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

 

[Takae]

The "r" has two cells. It must be single cell.

 

[The Reaper]

Can't you use single quotes?

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.

 

[The Reaper]

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).

 

[The Reaper]

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:

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

 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)

 

[Paul Ked]

Tidy!

 

[Fluff]

Another option is

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)