Trying to pass a string variable inside Column instruction

[Ricardo Caicedo]

I try to pass a string variable in order to select the column where I need to insert a new column (after the header “Email Address”) but it fails run error 1004 as it looks like the object is not defined. I think VBA does not have the concept COLUMNS so it cannot accept a variable inside Columns instruction …..
The think when I did manually the macro, the instruction to select the column (i.e. column H) Columns("H:H"), BUT IT DID NOT WORK IN MY MACRO code.
I will apreciate your help, please see the code down here
Sub FillData()
'
' Creates the columns and compares the data
Dim NColumn As Integer
Dim FindColumn As Range
Dim RColumn As String
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Cells.Find(what:="Email address", After:=ActiveCell, LookIn:=xlFormulas, lookat _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Set FindColumn = Cells.Find(what:="Email address", After:=ActiveCell, LookIn:=xlFormulas, lookat _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
NColumn = FindColumn.Column
NColumn = NColumn + 1
RColumn = NColumn & ":" & NColumn
Columns(RColumn).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

 

[Snakehips]

Ricardo Caicedo,

Try this...

Sub FillData()
' Creates the columns and compares the data
Dim NColumn As Integer
Dim FindColumn As Range
'Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Cells.Find(what:="Email address", After:=ActiveCell, LookIn:=xlFormulas, lookat _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Set FindColumn = Cells.Find(what:="Email address", After:=ActiveCell, LookIn:=xlFormulas, lookat _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
NColumn = FindColumn.Column + 1
Columns(NColumn).Insert
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

Hope that helps.

 

[Ricardo Caicedo]

Thank you snakehips! it works

 

[Snakehips]

You are welcome.

Just noticed that I left an unnecessary 'find' in there, from your original code.

So can reduce to the below if you wish.

Sub FillData()
' Creates the columns and compares the data
Dim NColumn As Integer
Dim FindColumn As Range
'Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Set FindColumn = Cells.Find(what:="Email address", After:=ActiveCell, LookIn:=xlFormulas, lookat _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
NColumn = FindColumn.Column + 1
Columns(NColumn).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

 

[Ricardo Caicedo]

Thank you very much apreciate!