Concatenate VBA results and an * in a cell

Hi,

I would like to concatenate the results of copying a cell and adding an * to left of the number value, in the same cell.

For example, it would look like Range("B1").Value = "*" + Range("A1").Value .
But it does not work. For sure, trying to mathematically sum a String and a number isn't a good idea in the form I've just written. So I imagine I would have to convert the number into String. How, I'd like too know. But then, if I want to use again the value in B1 (so removing the * in VBA and do maths with those numbers), how could I reverse this process?

Thx!

Werner

Hi Werner


Instead of Range("B1").Value = "*" + Range("A1").Value . Try Range("A1") = Range("B1").Value & Range("A1").Value . (where Range("B1").Value = "*"). The & is the equivalent to Concatenate in VBA.

to Remove Later the "*" Use

Please Login or Register  to view this content.

Check in the Help for a detailed explaination of the Right and Len Functions

Hope this helps you

Saludos

Jose Luis

Originally Posted by Werner

Hi,

I would like to concatenate the results of copying a cell and adding an * to left of the number value, in the same cell.

For example, it would look like Range("B1").Value = "*" + Range("A1").Value .
But it does not work. For sure, trying to mathematically sum a String and a number isn't a good idea in the form I've just written. So I imagine I would have to convert the number into String. How, I'd like too know. But then, if I want to use again the value in B1 (so removing the * in VBA and do maths with those numbers), how could I reverse this process?

Thx!

Werner

Hi Werner, Thanks by your feedback.

In order to find if there is a * in the string use

if Left(Range("B1").value,1) = "*" then
your code for number with "*"
Else
your code for number without "*"
End if


The Left function does the same as Right but from the other side .


Hope this helps,

Regards

Jose Luis

Originally Posted by Werner

Thank you Jose Luis for your code. I understand Len and Right correctly with the comments you added in your code. It will help me a lot.

I have another question in mind. If I want to add in my vba code an If condition that search to know if there's a * just to the left of my number, how could I do that. I imagine it must be something like If Range("B1").Value = "*" & *(something that say there's a number (not a specific one)) Then.

Have a good day!

Werner

Thanks again Jose, it helped a lot. It was precisely what I wanted to know. I fully understand the way Left and Right functions work now

Have a good day!

Werner