PDA

View Full Version : Solved: Reference and format a variable in one expression?



MisterNate
09-30-2010, 08:15 AM
Hi!

I'm trying to use VB to put a formula into a cell that combines text and variables to create a header.

Here's what I've got so far:


Dim StartDate, EndDate as Long

If Cells(4, 10).Value = "" Then
StartDate = Cells(3, 10).Value

Else: StartDate = Cells(4, 10).Value
End If

If Cells(4, 12).Value = "" Then
EndDate = Cells(3, 12).Value

Else: EndDate = Cells(4, 12).Value
End If


If StartDate <> EndDate Then

Cells(1, 1).Value = "Stock Out Report for " & StartDate & " To " & EndDate

Else: Cells(1, 1).Value = "Stock Out Report for " & StartDate

End If


What this returns for me is" "Stock Out Report for (DATE SERIAL) To (DATE SERIAL)" ... So, specifically the problem is that instead of a date, I get the serial number.

How can I format these variables so I get dd-mmm-yy?

Thanks for your help!!

Bob Phillips
09-30-2010, 09:18 AM
Try



Cells(1, 1).Value = "Stock Out Report for " & Format(StartDate, "dd-mmm-yyyy") & " To " & Format(EndDate , "dd-mmm-yyyy")

MisterNate
09-30-2010, 10:15 AM
That worked perfectly!!

Thanks so much!