Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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!

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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!



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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!

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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!

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I insert cells in a macro? ruby02monday Excel Worksheet Functions 7 July 12th 07 12:32 AM
make a macro to insert a macro mithu Excel Discussion (Misc queries) 6 March 20th 07 07:04 PM
DO NOT Insert Row - Macro Danny Excel Worksheet Functions 4 May 5th 06 01:04 AM
Macro to insert copied cells [email protected] Excel Discussion (Misc queries) 17 January 18th 06 11:40 AM
Insert Row with a macro Debi Excel Discussion (Misc queries) 5 March 11th 05 08:31 PM


All times are GMT +1. The time now is 10:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"