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

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

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

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