Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a Date variable
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)) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a Date variable
Don't declare CellOffset as Range, use Variant or Long
-- HTH RP (remove nothere from the email address if mailing direct) "scott" wrote in message ... 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)) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a Date variable
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 "scott" wrote: 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)) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a Date variable
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 "Jim Thomlinson" wrote in message ... 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 "scott" wrote: 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)) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a Date variable
i got it, thanks.
"Jim Thomlinson" wrote in message ... 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 "scott" wrote: 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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting variable = to sheet name | Excel Programming | |||
VBA Setting .Value to a date does not respect local system setting | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Setting range value to a variable | Excel Programming | |||
Trouble setting variable as filename with date | Excel Programming |