View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Barb Reinhardt Barb Reinhardt is offline
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!