Cell reference in VBA
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 wrote:
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" wrote:
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 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?
--
Dave Peterson
--
Dave Peterson
|