How do I insert cells in a macro?
 

I have a worksheet with 6 columns 300+ rows. I have a macro that preforms a
a filter, delete rows based on blanks, turn off filter and save as values.
Now I need to find "GRAND TOTAL:". in Column A. This is variable. Once
found, insert 7 cells and shift right.

It has got to be staring me in the face, but I can't see it anymore. Please
help!!

How do I insert cells in a macro?
 

Sorry, first time user of this great site. I left out that I am working in
Excel 2002. Is more info. needed?

"ruby02monday" wrote:

I have a worksheet with 6 columns 300+ rows. I have a macro that preforms a
a filter, delete rows based on blanks, turn off filter and save as values.
Now I need to find "GRAND TOTAL:". in Column A. This is variable. Once
found, insert 7 cells and shift right.

It has got to be staring me in the face, but I can't see it anymore. Please
help!!

How do I insert cells in a macro?
 

Does this help any?

Sub test()
Const lngRowsToInsert As Long = 7
Const strCriteria As String = "Grand Total:"
Dim rngGrandTotal As Range

With Sheets("Sheet3").Range("A:A")
Set rngGrandTotal = .Find( _
what:=strCriteria, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)
End With

If Not rngGrandTotal Is Nothing Then
rngGrandTotal.Cells(1).Resize(lngRowsToInsert, 1).Columns.Insert
shift:=xlToRight
Else
'Grand Total was not found
End If
End Sub


"ruby02monday" wrote:

I have a worksheet with 6 columns 300+ rows. I have a macro that preforms a
a filter, delete rows based on blanks, turn off filter and save as values.
Now I need to find "GRAND TOTAL:". in Column A. This is variable. Once
found, insert 7 cells and shift right.

It has got to be staring me in the face, but I can't see it anymore. Please
help!!

How do I insert cells in a macro?
 

Sorry for the delay in replying, I have been on vacation. This didn't fix
it, however. It didn't do anything. On my spreadsheet, in column A, the
words, "GRAND TOTAL:" are in all caps with the ":" at the end. I changed the
words in the " " to read in all caps and added the ":". Also, at the end,
the shift:-x1ToRight seems to draw the error, "Compile Error, Expected
Expression." If i put it at the end of the line above, I get no error, but
nothing happens either.

Am I not doing this right? I even changed the name of the sheet to Sheet3.

"JMB" wrote:

Does this help any?

Sub test()
Const lngRowsToInsert As Long = 7
Const strCriteria As String = "Grand Total:"
Dim rngGrandTotal As Range

With Sheets("Sheet3").Range("A:A")
Set rngGrandTotal = .Find( _
what:=strCriteria, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)
End With

If Not rngGrandTotal Is Nothing Then
rngGrandTotal.Cells(1).Resize(lngRowsToInsert, 1).Columns.Insert
shift:=xlToRight
Else
'Grand Total was not found
End If
End Sub


"ruby02monday" wrote:

I have a worksheet with 6 columns 300+ rows. I have a macro that preforms a
a filter, delete rows based on blanks, turn off filter and save as values.
Now I need to find "GRAND TOTAL:". in Column A. This is variable. Once
found, insert 7 cells and shift right.

It has got to be staring me in the face, but I can't see it anymore. Please
help!!

How do I insert cells in a macro?
 

The NG tends to wrap the code funny - the shift:=xltoright should not be on
its own line when you paste the code in. This should all be one line with a
space between "Insert" and "shift".

rngGrandTotal.Cells(1).Resize(lngRowsToInsert, 1).Columns.Insert
shift:=xlToRight

Try stepping through the code a line at a time using the F8 key. You s/b
able to tell if the cell w/"Grand Total:" is found by following the flow as
the program executes.



"ruby02monday" wrote:

Sorry for the delay in replying, I have been on vacation. This didn't fix
it, however. It didn't do anything. On my spreadsheet, in column A, the
words, "GRAND TOTAL:" are in all caps with the ":" at the end. I changed the
words in the " " to read in all caps and added the ":". Also, at the end,
the shift:-x1ToRight seems to draw the error, "Compile Error, Expected
Expression." If i put it at the end of the line above, I get no error, but
nothing happens either.

Am I not doing this right? I even changed the name of the sheet to Sheet3.

"JMB" wrote:

Does this help any?

Sub test()
Const lngRowsToInsert As Long = 7
Const strCriteria As String = "Grand Total:"
Dim rngGrandTotal As Range

With Sheets("Sheet3").Range("A:A")
Set rngGrandTotal = .Find( _
what:=strCriteria, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)
End With

If Not rngGrandTotal Is Nothing Then
rngGrandTotal.Cells(1).Resize(lngRowsToInsert, 1).Columns.Insert
shift:=xlToRight
Else
'Grand Total was not found
End If
End Sub


"ruby02monday" wrote:

I have a worksheet with 6 columns 300+ rows. I have a macro that preforms a
a filter, delete rows based on blanks, turn off filter and save as values.
Now I need to find "GRAND TOTAL:". in Column A. This is variable. Once
found, insert 7 cells and shift right.

It has got to be staring me in the face, but I can't see it anymore. Please
help!!

How do I insert cells in a macro?
 

Nothing happens. I even added some rows after Grand Total, just to see if
the macro was inserting rows and not cells, but nothing happens. I am
working with module 11, does that matter?

"JMB" wrote:

The NG tends to wrap the code funny - the shift:=xltoright should not be on
its own line when you paste the code in. This should all be one line with a
space between "Insert" and "shift".

rngGrandTotal.Cells(1).Resize(lngRowsToInsert, 1).Columns.Insert
shift:=xlToRight

Try stepping through the code a line at a time using the F8 key. You s/b
able to tell if the cell w/"Grand Total:" is found by following the flow as
the program executes.



"ruby02monday" wrote:

Sorry for the delay in replying, I have been on vacation. This didn't fix
it, however. It didn't do anything. On my spreadsheet, in column A, the
words, "GRAND TOTAL:" are in all caps with the ":" at the end. I changed the
words in the " " to read in all caps and added the ":". Also, at the end,
the shift:-x1ToRight seems to draw the error, "Compile Error, Expected
Expression." If i put it at the end of the line above, I get no error, but
nothing happens either.

Am I not doing this right? I even changed the name of the sheet to Sheet3.

"JMB" wrote:

Does this help any?

Sub test()
Const lngRowsToInsert As Long = 7
Const strCriteria As String = "Grand Total:"
Dim rngGrandTotal As Range

With Sheets("Sheet3").Range("A:A")
Set rngGrandTotal = .Find( _
what:=strCriteria, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)
End With

If Not rngGrandTotal Is Nothing Then
rngGrandTotal.Cells(1).Resize(lngRowsToInsert, 1).Columns.Insert
shift:=xlToRight
Else
'Grand Total was not found
End If
End Sub


"ruby02monday" wrote:

I have a worksheet with 6 columns 300+ rows. I have a macro that preforms a
a filter, delete rows based on blanks, turn off filter and save as values.
Now I need to find "GRAND TOTAL:". in Column A. This is variable. Once
found, insert 7 cells and shift right.

It has got to be staring me in the face, but I can't see it anymore. Please
help!!

How do I insert cells in a macro?
 

THANK YOU!!!
I modified it a bit from yours and Barb's. This is what I did:

Sub FindandInsert()
'
Dim rFound As Range
Dim aWS As Worksheet
Const strCriteria As String = "GRAND TOTAL:"
Set aWS = ActiveSheet

With aWS
Set rFound = .Columns(1).Find( _
what:=strCriteria, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


Set rFound = rFound.Resize(1, 7)
rFound.Insert Shift:=xlToRight


End With

End Sub


"JMB" wrote:

The NG tends to wrap the code funny - the shift:=xltoright should not be on
its own line when you paste the code in. This should all be one line with a
space between "Insert" and "shift".

rngGrandTotal.Cells(1).Resize(lngRowsToInsert, 1).Columns.Insert
shift:=xlToRight

Try stepping through the code a line at a time using the F8 key. You s/b
able to tell if the cell w/"Grand Total:" is found by following the flow as
the program executes.



"ruby02monday" wrote:

Sorry for the delay in replying, I have been on vacation. This didn't fix
it, however. It didn't do anything. On my spreadsheet, in column A, the
words, "GRAND TOTAL:" are in all caps with the ":" at the end. I changed the
words in the " " to read in all caps and added the ":". Also, at the end,
the shift:-x1ToRight seems to draw the error, "Compile Error, Expected
Expression." If i put it at the end of the line above, I get no error, but
nothing happens either.

Am I not doing this right? I even changed the name of the sheet to Sheet3.

"JMB" wrote:

Does this help any?

Sub test()
Const lngRowsToInsert As Long = 7
Const strCriteria As String = "Grand Total:"
Dim rngGrandTotal As Range

With Sheets("Sheet3").Range("A:A")
Set rngGrandTotal = .Find( _
what:=strCriteria, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)
End With

If Not rngGrandTotal Is Nothing Then
rngGrandTotal.Cells(1).Resize(lngRowsToInsert, 1).Columns.Insert
shift:=xlToRight
Else
'Grand Total was not found
End If
End Sub


"ruby02monday" wrote:

I have a worksheet with 6 columns 300+ rows. I have a macro that preforms a
a filter, delete rows based on blanks, turn off filter and save as values.
Now I need to find "GRAND TOTAL:". in Column A. This is variable. Once
found, insert 7 cells and shift right.

It has got to be staring me in the face, but I can't see it anymore. Please
help!!

How do I insert cells in a macro?
 

Glad to hear you got it working.

"ruby02monday" wrote:

THANK YOU!!!
I modified it a bit from yours and Barb's. This is what I did:

Sub FindandInsert()
'
Dim rFound As Range
Dim aWS As Worksheet
Const strCriteria As String = "GRAND TOTAL:"
Set aWS = ActiveSheet

With aWS
Set rFound = .Columns(1).Find( _
what:=strCriteria, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


Set rFound = rFound.Resize(1, 7)
rFound.Insert Shift:=xlToRight


End With

End Sub


"JMB" wrote:

The NG tends to wrap the code funny - the shift:=xltoright should not be on
its own line when you paste the code in. This should all be one line with a
space between "Insert" and "shift".

rngGrandTotal.Cells(1).Resize(lngRowsToInsert, 1).Columns.Insert
shift:=xlToRight

Try stepping through the code a line at a time using the F8 key. You s/b
able to tell if the cell w/"Grand Total:" is found by following the flow as
the program executes.



"ruby02monday" wrote:

Sorry for the delay in replying, I have been on vacation. This didn't fix
it, however. It didn't do anything. On my spreadsheet, in column A, the
words, "GRAND TOTAL:" are in all caps with the ":" at the end. I changed the
words in the " " to read in all caps and added the ":". Also, at the end,
the shift:-x1ToRight seems to draw the error, "Compile Error, Expected
Expression." If i put it at the end of the line above, I get no error, but
nothing happens either.

Am I not doing this right? I even changed the name of the sheet to Sheet3.

"JMB" wrote:

Does this help any?

Sub test()
Const lngRowsToInsert As Long = 7
Const strCriteria As String = "Grand Total:"
Dim rngGrandTotal As Range

With Sheets("Sheet3").Range("A:A")
Set rngGrandTotal = .Find( _
what:=strCriteria, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)
End With

If Not rngGrandTotal Is Nothing Then
rngGrandTotal.Cells(1).Resize(lngRowsToInsert, 1).Columns.Insert
shift:=xlToRight
Else
'Grand Total was not found
End If
End Sub


"ruby02monday" wrote:

I have a worksheet with 6 columns 300+ rows. I have a macro that preforms a
a filter, delete rows based on blanks, turn off filter and save as values.
Now I need to find "GRAND TOTAL:". in Column A. This is variable. Once
found, insert 7 cells and shift right.

It has got to be staring me in the face, but I can't see it anymore. Please
help!!