Evaluate instead of loop for 2 column concatenation

[JackDanIce]

Hi,

Have this code:

Sub Add_Column_for_Pivot()

    Dim x   As Long: x = LastRow(wData)
    
    With Range("J1:J" & x)
        .Value = Evaluate("IF(" & .Address & "," & .Offset(, -5).Address & "&" & .Offset(, -7).Address)
    End With

End Sub

Where I want to fill with contatenated values of two columns (Ex & Cx where x is row number), without looping, but above returns #VALUE .

Please can someone correct?

TIA,
Jack

 

[Fluff]

How about

Sub Add_Column_for_Pivot()


    Dim x   As Long: x = lastRow(wData)
    
    With Range("J1:J" & x)
        .Value = Evaluate("IF({1}," & .Offset(, -5).Address & "&" & .Offset(, -7).Address & ")")
    End With


End Sub

 

[Marcelo Branco]

Or you can use

With .Range("J1:J" & x)
    .Formula = "=C1&E1"
    .Value = .Value
End With

M.

 

[JackDanIce]

Thank you @Fluff and @Marcelo Branco

Marcelo, nice and simple, but I'll be re-using this elsewhere with similar offsets J1:J&x was just arbitary to get some code I could adapt.

Fluff, still trying to use Evaluate more often, I believe it's faster and often shorter than loops or onsheet calcs and understand first part of coercing array with true values (hence {1}), but always seem to get next part wrong... though in this case, seems like I was closer, missed out & ")") - will get there!

 

[Fluff]

I sometimes find it easier to write it like
Evaluate("IF({1},@e&@c)")
so I can see the basic formula construct, then replace the @e & @c with the relevant address

 

[JackDanIce]

I use that approach alot in general string concatenation, i.e. Range(x).Formula = Replace("=VLOOKUP(item,A1:B@LR,2,0)", "@LR", LR) where LR is the last row number of column B.

Or for new lines Replace(strVariable, "@NL", vbcrlf), for same reason so I can better "see" the basic formula, now I can try it on something new and hopefully use Evaluate more often - awesome, thanks for helping connect the ideas @Fluff!

 

[Fluff]

You're welcome & thanks for the feedback