GasMeterGuy wrote (emphasis added): ``I have data in cell A1 and B1. A1 contains the number 11. B1 contains the number 21. What I would like to accomplish via VBA is put the numbers of these two cells in another cell such that the third cell would show "1121". Is there a method for doing something like this?``
Sorry. I keyed off some other responses and overlooked the fact that you are looking for a VBA solution. If you want to modify another cell, presumably you are writing this in a sub(routine), not a function. Then you can write simply:
Range("c1") = Range("a1") & Range("b1")
And in case you used the Custom format 00 to display a leading zero, if any, you could write:
Range("c1") = Range("a1").Text & Range("b1").Text
There is no need to convert to a number explicitly. Excel recognizes the text as a number, just as it does when we enter it manually. However, if there is any chance that C1 might be formatted as Text beforehand, it would be prudent to write:
Range("c1").ClearFormats
Range("c1") = Range("a1") & Range("b1")
or something similar.
VBA also auto-converts the concatenated string to a number when assigning to a VBA numeric variable, e.g:
Dim x As Long
x = Range("a1") & Range("b1")
But if you neglect to assign a type to x (not a good idea) -- that is, it is Variant -- x would become type String in that case. (Technically, a Variant containing a String.) Then you could use the double-negative trick, to wit:
x = --(Range("a1") & Range("b1"))
Again, for future reference, Excel questions are best answered in the Microsoft Answers Forum for Office Excel.