Setting a Date variable

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

I have a cell A3 that contains the date like "200412" that my code finds
with the OFFSET command. In "CODE 1" below, sDateRange returns "12/1/2004"
as it should. However, "CODE 2" gives me an error if I try to set the
cellOffset variable to the OFFSET cell. I'm just trying to shorten my code.
Is it the data type that's causing a problem?


CODE 1 (Works):
Dim cell As Range, sDateRange As Date

Set cell = FindCell("myword", Sheets(1).Cells)
sDateRange = Right(cell.Offset(0, 2).Value, 2) & "/" & (Left(cell.Offset(0,
2).Value, Len(cell.Offset(0, 2).Value) - 2))


CODE 2 (Doesn't Work):

Dim cell As Range, cellOffset As Range, sDateRange As Date

Set cell = FindCell("myword", Sheets(1).Cells)
cellOffset = cell.Offset(0, 2).Value
sDateRange = Right(cellOffset, 2) & "/" & (Left(cell.Offset(0, 2).Value,
Len(cellOffset) - 2))
 
Don't declare CellOffset as Range, use Variant or Long

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
First thing off the top of my pointy little head is that you are not using
set for celloffset... That being said try this instead...

Dim cell As Range, sDateRange As Date

Set cell = FindCell("myword", Sheets(1).Cells).Offset(0, 2)
sDateRange = Right(cell.value, 2) & "/" & (Left(cell.Value, Len(cell) - 2))

Or something like that...

HTH
 
Consider below code. If sDateRange works fine and returns "12/1/2004", why
does the sMonth varible not set to "12"? It gives no error, but I can't seem
to return the "12" month part as a string to use elsewhere.

Dim cell As Range, cellOffset As Range
Dim sDateRange As Date
Dim sMonth As String

Set cell = FindCell("To Period", Sheets(1).Cells).Offset(0, 2)
sDateRange = Right(cell.Value, 2) & "/" & (Left(cell.Value, Len(cell) -
2))
sMonth = CStr(Right(cell.Value, 2))

MsgBox sMonth
 
i got it, thanks.


Jim Thomlinson said:
First thing off the top of my pointy little head is that you are not using
set for celloffset... That being said try this instead...

Dim cell As Range, sDateRange As Date

Set cell = FindCell("myword", Sheets(1).Cells).Offset(0, 2)
sDateRange = Right(cell.value, 2) & "/" & (Left(cell.Value, Len(cell) -
2))

Or something like that...

HTH
Click to expand...
 
Back
Top