How do I insert cells in a macro?
 

I missed the answer by 15 minutes, then went on vacation for a week. The
answer didn't work, however, and this problem truely has me bugged. My
question is now past page 15, so I thought I would try again.

I have a worksheet with 6 columns and 300+ rows. In column "A" I need to
find, "GRAND TOTAL:" (all caps, if that matters) and insert 7 cells, shifting
all on that row only,to the right. The amount of information increases each
month, making "GRAND TOTAL:" a variable. Any help is very much appreciated!

How do I insert cells in a macro?
 

I'm not clear on what you want to do from what you've posted (specifically
the location of where you want to insert cells). Try recording what you want
to do (with Tool - Macro - Record New Macro) and post that code and we can
help you clean it up.

HTH,
Barb Reinhardt

"ruby02monday" wrote:

I missed the answer by 15 minutes, then went on vacation for a week. The
answer didn't work, however, and this problem truely has me bugged. My
question is now past page 15, so I thought I would try again.

I have a worksheet with 6 columns and 300+ rows. In column "A" I need to
find, "GRAND TOTAL:" (all caps, if that matters) and insert 7 cells, shifting
all on that row only,to the right. The amount of information increases each
month, making "GRAND TOTAL:" a variable. Any help is very much appreciated!

How do I insert cells in a macro?
 

Hi Barb!
I tried to record a macro, but "GRAND TOTAL:" shows up on different rows
each month, making it a variable.
A B C D
E F
CITY/STATE PAYOR ID PAYOR NAME LAST AUDIT DATE CK AMT AUDIT BILLED
Dallas, TX A121 Zinn 11/11/02
15.86 15.86
Plano, TX 244 Exxon 1/1/07
102.85 102.85
GRAND TOTAL: 292 Total (stuff) 100%
$5,123...

Grand Total is on line, say 300 this month and 306 next month, and 310 the
next, etc.

I want the macro to Find "GRAND TOTAL:" in column "A" each month, wherever
it lands, insert 7 cells on that row only, and shift to the right. This will
leave columns A:F blank. The only thing in columns G:L will be: GRAND TOTAL:
beginning in column G, Payor ID in column H, etc., which will be the result
of finding GRAND TOTAL: in column A and inserting 7 cells, shifting to the
right.
"Barb Reinhardt" wrote:

I'm not clear on what you want to do from what you've posted (specifically
the location of where you want to insert cells). Try recording what you want
to do (with Tool - Macro - Record New Macro) and post that code and we can
help you clean it up.

HTH,
Barb Reinhardt

"ruby02monday" wrote:

I missed the answer by 15 minutes, then went on vacation for a week. The
answer didn't work, however, and this problem truely has me bugged. My
question is now past page 15, so I thought I would try again.

I have a worksheet with 6 columns and 300+ rows. In column "A" I need to
find, "GRAND TOTAL:" (all caps, if that matters) and insert 7 cells, shifting
all on that row only,to the right. The amount of information increases each
month, making "GRAND TOTAL:" a variable. Any help is very much appreciated!

How do I insert cells in a macro?
 

Is there a risk that you'll insert cells so that something goes off the sheet?

"ruby02monday" wrote:

Hi Barb!
I tried to record a macro, but "GRAND TOTAL:" shows up on different rows
each month, making it a variable.
A B C D
E F
CITY/STATE PAYOR ID PAYOR NAME LAST AUDIT DATE CK AMT AUDIT BILLED
Dallas, TX A121 Zinn 11/11/02
15.86 15.86
Plano, TX 244 Exxon 1/1/07
102.85 102.85
GRAND TOTAL: 292 Total (stuff) 100%
$5,123...

Grand Total is on line, say 300 this month and 306 next month, and 310 the
next, etc.

I want the macro to Find "GRAND TOTAL:" in column "A" each month, wherever
it lands, insert 7 cells on that row only, and shift to the right. This will
leave columns A:F blank. The only thing in columns G:L will be: GRAND TOTAL:
beginning in column G, Payor ID in column H, etc., which will be the result
of finding GRAND TOTAL: in column A and inserting 7 cells, shifting to the
right.
"Barb Reinhardt" wrote:

I'm not clear on what you want to do from what you've posted (specifically
the location of where you want to insert cells). Try recording what you want
to do (with Tool - Macro - Record New Macro) and post that code and we can
help you clean it up.

HTH,
Barb Reinhardt

"ruby02monday" wrote:

I missed the answer by 15 minutes, then went on vacation for a week. The
answer didn't work, however, and this problem truely has me bugged. My
question is now past page 15, so I thought I would try again.

I have a worksheet with 6 columns and 300+ rows. In column "A" I need to
find, "GRAND TOTAL:" (all caps, if that matters) and insert 7 cells, shifting
all on that row only,to the right. The amount of information increases each
month, making "GRAND TOTAL:" a variable. Any help is very much appreciated!

How do I insert cells in a macro?
 

Try this:
Sub FindandInsert()
'
Dim rFound As Range
Dim aWS As Worksheet

Set aWS = ActiveSheet

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


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

End If
End With

End Sub

HTH

"ruby02monday" wrote:

Hi Barb!
I tried to record a macro, but "GRAND TOTAL:" shows up on different rows
each month, making it a variable.
A B C D
E F
CITY/STATE PAYOR ID PAYOR NAME LAST AUDIT DATE CK AMT AUDIT BILLED
Dallas, TX A121 Zinn 11/11/02
15.86 15.86
Plano, TX 244 Exxon 1/1/07
102.85 102.85
GRAND TOTAL: 292 Total (stuff) 100%
$5,123...

Grand Total is on line, say 300 this month and 306 next month, and 310 the
next, etc.

I want the macro to Find "GRAND TOTAL:" in column "A" each month, wherever
it lands, insert 7 cells on that row only, and shift to the right. This will
leave columns A:F blank. The only thing in columns G:L will be: GRAND TOTAL:
beginning in column G, Payor ID in column H, etc., which will be the result
of finding GRAND TOTAL: in column A and inserting 7 cells, shifting to the
right.
"Barb Reinhardt" wrote:

I'm not clear on what you want to do from what you've posted (specifically
the location of where you want to insert cells). Try recording what you want
to do (with Tool - Macro - Record New Macro) and post that code and we can
help you clean it up.

HTH,
Barb Reinhardt

"ruby02monday" wrote:

I missed the answer by 15 minutes, then went on vacation for a week. The
answer didn't work, however, and this problem truely has me bugged. My
question is now past page 15, so I thought I would try again.

I have a worksheet with 6 columns and 300+ rows. In column "A" I need to
find, "GRAND TOTAL:" (all caps, if that matters) and insert 7 cells, shifting
all on that row only,to the right. The amount of information increases each
month, making "GRAND TOTAL:" a variable. Any help is very much appreciated!

How do I insert cells in a macro?
 

Beginning with SetrFound thru SearchFormat:=False) turns red. First I
typed it. Then deleted it, then I copied and pasted it and it is red each
time. I am working in "Module 11". Does that matter? I have tried so many
different things. Some modules have macros, some don't. Sorry, I am so new
to this....

"Barb Reinhardt" wrote:

Try this:
Sub FindandInsert()
'
Dim rFound As Range
Dim aWS As Worksheet

Set aWS = ActiveSheet

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


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

End If
End With

End Sub

HTH

"ruby02monday" wrote:

Hi Barb!
I tried to record a macro, but "GRAND TOTAL:" shows up on different rows
each month, making it a variable.
A B C D
E F
CITY/STATE PAYOR ID PAYOR NAME LAST AUDIT DATE CK AMT AUDIT BILLED
Dallas, TX A121 Zinn 11/11/02
15.86 15.86
Plano, TX 244 Exxon 1/1/07
102.85 102.85
GRAND TOTAL: 292 Total (stuff) 100%
$5,123...

Grand Total is on line, say 300 this month and 306 next month, and 310 the
next, etc.

I want the macro to Find "GRAND TOTAL:" in column "A" each month, wherever
it lands, insert 7 cells on that row only, and shift to the right. This will
leave columns A:F blank. The only thing in columns G:L will be: GRAND TOTAL:
beginning in column G, Payor ID in column H, etc., which will be the result
of finding GRAND TOTAL: in column A and inserting 7 cells, shifting to the
right.
"Barb Reinhardt" wrote:

I'm not clear on what you want to do from what you've posted (specifically
the location of where you want to insert cells). Try recording what you want
to do (with Tool - Macro - Record New Macro) and post that code and we can
help you clean it up.

HTH,
Barb Reinhardt

"ruby02monday" wrote:

I missed the answer by 15 minutes, then went on vacation for a week. The
answer didn't work, however, and this problem truely has me bugged. My
question is now past page 15, so I thought I would try again.

I have a worksheet with 6 columns and 300+ rows. In column "A" I need to
find, "GRAND TOTAL:" (all caps, if that matters) and insert 7 cells, shifting
all on that row only,to the right. The amount of information increases each
month, making "GRAND TOTAL:" a variable. Any help is very much appreciated!

How do I insert cells in a macro?
 

THANK YOU!!!
I modified it a bit. I took part of JMB's and part of yours and it works!!!
I love you guys! Now I can retire with a clear conscience! Only 6 more
working days!

"Barb Reinhardt" wrote:

Try this:
Sub FindandInsert()
'
Dim rFound As Range
Dim aWS As Worksheet

Set aWS = ActiveSheet

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


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

End If
End With

End Sub

HTH

"ruby02monday" wrote:

Hi Barb!
I tried to record a macro, but "GRAND TOTAL:" shows up on different rows
each month, making it a variable.
A B C D
E F
CITY/STATE PAYOR ID PAYOR NAME LAST AUDIT DATE CK AMT AUDIT BILLED
Dallas, TX A121 Zinn 11/11/02
15.86 15.86
Plano, TX 244 Exxon 1/1/07
102.85 102.85
GRAND TOTAL: 292 Total (stuff) 100%
$5,123...

Grand Total is on line, say 300 this month and 306 next month, and 310 the
next, etc.

I want the macro to Find "GRAND TOTAL:" in column "A" each month, wherever
it lands, insert 7 cells on that row only, and shift to the right. This will
leave columns A:F blank. The only thing in columns G:L will be: GRAND TOTAL:
beginning in column G, Payor ID in column H, etc., which will be the result
of finding GRAND TOTAL: in column A and inserting 7 cells, shifting to the
right.
"Barb Reinhardt" wrote:

I'm not clear on what you want to do from what you've posted (specifically
the location of where you want to insert cells). Try recording what you want
to do (with Tool - Macro - Record New Macro) and post that code and we can
help you clean it up.

HTH,
Barb Reinhardt

"ruby02monday" wrote:

I missed the answer by 15 minutes, then went on vacation for a week. The
answer didn't work, however, and this problem truely has me bugged. My
question is now past page 15, so I thought I would try again.

I have a worksheet with 6 columns and 300+ rows. In column "A" I need to
find, "GRAND TOTAL:" (all caps, if that matters) and insert 7 cells, shifting
all on that row only,to the right. The amount of information increases each
month, making "GRAND TOTAL:" a variable. Any help is very much appreciated!

How do I insert cells in a macro?
 

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


"Barb Reinhardt" wrote:

Try this:
Sub FindandInsert()
'
Dim rFound As Range
Dim aWS As Worksheet

Set aWS = ActiveSheet

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


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

End If
End With

End Sub

HTH

"ruby02monday" wrote:

Hi Barb!
I tried to record a macro, but "GRAND TOTAL:" shows up on different rows
each month, making it a variable.
A B C D
E F
CITY/STATE PAYOR ID PAYOR NAME LAST AUDIT DATE CK AMT AUDIT BILLED
Dallas, TX A121 Zinn 11/11/02
15.86 15.86
Plano, TX 244 Exxon 1/1/07
102.85 102.85
GRAND TOTAL: 292 Total (stuff) 100%
$5,123...

Grand Total is on line, say 300 this month and 306 next month, and 310 the
next, etc.

I want the macro to Find "GRAND TOTAL:" in column "A" each month, wherever
it lands, insert 7 cells on that row only, and shift to the right. This will
leave columns A:F blank. The only thing in columns G:L will be: GRAND TOTAL:
beginning in column G, Payor ID in column H, etc., which will be the result
of finding GRAND TOTAL: in column A and inserting 7 cells, shifting to the
right.
"Barb Reinhardt" wrote:

I'm not clear on what you want to do from what you've posted (specifically
the location of where you want to insert cells). Try recording what you want
to do (with Tool - Macro - Record New Macro) and post that code and we can
help you clean it up.

HTH,
Barb Reinhardt

"ruby02monday" wrote:

I missed the answer by 15 minutes, then went on vacation for a week. The
answer didn't work, however, and this problem truely has me bugged. My
question is now past page 15, so I thought I would try again.

I have a worksheet with 6 columns and 300+ rows. In column "A" I need to
find, "GRAND TOTAL:" (all caps, if that matters) and insert 7 cells, shifting
all on that row only,to the right. The amount of information increases each
month, making "GRAND TOTAL:" a variable. Any help is very much appreciated!