Casting Problem in VBA

[Russ B]

See code below. I load the departments collection with string values
(For example 007). When I print out the values in a message box they
are string values (For example 007). But when I execute the statement:
Sheets("Expenses").Range("E3") = CStr(departments(i))
.... the value that gets put into the spreadsheet is NUMERIC (For
example 7). I added the CStr cast to String, but that did not help.
What must I do to update E3 with a String instead of a Numeric?

Thanks,

Russ


Dim departments As New Collection
Dim sitem As String

'Make sure the right worksheet is active
Worksheets("Legend").Activate

' Load all of the Codes in departments collection
' Start at top of list and continue until you hit a blank cell
Sheets("Legend").Range("B3").Select
Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
sitem = ActiveCell.Value
departments.Add (sitem)
ActiveCell.Offset(1, 0).Select
Loop

' TEMPORARY - output the collection count
MsgBox departments.Count

' TEMPORARY - List the contents of the collection.
For i = 1 To departments.Count
MsgBox departments(i)
Next

'Loop through all departments, filling in the department number
' on the Expenses sheet. Recalculate spreadsheet and print.
For i = 1 To departments.Count
Sheets("Expenses").Range("E3") = CStr(departments(i))
Calculate
With Worksheets("Sheet1").PageSetup
.CenterHorizontally = True
.PrintArea = "$A$1:$T$39"
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
'Worksheets("Expenses").PrintOut
Next

 

[MDW]

One way (a pretty heavy-handed one, but if you're porting thise to, for
example, Access it'll be worth it) is to precede the value with an apostrophe.

Sheets("Expenses").Range("E3").Value = "'" & CStr(departments(i))

 

[Jim Thomlinson]

Excel will do an implicit conversion of the value when you add it. That is
why it is a number, instead of text. To avoid this, format the cell as text
prior to adding the value, something like this...

with Sheets("Expenses").Range("E3")
.numberformat = "@"
.value = CStr(departments(i))
end with

 

[Charlie]

Try

Sheets("Expenses").Range("E3") = departments(i)
Sheets("Expenses").Range("E3").NumberFormat = "@"

 

[Jim Thomlinson]

If I am correct I beleive you need those statements to be the other way
around. First make the cell text and then add the value. Otherwise when you
put in 007 it is converted to a number 7 and then it is made into a text 7,
and the 00 in the front is lost... If I recall correctly...

 

[Charlie]

How true, I didn't think about that.

If I am correct I beleive you need those statements to be the other way
around. First make the cell text and then add the value. Otherwise when you
put in 007 it is converted to a number 7 and then it is made into a text 7,
and the 00 in the front is lost... If I recall correctly...