Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing column references in a variable
Hello all,
I'm trying to store column references in a variable as an interger. I want to store it as an interger so I can perform calculations on it and recall it to refer to a column or multiple columns. When I run my code, I get the error message "Run-time error '1004': Application-defined or object-defined error". It looks like it is because I'm trying to refer to columns as intergers and it wants letters. Here is some of my code: Sub Macro5() ' ' Macro5 Macro ' Macro recorded 6/22/2005 by ' Dim intLastCol As Integer Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select Cells.Find(What:="Balance", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate intLastCol = ActiveCell.Column + 1 Columns(intLastCol & ":" & intLastCol + 6).Select 'Rows (intLastRow + 1 & ":" & intLastRow + 4).Select Selection.EntireColumn.Delete Range(intLastCol + 1 & "1").Select Selection.End(xlDown).Select If ActiveCell.Row < 65536 Then MsgBox "Still Data in this column. Please delete more columns.", vbOKOnly, "Error!!!!!!!!" Exit Sub End If I get the error message at "Columns(intLastCol & ":" & intLastCol + 6).Select". The commented line below it is a working one from another module that I have there for reference. Is there any way I can make this work the way I want to? Thanks in advance for any help anyone can provide, Conan Kelly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing column references in a variable
Conan,
I changed the two lines marked by asterisks. See if that meets your needs... '------------------------------- Range("A1").Select Cells.Find(What:="Balance", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).Activate intLastCol = ActiveCell.Column + 1 Range(Columns(intLastCol), Columns(intLastCol + 6)).Select '***** Selection.EntireColumn.Delete Rows(intLastCol + 1 & "1").Select '***** Selection.End(xlDown).Select If ActiveCell.Row < 65536 Then MsgBox "Still Data in this column. Please delete more columns.", _ vbOKOnly, "Error!!!!!!!!" Exit Sub End If '-------------------------- Regards, Jim Cone San Francisco, USA "Conan Kelly" wrote in message ... Hello all, I'm trying to store column references in a variable as an interger. I want to store it as an interger so I can perform calculations on it and recall it to refer to a column or multiple columns. When I run my code, I get the error message "Run-time error '1004': Application-defined or object-defined error". It looks like it is because I'm trying to refer to columns as intergers and it wants letters. Here is some of my code: Sub Macro5() ' ' Macro5 Macro ' Macro recorded 6/22/2005 by ' Dim intLastCol As Integer Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select Cells.Find(What:="Balance", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate intLastCol = ActiveCell.Column + 1 Columns(intLastCol & ":" & intLastCol + 6).Select 'Rows (intLastRow + 1 & ":" & intLastRow + 4).Select Selection.EntireColumn.Delete Range(intLastCol + 1 & "1").Select Selection.End(xlDown).Select If ActiveCell.Row < 65536 Then MsgBox "Still Data in this column. Please delete more columns.", vbOKOnly, "Error!!!!!!!!" Exit Sub End If I get the error message at "Columns(intLastCol & ":" & intLastCol + 6).Select". The commented line below it is a working one from another module that I have there for reference. Is there any way I can make this work the way I want to? Thanks in advance for any help anyone can provide, Conan Kelly |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing column references in a variable
Jim,
Thanks for your help. I have tomorrow off, so I won't be able to try out your changes until Friday. I will let you know how they work out. Thanks again, Conan "Jim Cone" wrote in message ... Conan, I changed the two lines marked by asterisks. See if that meets your needs... '------------------------------- Range("A1").Select Cells.Find(What:="Balance", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).Activate intLastCol = ActiveCell.Column + 1 Range(Columns(intLastCol), Columns(intLastCol + 6)).Select '***** Selection.EntireColumn.Delete Rows(intLastCol + 1 & "1").Select '***** Selection.End(xlDown).Select If ActiveCell.Row < 65536 Then MsgBox "Still Data in this column. Please delete more columns.", _ vbOKOnly, "Error!!!!!!!!" Exit Sub End If '-------------------------- Regards, Jim Cone San Francisco, USA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing column references in a variable
Jim,
Sorry for not getting back to you sooner. I tried your suggestion and it worked just fine. Thanks again for your help, Conan "Jim Cone" wrote in message ... Conan, I changed the two lines marked by asterisks. See if that meets your needs... '------------------------------- Range("A1").Select Cells.Find(What:="Balance", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).Activate intLastCol = ActiveCell.Column + 1 Range(Columns(intLastCol), Columns(intLastCol + 6)).Select '***** Selection.EntireColumn.Delete Rows(intLastCol + 1 & "1").Select '***** Selection.End(xlDown).Select If ActiveCell.Row < 65536 Then MsgBox "Still Data in this column. Please delete more columns.", _ vbOKOnly, "Error!!!!!!!!" Exit Sub End If '-------------------------- Regards, Jim Cone San Francisco, USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Storing a value to variable | Excel Discussion (Misc queries) | |||
Variable Column references | Excel Worksheet Functions | |||
Storing variable in Option.Tag | Excel Programming | |||
Storing a range in a variable | Excel Programming | |||
Storing value in a variable from a cell | Excel Programming |