Andy, your question intrigued me, so I took a look at this and here's how I understand it; The use of the @'s are inconsequential, as it's simply the character Rick chose to use in the Replace function.
Preface: For those unfamiliar with the original question,
here is the post.
To help explain, I'm going to modify the original code for use on a single cell:
Code:
ActiveCell.Value = Evaluate(Replace("IF(@="""","""",""'""&@)", "@", ActiveCell.Address))
The first part of the code to focus on is the IF statement. In the VBA code the IF statement is a literal string:
Code:
ActiveCell.Value = Evaluate(Replace([COLOR=#ff0000]"IF(@="""","""",""'""&@)"[/COLOR], "@", ActiveCell.Address))
This literal string is nested in the VBA Replace function. Assuming the active cell is Q2, the Replace function replaces the @'s and changes the literal IF string into:
Code:
"IF(Q2="""","""",""'""&Q2)"
Technically .Address without any additional parameters would return the absolute reference, $Q$2, but I'm leaving those out to make it easier to read.
If this was used as a formula on the worksheet it would look like this:
Code:
IF(Q2="","","'"&Q2)
In other words, If Q2 is blank then return a blank value, otherwise return a single quote ' and the value of Q2.
So back to VBA, after the Replace function has executed and created the new literal string the code has now changed to:
Code:
Evaluate("IF(Q2="""","""",""'""&Q2)")
Rick's original code is looking at the cell to be changed, creating an IF formula based off of that cell, Evaluating it in the VBA code, and then overwriting the value of the cell with the end result. The end result being a blank "" value or the cells original value preceded by a single quote mark.
Another (inferior) way to write this would be something like this:
Code:
For Each cell In Range("Q2", Cells(Rows.Count, "Q").End(xlUp))
If cell.Value = "" Then
'Do nothing
Else
cell.Value = "'" & cell.Value
End If
Next
However, running it this way is much slower than Rick's code. My guess is because it has to cycle through the code for each cell, which was part of the OP's problem. I thought Evaluate may have been the key to Rick's code, but I think it's the vehicle needed in order to use the With method, and the combination of the two is why it works so well.
I still consider myself a novice, and I rarely use With statements or Evaluate, so I can't say for sure why his code is more optimized than a traditional loop. I will however try this With & Evaluate trick in the future!
-Grummet