Cell reference in VBA
Tami,
On a more general note, one thing I do to help keep the code working when
sheet layout changes are made is to define 'critical' columns and rows as
Public Const values in a module that I usually name "Declarations". I'll
have sections in that module dedicated to each worksheet that needs code to
perform some actions.
While this doesn't actually keep the code functioning immediately after a
sheet layout change, it does make it easier to fix. I can go to the
Declarations module, find the values for the sheet that changed and edit them
to match the new layout, then the code works properly again.
Example:
In my declarations module I might have something like this:
Public Const s1NameCol = "A" ' Names column on sheet 'Sheet1'
Public Const s1FirstDataRow = 4 ' 1st row with data on sheet 'Sheet1'
Then in code somewhere, perhaps I use these constants to help define a range
or check a value, using their names:
(this assumes we know that the ActiveSheet is 'Sheet1')
If ActiveSheet.Range(s1NameCol & Rows.Count).End(xlUp).Row < _
s1FirstDataRow Then
...code to deal with no data on the sheet here
End If
In this example, if a new column A were to be inserted, I'd just change
Public Const s1NameCol = "A" ' Names column on sheet 'Sheet1'
to
Public Const s1NameCol = "B" ' Names column on sheet 'Sheet1'
and by having used s1NameCol in the functional code, things keep working
fine after that.
"Tami" wrote:
i have a line of VBA code that asks if cell O1 is "000" then yada yada yada.
It looks like this:
If Cells(1, 15) = "000" Then
But if i insert a column somewhere to the left of column O, my code still
refers to cell O1 but needs to now refer to cell P1.
I've named the cell "data" and i tried
If Cells("data")="000" Then
but that didnt' work.
any other suggestions?
|