Excel - ActiveCell.Value

Asked By tmarsh740 on 25-Apr-08 11:05 PM
I am assigning a two character string to a variable as follows (Stringy is
the variable I read each record form before breaking it into pieces):

ThisActy = Mid(Stringy, 4, 4)

The record I am reading from has "00  " (two zeros and two blanks) starting
at position 4.  I then try to insert it into a cell in my spreadsheet as

ActiveCell.Value = ThisActy

However when I look at the cell I see a 0 (number, not text) in that cell.
I want it to stay in text form in the spreadsheet.  What am I doing wrong?

JLGWhi replied on 26-Apr-08 12:14 AM
Looks like a built in glitch.  Here is how I handled it.

Sub jl()
st = Mid(Range("B2"), 4, 4)
st = CStr(st)
Range("A2").NumberFormat = "@"
Range("A2") = st
End Sub

The receiving range has to be formatted as text or it will automatically
convert the "00  " to a number which = 0.
Dave Peterson replied on 26-Apr-08 10:18 AM
Have you ever typed
( _ = spacebar)
into a cell.

Excel is very forgiving.  It'll let you do it, but fix it to be just 1234 (a
real number).

Same thing happens when you plop anything into a cell formatted as General

You could use what JLGWhiz suggested:

with activesheet
.range("a1").numberformat = "@" 'text
.range("A1").value = Mid(stringy, 4, 4)
End With

Or just like you use apostrophes when you're typing something into the cell:

activesheet.range("a1").value = "'" & Mid(stringy, 4, 4)


Dave Peterson