Re: EvaluatingConcatenating in VBA (and the Row() stuff)!

. Sorry if I am confusingly / mixing up Themesin this Thread!

. I will try to get some of my thoughtsstraight on this. (I might be repeating wot The “Fox” said above, but this is now how it is coming out ofmy brain now that I am beginning to understand it!). I start with a bit ofbasic Evaluate syntax as that can get a bit confusing if you are not careful!!
. We probably all know that, simplified said,the Application.Evaluate Method in VBA gives a way of using the Normal Excelfunctions in VBA. Again very simplifiedthe Evaluate in VBA sort of does wot the = does in a Normal ExcelWorksheet(Spreadsheet). So, for a randomexample, to get the value of B2 into Cell H2:
. In Excel, in cell H2, one would type
=B2
. In VBA code one would write
Sub TestVBA1()

Range("H2").Value= Range("B2").Value

End Sub

( Note in passing : This code isequivalent, that is to say VBA reads it the same:
Sub TestVBA1()
Range("H2").Value =Range("$B$2").Value
End Sub )


… It is veryimportant to note the exact syntax, as it is easy in more complicated stuff toget confused with the number of “
……You type this Evaluate(“ “), and then in the space you type exactlywhat you would in the Spreadsheet cell but omitting the =
………In theparticular code that was giving me trouble to understand, this basic code wastaken a bit further, that is to say the Address of the cell B2 was got by the VBA code Property .Address , in our case this would look like this
Range(“B2”).Address
This code can bewritten within the Evaluate Function. The syntax for this is “ & to get into acode bit and then & “ to get out ofit!, if that makes sense!!
. So the codewould be

SubTestEvaluateVBA1()
Range("H2")= Evaluate("" & Range("B2").Address & "")
End Sub


( Note in passing : This code isequivalent, that is to say VBA reads it the same:
Sub TestEvaluateVBA1()
Range("H2") = Evaluate(" " &Range("B2").Address & " ")
End Sub
This is because nothing is happening in thiscase or read by VBA between the pair of quotes at the start and the end!, ifthat makes sense!?)

. Staying for now with the Syntax Theme, beforeI go on to my main problem. If I wish to get involved now with another simpleexample that involves three cells, B2 and C2 And D2 the values of which I wishto put in that one cell H2, then in an Excel spreadsheet the formula syntax wemay know would be
=B2 & C2& D2
. We must be very careful now with syntax andtry to think about how Excel and VBA are thinking!. We have now 5 things beingdone after the =
. The evaluate function in VBA does allow youto “evaluate” more than one thing within a single evaluate, but just to confuseus, the & is used to link separate things! We need then our 5things within “ “ and linked by a & . Just to help us get a little less confusedlets use & for the VBA and & for the excel Spreadsheet!

…TO NEXT REPLYBECAUSE OF SIZE CONSTRAINTS HERE!