Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.Offset w/ VBA
If IsEmpty(ActiveCell.Offset(0, -1)) Then
ActiveCell.Offset(0, -1).Value = Application.WorksheetFunction.Text(11900, "000000") End If You had misused Range. Range takes something like: Range("A5") Range("G3:G12") etc. (many others) Range(ActiveCell.Offset(0,-1).Value) only would mean something if Activecell.Offset(0,1).Value were something like A5 or G3:G12, etc. Also, you have Active.Cell rather than ActiveCell. Also, instead of Application.WorksheetFunction.Text(11900, "000000"), use "011900", but then you'd have to format the cell anyway! Bob Umlas Excel MVP "green67beanie" wrote in message ... I am searching down a column for a particular value and if found, want to check the cell to the immediate left for being empty. If it is, I want to insert a text value derived from a function, and then continue with a find next. I am getting an error, Run time error "424" Object required, from the following true condition statement: If IsEmpty(ActiveCell.Offset(0, -1)) Then Range(Active.Cell.Offset(0, -1)).Value = Application.WorksheetFunction.Text(11900, "000000") End If I initially tried the following line there, but got the same error: Active.Cell.Offset(0, -1).FormulaR1C1 = Application.WorksheetFunction.Text(11900, "000000") Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.Offset w/ VBA
Thanks, Bob for the speedy response. My initial approach (without using the
Range()), after correcting the Active.Cell to ActiveCell error you pointed out worked fine. "Bob Umlas" wrote: If IsEmpty(ActiveCell.Offset(0, -1)) Then ActiveCell.Offset(0, -1).Value = Application.WorksheetFunction.Text(11900, "000000") End If You had misused Range. Range takes something like: Range("A5") Range("G3:G12") etc. (many others) Range(ActiveCell.Offset(0,-1).Value) only would mean something if Activecell.Offset(0,1).Value were something like A5 or G3:G12, etc. Also, you have Active.Cell rather than ActiveCell. Also, instead of Application.WorksheetFunction.Text(11900, "000000"), use "011900", but then you'd have to format the cell anyway! Bob Umlas Excel MVP "green67beanie" wrote in message ... I am searching down a column for a particular value and if found, want to check the cell to the immediate left for being empty. If it is, I want to insert a text value derived from a function, and then continue with a find next. I am getting an error, Run time error "424" Object required, from the following true condition statement: If IsEmpty(ActiveCell.Offset(0, -1)) Then Range(Active.Cell.Offset(0, -1)).Value = Application.WorksheetFunction.Text(11900, "000000") End If I initially tried the following line there, but got the same error: Active.Cell.Offset(0, -1).FormulaR1C1 = Application.WorksheetFunction.Text(11900, "000000") Any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.Offset w/ VBA
It works fine, it is just slower.
ActiveCell.Offset(0, -1).Value = _ format(11900, "000000") or ActiveCell.Offset(0, -1).Value = _ "011900" Would work the same as your code but would be faster. (although the difference may not be noticeable). -- Regards, Tom Ogilvy "green67beanie" wrote in message ... Thanks, Bob for the speedy response. My initial approach (without using the Range()), after correcting the Active.Cell to ActiveCell error you pointed out worked fine. "Bob Umlas" wrote: If IsEmpty(ActiveCell.Offset(0, -1)) Then ActiveCell.Offset(0, -1).Value = Application.WorksheetFunction.Text(11900, "000000") End If You had misused Range. Range takes something like: Range("A5") Range("G3:G12") etc. (many others) Range(ActiveCell.Offset(0,-1).Value) only would mean something if Activecell.Offset(0,1).Value were something like A5 or G3:G12, etc. Also, you have Active.Cell rather than ActiveCell. Also, instead of Application.WorksheetFunction.Text(11900, "000000"), use "011900", but then you'd have to format the cell anyway! Bob Umlas Excel MVP "green67beanie" wrote in message ... I am searching down a column for a particular value and if found, want to check the cell to the immediate left for being empty. If it is, I want to insert a text value derived from a function, and then continue with a find next. I am getting an error, Run time error "424" Object required, from the following true condition statement: If IsEmpty(ActiveCell.Offset(0, -1)) Then Range(Active.Cell.Offset(0, -1)).Value = Application.WorksheetFunction.Text(11900, "000000") End If I initially tried the following line there, but got the same error: Active.Cell.Offset(0, -1).FormulaR1C1 = Application.WorksheetFunction.Text(11900, "000000") Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveCell.Offset Question | New Users to Excel | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Sum Activecell Offset Problem | Excel Worksheet Functions | |||
activecell offset | Excel Programming |