Concatenate VBA results and an * in a cell

[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

 

[jose luis]

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


Code:
--------------------
Size = Len(Range("A1").Text) - 1 ' The Len function returns the lenght of the string minus 1 to eliminate the *

Range("A1") = Right(Range("A1").Value, Size ) 'The right function returns the SIZE characters FROM the right of the string.

or in one line: Range("A1") = Right(Range("A1").Value, Len(Range("A1").Text) - 1)
--------------------


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

Hope this helps you

Saludos

Jose Luis

 

[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

 

[jose luis]

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

 

[Werner]

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


Have a good day!

Werne