-
Solved: Absolute Row, Relative Column Refernce VBA
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.
[VBA]
If InServiceDate < ActiveCell.Offset(-8, 0) Then
ActiveCell = AssetAmount / DepreciationMonths
Else ActiveCell = 0
End If
[/VBA]
-
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.
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
not sure if I've understood correctly but:[VBA]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
[/VBA]?
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
Hi Chadwe,
A small sample file might make things clearer. Use Manage Attachments in the Go Advanced reply section.
Welcome to VBAX
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
YES!!!!!!!!!!!!
p45cal, I used your suggestion and it worked.
p45cal and mdmackillop, THANK YOU SO MUCH!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules