Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference in VBA
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference in VBA
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 On Sun, 20 Sep 2009 10:22:01 -0700, 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference in VBA
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference in VBA
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference in VBA
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" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference in VBA
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. Tami wrote: 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" wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
absolute cell reference A spreadsheet cell reference that does no | Excel Discussion (Misc queries) | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |