Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activecell offset value with formula?
Hi,
Thanks for the help in advance, The "ActiveCell.Offset(0, -2).Value" is a date i.e 9/26/05 I need the "Range("L2").Value" to include the workdays function -1. So, the result in L2 would be 9/23/05 WORKDAY("l2",-1) ...???? Sub test() Sheets("Sheet1").Activate If ActiveCell.Column = 4 Then With Sheets("~DATA~") .Range("L2").Value = ActiveCell.Offset(0, -2).Value End With Else: GoTo LineEnd End If End Sub Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activecell offset value with formula?
2 issues
1)Combine Formulas: .Range("L2").Value = Workday(ActiveCell.Offset(0, -2).Value,-1) will put the date for the workday preceeding offset date into L2 2) Not clear on what you want to trigger this and do you always want the answer in "L2" or just the L column PS: You might be looking for the worksheet_Change event where Target.Column = 4 "Dave" wrote: Hi, Thanks for the help in advance, The "ActiveCell.Offset(0, -2).Value" is a date i.e 9/26/05 I need the "Range("L2").Value" to include the workdays function -1. So, the result in L2 would be 9/23/05 WORKDAY("l2",-1) ...???? Sub test() Sheets("Sheet1").Activate If ActiveCell.Column = 4 Then With Sheets("~DATA~") .Range("L2").Value = ActiveCell.Offset(0, -2).Value End With Else: GoTo LineEnd End If End Sub Dave |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activecell offset value with formula?
Thanks for the reply.
I tried to use ..Range("L2").Value = Workday(ActiveCell.Offset(0, -2).Value, -1) I get a Compile Error Sub or Function not defined? The copy procedure is part of a loop? macro below It loops the list of dates plugs the date to "L2" downloads some information from that date, copys back that info and goes to the next date to do the same. I barley know what I'm doing Thanks Sub macro() Do On Error Resume Next Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Sheets("Sheet1").Activate If ActiveCell.Column = 4 Then With Sheets("~DATA~") .Range("L2").Value = Workday(ActiveCell.Offset(0, -2).Value, -1) .Range("K2") = Trim(ActiveCell.Offset(0, -3)) End With Else: GoTo LineEnd End If Sheets("~DATA~").Select (more macro) Sheets("Sheet1").Select ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, -1)) LineEnd: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.DisplayAlerts = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activecell offset value with formula?
ooppss!
I had just switched from cell formulas, need to convert to VBA using "WorksheetFunction." in front of a worksheet function, otherwise get your error message. ..Range("L2").Value = WorksheetFunction.Workday(ActiveCell.Offset(0, -2).Value, -1) (certain functions are not repeated in VBA because you can simply call tehm with the WorksheetFunction prefix) "Dave" wrote: Thanks for the reply. I tried to use ..Range("L2").Value = Workday(ActiveCell.Offset(0, -2).Value, -1) I get a Compile Error Sub or Function not defined? The copy procedure is part of a loop? macro below It loops the list of dates plugs the date to "L2" downloads some information from that date, copys back that info and goes to the next date to do the same. I barley know what I'm doing Thanks Sub macro() Do On Error Resume Next Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Sheets("Sheet1").Activate If ActiveCell.Column = 4 Then With Sheets("~DATA~") .Range("L2").Value = Workday(ActiveCell.Offset(0, -2).Value, -1) .Range("K2") = Trim(ActiveCell.Offset(0, -3)) End With Else: GoTo LineEnd End If Sheets("~DATA~").Select (more macro) Sheets("Sheet1").Select ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, -1)) LineEnd: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.DisplayAlerts = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
activeCell.offset in another worksheet | Excel Programming | |||
Activecell Offset | Excel Programming | |||
ActiveCell.Offset w/ VBA | Excel Programming | |||
activecell offset | Excel Programming |