Follow up question on VBA coding for Top 5 Customers.

D

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
I have a followup question to an earlier post.

The formulas are all working great. However, I need for the coding to overlook 3 customers when selecting the "Top 5 Customers".

These customers are GMIT, BGS GRM, and GMIT SC.

I'm not sure how to incorporate this into what I have currently. Also, can this be shortened at all?

This is the original post:

https://www.mrexcel.com/forum/excel...ch-top-5-outstanding-amounts-offset-name.html


Here is the code I have.

Code: 
[TABLE="width: 128"]
<tbody>[TR]
[TD="colspan: 2"]Sub OverdueReporting()
[/TD]
[/TR]
[TR]
[TD]'[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]' Overdue Aging Macro[/TD]
[/TR]
[TR]
[TD]'[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]'[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("H1").Select
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=MID(RC[-1],13,10)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("H1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.NumberFormat = "mm/dd/yy;@"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("I1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.SpecialCells(xlCellTypeLastCell).Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "X"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("M4").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC8,3)=""net"",MID(RC8,5,2),0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Columns("O:O").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.NumberFormat = "0.00"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("O5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = _[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        "=IFERROR(IF(OR(R1C[-7]-RC[-10]>1000,R1C[-7]-RC[-10]<0),0,R1C[-7]-RC[-10]),0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("P5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=IF(and(RC15>0,RC15<8),RC11,0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Q5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=IF(AND(RC15>7,rc15<15),RC11,0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("R5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=IF(AND(RC15>15,RC15<31),RC11,0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("S5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=IF(AND(RC15>30,RC15<60),RC11,0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("T5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=IF(RC15>59,RC11,0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("V5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=IF(RC[-21]=""Customer Totals:"",RC[-20],0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("M4").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("M5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range(Selection, Selection.End(xlDown)).Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("M5:y5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("M6:y6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range(Selection, Selection.End(xlDown)).Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("P3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "'1-7 dys"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Q3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "'8-14 dys"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("R3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "'15-30 dys"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("S3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "'31-60 dys"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("T3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "'>60 dys"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("y1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "LARGE"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Z1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "VALUE"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("AA1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "ROW"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("AB1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "CUSTOMER"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Y2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "'1"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Y3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "'2"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Y4").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "'3"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Y5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "'4"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Y6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "'5"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Z2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=LARGE(R4C[-4]:R1000C[-4],RC[-1])"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("AA2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=MATCH(RC[-1],C[-5],0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("AB2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = _[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        "=LOOKUP(2,1/((R4C[-27]:INDEX(C[-27],RC[-1])<>""INV"")*(R4C[-27]:INDEX(C[-27],RC[-1])<>""CUSTOMER TOTALS:"")),R4C[-27]:INDEX(C[-27],RC[-1]))"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Z2:AB2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Z3:Z6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Z2:Z6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Style = "Comma"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Cells.Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Columns("m:U").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Style = "Comma"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("AE2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=+RC[-5]/1000"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("AE2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.NumberFormat = "0.0000"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.NumberFormat = "0.000"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.NumberFormat = "0.00"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("AE3:AE6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("AB2:AE6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    With Selection.Interior[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Pattern = xlSolid[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .PatternColorIndex = xlAutomatic[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Color = 65535[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .PatternTintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Columns("AB:AB").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.ColumnWidth = 31[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Columns("AC:AC").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.ColumnWidth = 2.71[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Columns("AD:AE").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.ColumnWidth = 13.86[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("AD2:AE6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Borders(xlDiagonalDown).LineStyle = xlNone[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Borders(xlDiagonalUp).LineStyle = xlNone[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    With Selection.Borders(xlEdgeLeft)[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    With Selection.Borders(xlEdgeTop)[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    With Selection.Borders(xlEdgeBottom)[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    With Selection.Borders(xlEdgeRight)[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    With Selection.Borders(xlInsideVertical)[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    With Selection.Borders(xlInsideHorizontal)[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Cells.Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        xlNone, SkipBlanks:=False, Transpose:=False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Cells.Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ThisWorkbook.Worksheets("Sheet1").Cells.EntireColumn.AutoFit[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("P1:T1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        With Selection.Interior[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Pattern = xlSolid[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .PatternColorIndex = xlAutomatic[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Color = 65535[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .PatternTintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    With Selection.Font[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Name = "Calibri"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Size = 12[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Strikethrough = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Superscript = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Subscript = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .OutlineFont = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Shadow = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Underline = xlUnderlineStyleNone[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .Color = -16777216[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        .ThemeFont = xlThemeFontMinor[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("P1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=SUM(R[4]C:R[999]C)/1000"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("P1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("Q1:T1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("U1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveCell.FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Columns("A:U").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Columns("A:U").EntireColumn.AutoFit[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("p1:U1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Copy[/TD]
[/TR]
[TR]
[TD]    ChDir _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]        "H:\Accounting\ABC Services Inc\Accounting\Month End Financial Close\2019 Month End Close"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Workbooks.Open Filename:= _[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        "H:\Accounting\ABC SERVICES INC\Accounting\Month End Financial Close\2019 Month End Close\Overdue reporting template.xlsx"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("d8").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        xlNone, SkipBlanks:=False, Transpose:=True[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveWorkbook.Save[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveWindow.Close[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("AB2:AE6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Selection.Copy[/TD]
[/TR]
[TR]
[TD]    ChDir _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]        "H:\Accounting\ABC SERVICES Inc\Accounting\Month End Financial Close\2019 Month End Close"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Workbooks.Open Filename:= _[/TD]
[/TR]
[TR]
[TD="colspan: 2"]        "H:\Accounting\ABC SERVICES INC\Accounting\Month End Financial Close\2019 Month End Close\Overdue reporting template.xlsx"[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("a16").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveWorkbook.Save[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveWindow.Close[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    Range("a1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"]    ActiveWorkbook.Save[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Sub
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited by a moderator:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Try the following for a few ideas. Not tested, and no time to explain today sorry - train to catch...

Code: 
Option Explicit

Sub OverdueReporting()


Dim ws As Worksheet: Set ws = ActiveSheet
Dim wb As Workbook


With Range("H1")
    .FormulaR1C1 = "=MID(RC[-1],13,10)"
    .NumberFormat = "mm/dd/yy;@"
End With


With Range("I1")
    .SpecialCells(xlCellTypeLastCell).Select
    .FormulaR1C1 = "X"
End With


Range("M4").FormulaR1C1 = "=IF(LEFT(RC8,3)=""net"",MID(RC8,5,2),0)"
    
Columns("O:O").NumberFormat = "0.00"


Range("O5").FormulaR1C1 = "=IFERROR(IF(OR(R1C[-7]-RC[-10]>1000,R1C[-7]-RC[-10]<0),0,R1C[-7]-RC[-10]),0)"
Range("P5").FormulaR1C1 = "=IF(and(RC15>0,RC15<8),RC11,0)"
Range("Q5").FormulaR1C1 = "=IF(AND(RC15>7,rc15<15),RC11,0)"
Range("R5").FormulaR1C1 = "=IF(AND(RC15>15,RC15<31),RC11,0)"
Range("S5").FormulaR1C1 = "=IF(AND(RC15>30,RC15<60),RC11,0)"
Range("T5").FormulaR1C1 = "=IF(RC15>59,RC11,0)"
Range("V5").FormulaR1C1 = "=IF(RC[-21]=""Customer Totals:"",RC[-20],0)"
    
Range("M4").Copy
Range(Range("M5"), Range("M5").End(xlDown)).PasteSpecial xlPasteAll
    
Range("M5:y5").Copy
Range(Range("M6:y6"), Range("M6:y6").End(xlDown)).PasteSpecial xlPasteAll    ' this is strange - confirm which range is being patsed to
    
Range("P3") = "'1-7 dys"
Range("Q3") = "'8-14 dys"
Range("R3") = "'15-30 dys"
Range("S3") = "'31-60 dys"
Range("T3") = "'>60 dys"


Range("y1") = "LARGE"
Range("Z1") = "VALUE"
Range("AA1") = "ROW"
Range("AB1") = "CUSTOMER"
Range("Y2") = "'1"
Range("Y3") = "'2"
Range("Y4") = "'3"
Range("Y5") = "'4"
Range("Y6") = "'5"


Range("Z2").FormulaR1C1 = "=LARGE(R4C[-4]:R1000C[-4],RC[-1])"
Range("AA2").FormulaR1C1 = "=MATCH(RC[-1],C[-5],0)"
Range("AB2").FormulaR1C1 = "=LOOKUP(2,1/((R4C[-27]:INDEX(C[-27],RC[-1])<>""INV"")*(R4C[-27]:INDEX(C[-27],RC[-1])<>""CUSTOMER TOTALS:"")),R4C[-27]:INDEX(C[-27],RC[-1]))"
Range("Z2:AB2").Copy
Range("Z3:Z6").PasteSpecial xlPasteAll
    
Application.CutCopyMode = False


Range("Z2:Z6").Style = "Comma"
Columns("m:U").Style = "Comma"


With Range("AE2")
    .FormulaR1C1 = "=+RC[-5]/1000"
    .NumberFormat = "0.00"
    .Copy
End With


Range("AE3:AE6").PasteSpecial xlPasteAll
Application.CutCopyMode = False
    
With Range("AB2:AE6")
    With .Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End With


Columns("AB:AB").ColumnWidth = 31
Columns("AC:AC").ColumnWidth = 2.71
Columns("AD:AE").ColumnWidth = 13.86


Dim i As Integer
With Range("AD2:AE6")


    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    
    For i = 7 To 12 ' all constants xlEdgeLeft to xlInsideHorizontal
        With .Borders(i)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
    Next i
    
End With
    
    
With Cells
    .Value = .Value
    .EntireColumn.AutoFit
End With
    
With Range("P1:T1")
    With .Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic        '
        .Color = 65535                          '
        .TintAndShade = 0                       ' most of these lines can probably be deleted, depending on what you're doing
        .PatternTintAndShade = 0                '
    End With
    With .Font
        .Name = "Calibri"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .Color = -16777216
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
End With


Range("P1:T1").FormulaR1C1 = "=SUM(R[4]C:R[999]C)/1000"
Range("U1").FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
Columns("A:U").EntireColumn.AutoFit
    
Set wb = Workbooks.Open("H:\Accounting\ABC SERVICES INC\Accounting\Month End Financial Close\2019 Month End Close\Overdue reporting template.xlsx")




With wb
    ws.Range("p1:U1").Copy
    .Sheets("sheet name here").Range("d8").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        
    ws.Range("AB2:AE6").Copy
    .Sheets("sheet name here").Range("a16").PasteSpecial xlPasteAll
        
    Application.CutCopyMode = False
    
    .Save
    .Close
End With


Set wb = Nothing


ActiveWorkbook.Save


End Sub
 
Upvote 0
OK, just to add (now I've had time to read the original thread), You should be able to add extra conditions to your lookup formula

For simplicity I'll use the example formula given by Alladin at post https://www.mrexcel.com/forum/excel...alue-unsorted-data-post491590.html#post491590, which was [=LOOKUP(2,1/($B$2:$B$13=D2),$A$2:$A$13)]

I've added a third set of values a, b or c in range C2:C13, and a value to be ignored in cell D3. Changing the formula to [=LOOKUP(2,1/(($B$2:$B$13=D2)*($C$2:$C$13<>D3)),$A$2:$A$13)] adds the condition you're looking to use
 
Upvote 0
I think I might have it but I'm not sure.
Here is a link to my shared file.

https://www.dropbox.com/preview/Book2.xlsm?role=personal

Columns A:V are basically my data base.

Columns Y:AE is my top 5 table.

I show a top 5 with a couple of customers that should not be there.
Below this I show how the table should read.

Below that I have the coding for creating the table.

And below that I have how I think the code should look in order to ignore the customers "GMIT, BGS GRM, AND GMIT SC".

Please advise.


Thank you so much!
 
Upvote 0
Please help? I need the attention of all you excel gurus.

I can't make sense of this and I've read so much my head is spinning.

I can't even begin to guess how where to factor this in.

I am trying to factor out 3 ineligible customers from the top 5. - GMIT, GMIT SC, and BGS GRM

The first table is how the formula is coding now.

It does not factor out the 3 ineligible customers:

The second table is how it should look.

I am also including the formulas used to create this table and how I interpreted them.

I have all my data in columns a:v



[TABLE="width: 512"]
<tbody>[TR]
[TD]Y
[/TD]
[TD]Z
[/TD]
[TD]AA
[/TD]
[TD]AB
[/TD]
[TD]AC
[/TD]
[TD]AD
[/TD]
[TD]AE
[/TD]
[/TR]
[TR]
[TD]LARGE
[/TD]
[TD]VALUE
[/TD]
[TD]ROW
[/TD]
[TD]CUSTOMER
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]<strike> 945,173.11 </strike>
[/TD]
[TD]<strike>167</strike>
[/TD]
[TD]<strike>GMIT</strike>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]<strike>945.17</strike>
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD] 465,847.25
[/TD]
[TD]93
[/TD]
[TD]NAGGY
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]465.85
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD] 388,000.00
[/TD]
[TD]47
[/TD]
[TD]DURG
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]388.00
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]<strike> 356,657.87 </strike>
[/TD]
[TD]<strike>80</strike>
[/TD]
[TD]<strike>BGS GRM</strike>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]<strike>356.66</strike>
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD] 324,711.00
[/TD]
[TD]21
[/TD]
[TD]GNP
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]324.71
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LARGE
[/TD]
[TD]VALUE
[/TD]
[TD]ROW
[/TD]
[TD]CUSTOMER
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD] 465,847.25
[/TD]
[TD]93
[/TD]
[TD]NAGGY
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]465.85
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD] 388,000.00
[/TD]
[TD]47
[/TD]
[TD]DURG
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]388.00
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD] 324,711.00
[/TD]
[TD]11
[/TD]
[TD]GNP
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]324.71
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD] 291,158.00
[/TD]
[TD]39
[/TD]
[TD]CONSU
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]291.16
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD] 290,946.70
[/TD]
[TD]21
[/TD]
[TD]FLYBY
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]290.95
[/TD]
[/TR]
</tbody>[/TABLE]



Current Code:
Code: 
Range("Z2").Select    ActiveCell.FormulaR1C1 = "=LARGE(R4C[-4]:R1000C[-4],RC[-1])"
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = "=MATCH(RC[-1],C[-5],0)"
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = _
        "=LOOKUP(2,1/((R4C[-27]:INDEX(C[-27],RC[-1])<>""INV"")*(R4C[-27]:INDEX(C[-27],RC[-1])<>""CUSTOMER TOTALS:"")),R4C[-27]:INDEX(C[-27],RC[-1]))"
    Range("Z2:AB2").Select
    Selection.Copy
    Range("Z3:Z6").Select
    ActiveSheet.Paste

Interpreted Code from R1C1 reference style to A1:
[TABLE="width: 763"]
<tbody>[TR]
[TD]
Code: 
Range("Z2").Select
[/TD]
[/TR]
[TR]
[TD]    ActiveCell.FormulaR1C1 = "=LARGE(v4:v1000,y2)"
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]    Range("AA2").Select
[/TD]
[/TR]
[TR]
[TD]    ActiveCell.FormulaR1C1 = "=MATCH(Z2,v:v,0)"
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]    Range("AB2").Select
[/TD]
[/TR]
[TR]
[TD]    ActiveCell.FormulaR1C1 = _
[/TD]
[/TR]
[TR]
[TD]        "=LOOKUP(2,1/((a4:INDEX(A:A,AA2])<>""INV"")*(a4:INDEX(A:A,AA2)<>""CUSTOMER TOTALS:"")),a4:INDEX(A:A,AA2))"
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
311
Micron
M
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
584
RoryA
RoryA
E
  • Question Question
Cleaning up some "Select" code
2
Replies
17
Views
237
EssKayKay
E
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
262
rlv01
R
T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
344
RoryA
RoryA

Forum statistics

Threads
1,223,885
Messages
6,175,181
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top