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...
--
HTH...

Jim Thomlinson


"Charlie" wrote:

Try

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

"Russ B" wrote:

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