Solved: Absolute Row, Relative Column Refernce VBA

[chadwe]

I need to use an absolute row reference and a relative column reference in my VBA code.

I go to specified rows within a worksheet and perform the following IF statement in 12 cells in the row (performs IF statement, goes to one cell to the right until 12th cell to right) then the active cell becomes a cell in the next row.

Now ActiveCell.Offset(-8, 0) in my IF statement is pointing the wrong row. I want my code to always point to the same row. Any help with this would be appreciated.


If InServiceDate < ActiveCell.Offset(-8, 0) Then

ActiveCell = AssetAmount / DepreciationMonths

Else ActiveCell = 0

End If

[mdmackillop]

Rather than using the active cell, create a range object and refer to that.
It could be set to the active cell initially, if that is how your macro is run.


Sub NextCell()
Dim Cel As Range
Set Cel = Range("C3")
'or
'Set Cel = ActiveCell
For i = 0 To 11
If InServiceDate < Cel.Offset(-8, i) Then
Cel.Offset(-8, i) = AssetAmount / DepreciationMonths
End If
Next
End Sub

[p45cal]

not sure if I've understood correctly but:myRow = ActiveCell.Offset(-8).Row 'do this while active cell is on first row
'move your active cell
If InServiceDate < Cells(myRow, ActiveCell.Column) Then
ActiveCell = AssetAmount / DepreciationMonths
Else
ActiveCell = 0
End If
?

[mdmackillop]

Hi Chadwe,
A small sample file might make things clearer. Use Manage Attachments in the Go Advanced reply section.
Welcome to VBAX
Regards
MD

[chadwe]

YES!!!!!!!!!!!!

p45cal, I used your suggestion and it worked.

p45cal and mdmackillop, THANK YOU SO MUCH!