Prev: Macro not saving as
Next: Call a Sub from another Worksheet
From: Valerie on 27 Apr 2010 10:27
Hello, all.

I have a spreadsheet that has "sections" - a header row and several detail
rows beneath it until the next header row, etc. The sections are different
companies. This is for an JE upload into SAP and the row count for each
company section is limited to 190 rows. I have 2 companies that are always
larger than 190 and occasionally one other company. This is the macro I
currently have that is for a specific company:

'Split lines longer than 190 for US14

''' where to search
Set rg = ActiveSheet.Range("E:E")
''' search for 'found'
Set rg = rg.Find(What:="US14", LookIn:=xlValues, LookAt:=xlWhole)
''' process result
If rg Is Nothing Then ''' was not found
MsgBox "Not found"
Else
''' go 190 rows below that found cell
Set rg = rg.Offset(190)
''' resize to 2 rows
Set rg = rg.Resize(2)
''' insert 2 rows
rg.EntireRow.Insert xlShiftDown
End If
'Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, -4).Select
Selection.EntireRow.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveSheet.Paste
'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

Cells.Find(What:="US14 Total", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Selection.EntireRow.Copy
Selection.End(xlUp).Select
ActiveCell.Offset(-1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, 8).Select
ActiveCell.FormulaR1C1 = _

"=SUMIF(R[-189]C3:R[-1]C3,40,R[-189]C9:R[-1]C9)-SUMIF(R[-189]C3:R[-1]C3,50,R[-189]C9:R[-1]C9)"

I am wondering if there is a way this macro could be enhanced/revised to
where XL would evaluate the number of lines within the company section to see
if a break is needed and if so, how many breaks (US14 often needs 2 - has
more than 380 lines) and insert these breaks. I currently have to do the
second break manually. Any help with this would be greatly appreciated!!

Thanks!
Valerie
From: PY & Associates on 29 Apr 2010 02:34
On Apr 27, 10:27 pm, Valerie <Vale...(a)discussions.microsoft.com>
wrote:
> Hello, all.
>
> I have a spreadsheet that has "sections" - a header row and several detail
> rows beneath it until the next header row, etc.  The sections are different
> companies. This is for an JE upload into SAP and the row count for each
> company section is limited to 190 rows.  I have 2 companies that are always
> larger than 190 and occasionally one other company.  This is the macro I
> currently have that is for a specific company:
>
> 'Split lines longer than 190 for US14
>
>    ''' where to search
>    Set rg = ActiveSheet.Range("E:E")
>    ''' search for 'found'
>    Set rg = rg.Find(What:="US14", LookIn:=xlValues, LookAt:=xlWhole)
>    ''' process result
>    If rg Is Nothing Then   ''' was not found
>       MsgBox "Not found"
>    Else
>       ''' go 190 rows below that found cell
>       Set rg = rg.Offset(190)
>       ''' resize to 2 rows
>       Set rg = rg.Resize(2)
>       ''' insert 2 rows
>       rg.EntireRow.Insert xlShiftDown
>    End If
>     'Range("A1").Select
>     Selection.End(xlUp).Select
>     ActiveCell.Offset(0, -4).Select
>     Selection.EntireRow.Copy
>     Selection.End(xlDown).Select
>     ActiveCell.Offset(2, 0).Select
>     ActiveSheet.Paste
>     'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
>         False, Transpose:=False
>     Application.CutCopyMode = False
>
>     Cells.Find(What:="US14 Total", After:=ActiveCell, LookIn:=xlFormulas,
> LookAt:= _
>         xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
> MatchCase:= _
>         False).Activate
>     Selection.EntireRow.Copy
>     Selection.End(xlUp).Select
>     ActiveCell.Offset(-1, 0).Select
>     ActiveSheet.Paste
>     Application.CutCopyMode = False
>     ActiveCell.Offset(0, 8).Select
>     ActiveCell.FormulaR1C1 = _
>
> "=SUMIF(R[-189]C3:R[-1]C3,40,R[-189]C9:R[-1]C9)-SUMIF(R[-189]C3:R[-1]C3,50,­R[-189]C9:R[-1]C9)"
>
> I am wondering if there is a way this macro could be enhanced/revised to
> where XL would evaluate the number of lines within the company section to see
> if a break is needed and if so, how many breaks (US14 often needs 2 - has
> more than 380 lines) and insert these breaks.  I currently have to do the
> second break manually.  Any help with this would be greatly appreciated!!
>
> Thanks!
> Valerie

I imagine I would search for the header rows and get the number of
rows inbetween.
If more than "190" then insert accordingly, else do nothing.
 | 
Pages: 1
Prev: Macro not saving as
Next: Call a Sub from another Worksheet