reading data from a cell to a string

[Bralyan]

I am making a macro, and I need to read a value from a cell, like A1,
and store that value into a string, so I can edit the string or
whatever, thanks guys

 

[JE McGimpsey]

One way:

Dim sStrValue As String
sStrValue = CStr(Range("A1").Value)

 

[Bralyan]

thanks, that was easier than I thought, is there anything special that I
would need to do to paste the string back into a cell? like A2?

 

[JE McGimpsey]

thanks, that was easier than I thought, is there anything special that I
would need to do to paste the string back into a cell? like A2?

When one "enters" a value into a cell via VBA, e.g.:

Range("A2").Value = sStrValue

The value in sStrValue is evaluated the same way it would be if you
entered it manually. So if

sStrValue = "1/3"

will insert

1/3

into A2 using the above assignment, interpret it as a date and the cell
will display

03-Jan

or whatever the default date format is.

To assign the value so that it's interpreted as Text, you need to format
the cell first:

With Range("A2")
.NumberFormat = "@" 'Text
.Value = sStrValue
End With

Likewise, if you wish to use a specific date format, you have to change
the cell's .NumberFormat property, not just the string's format.

 

[Bralyan]

awsome, I have 1 last question, how do you change an int to a string

 

[JE McGimpsey]

One way:

You can do explicit coercion using CStr, but the VBA assign method makes
the coercion automatically:


Dim nInt As Long
Dim sInt As String

nInt = 12345
sInt = nInt
Debug.Print nInt, TypeName(nInt)
Debug.Print sInt, TypeName(sInt)

produces:

12345 Long
12345 String