RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 790
- Office Version
- 365
- Platform
- Windows
Hello, trying to wrap my head around Evaluate, one of these formulas was helpfully given by the legendary Fluff:
What this doing is appending the start of each entry in range K with a space, and the end with a comma.
Unfortunately, this does not like strings longer than 255 characters and I have more than a few cells where the string length is quite long.
Also, can someone explain what the {1} means in the if statement? Thanks.
Secondly I'd like to expand this "with range" to also append "Making own way" on to cells in the range, where the equivalent row in column AD has "Making own way" written.
As an example:
VBA Code:
With Range("K2:K" & LastrowPPD)
.Value = Evaluate("if({1},"" ""&" & .Address & "&"","")")
End With
What this doing is appending the start of each entry in range K with a space, and the end with a comma.
Unfortunately, this does not like strings longer than 255 characters and I have more than a few cells where the string length is quite long.
Also, can someone explain what the {1} means in the if statement? Thanks.
Secondly I'd like to expand this "with range" to also append "Making own way" on to cells in the range, where the equivalent row in column AD has "Making own way" written.
As an example:
K | AD | Result: | |
1 | Pickups | ||
2 | Altrincham, Bedford, Chepstow, | Making own way | Altrincham, Bedford, Chepstow, Making own way, |
3 | Birmingham, Solihull, Redditch | Birmingham, Solihull, Redditch | |
4 | Milton Keynes, Newhaven, Oxford, | Making own way | Milton Keynes, Newhaven, Oxford, Making own way |