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

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

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

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