Cell reference in VBA

[Tami]

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?

 

[Gord Dibben]

Excel doesn't like "data" as a named range.

This will work...........

Sub test()
If Range("myname") = "000" Then
MsgBox "hello"
End If
End Sub

Or this construct..............

Set r = Range("myname")
If r.Cells = "000" Then
MsgBox "hello"
End If


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Try

if range("Data").value = "000" then
(if it's really a string equal to 000)

or
if range("Data").value = 0 then
if it's really the number 0 that's formatted to show 3 digits)

 

[Tami]

thanks Gord & Dave
a somewhat related question as i am trying to clean up my vba so that when i
insert or delete columns, my macros don't get messed up by referring to
specific columns:

I have these 2 lines of code in one of my macros

lr = ActiveCell.Row
Range("t" & lr).Select

so the Tis referring to specific column T but that could change to "U" if i
nsert a column...how do i make this code flexible?
tami

 

[Dave Peterson]

You could give T1 a nice name (Insert|Name|define in xl2003 menus).

Then you could use:

with activesheet 'I like to qualify my ranges!
.cells(lr, .range("nicenamehere").column).select
selection.value = "hi there"
end with

But most things you do in code don't need you to select the range first.

You can act on it directly:

with activesheet 'I like to qualify my ranges!
.cells(lr, .range("nicenamehere").column).value = "hi there"
end with

If you don't want to use a name, maybe you could search for a unique string in
the header (row 1???). Then use that found cell's column number.

dim FoundCell as range
with activesheet
with .rows(1) 'whatever row you want to search
set foundcell = .cells.find(What:="SomeUniqueString", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
msgbox "Design error!"
exit sub
end if

.cells(lr, foundcell.column).value = "hi there again!"
end with

 

[Tami]

worked! thanks much Dave (i did have to take the . out from the .range...but
it seems to be working)
That latter suggestion made my brain hurt...you're giving me far too much
credit in VBA abilities:-|

 

[Dave Peterson]

If the range you named was on the activesheet, then you shouldn't have to remove
that dot.

I bet you didn't use the With/End With structure.

It's always a good idea to qualify your ranges.

worked! thanks much Dave (i did have to take the . out from the .range...but
it seems to be working)
That latter suggestion made my brain hurt...you're giving me far too much
credit in VBA abilities:-|

 

[JLatham]

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.