Hello all,
I am trying to write a macro that filters a sheet and uses formulas like SUMIF and Vlookup to replace the existing columns. I need to find a way to drag down the formulas without affecting the hidden/ invisible cells in vba. I would really appreciate any help and suggestion. Here is my code so far. I am dragging the formulas but couldn't find a way to skip hidden/invisible rows. Thank you so much for your time.
Sub ReconcileColumns()
'
' ReconcileColumns Macro
'
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim fPath As Variant
Dim Val As Variant
Dim vendor As String
Dim fName As String
Dim fso As Object
Dim DT As String
Dim fRow As Long
Dim lastRow As Long
Dim AgentBreakdownlRow As Long
Dim CompareAgentBreakdownlRow As Long
Set fso = CreateObject("Scripting.FileSystemObject")
'Open file you would like to reconcile
fPath = Application.GetOpenFilename(filefilter:="Excel Files,*.xl*; *.xm*")
' MultiSelect:=True)
If fPath <> False Then
fName = fso.GetFileName(fPath)
Workbooks.Open fileName:=fPath
End If
'Unfilter sheet only if there is a filter
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
'last row of newly opened file
AgentBreakdownlRow = Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("C" & Rows.Count).End(xlUp).Row
'Give everyone the same date and week
Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("H2:I2").Copy Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("H3:I" & AgentBreakdownlRow)
'Sort A - Z Vendor file
Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("A1:Q" & AgentBreakdownlRow).Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes
'Format date cells
Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("I:I").NumberFormat = "m/dd/yyyy"
''last row of master sheet
lRow = Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("A" & Rows.Count).End(xlUp).Row
'
''Check what vendor and filter it accordingly in per agent breakdown sheet
vendor = Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("A2").Value
DT = Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("I2").Value
'Apply Vendor Filter
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("A1:S" & lRow).AutoFilter Field:=1, Criteria1:=vendor
'Apply Date Filter
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("A1:S" & lRow).AutoFilter Field:=9, Criteria1:=DT
'Clear the sorted field
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").AutoFilter.Sort.SortFields.Clear
'Sort A - Z Master file
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").AutoFilter.Sort.SortFields.Add Order:=xlAscending, _
SortOn:=xlSortOnValues, Key:=Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("E1:E" & lRow)
'Applying the sort
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").AutoFilter.Sort.Apply
'adjust training hours adjustment column of master sheet based on columns total training hours and training hours
'adjustment of vendor sheet
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Activate
Worksheets("Per Agent Per Day Hr Breakdown").Select
fRow = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
lastRow = Worksheets("Per Agent Per Day Hr Breakdown").Range("A" & Rows.Count).End(xlUp).Row
MsgBox fRow
MsgBox lastRow
'The formulas I am trying to use are
'Range("O2").Formula = "=SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E2, '[" & fName & "]Per Agent Per Day Hr Breakdown'!$J:$J) + SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E2, '[" & fName & "]Per Agent Per Day Hr Breakdown'!$O:$O) - J2"
'Range("O2").Select
'Selection.AutoFill Destination:=Range("O2" & ":O" & lRow), Type:=xlFillDefault
'With Range("O2" & ":O" & lRow)
' .Value = .Value
'End With
' Range("N" & fRow).Formula = "=SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E" & fRow & ", '[" & fName & "]Per Agent Per Day Hr Breakdown'!$K:$K) + SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E" & fRow & ", '[" & fName & "]Per Agent Per Day Hr Breakdown'!$N:$N) - K" & fRow & ""
'Range("N" & fRow).Select
'Selection.AutoFill Destination:=Range("N" & fRow & ":N" & lastRow), Type:=xlFillDefault
'With Range("N" & fRow & ":N" & lastRow)
' .Value = .Value
'End With
'Range("M" & fRow).Formula = "=VLOOKUP(E&fRow, '[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$M, 11, FALSE)"
''Range("M" & fRow).Select
'Selection.AutoFill Destination:=Range("M" & fRow & ":M" & lastRow), Type:=xlFillDefault
'With Range("M" & fRow & ":M" & lastRow)
'.Value = .Value
'End With
//drag down the formula ignore the hidden cells
I am trying to write a macro that filters a sheet and uses formulas like SUMIF and Vlookup to replace the existing columns. I need to find a way to drag down the formulas without affecting the hidden/ invisible cells in vba. I would really appreciate any help and suggestion. Here is my code so far. I am dragging the formulas but couldn't find a way to skip hidden/invisible rows. Thank you so much for your time.
Sub ReconcileColumns()
'
' ReconcileColumns Macro
'
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim fPath As Variant
Dim Val As Variant
Dim vendor As String
Dim fName As String
Dim fso As Object
Dim DT As String
Dim fRow As Long
Dim lastRow As Long
Dim AgentBreakdownlRow As Long
Dim CompareAgentBreakdownlRow As Long
Set fso = CreateObject("Scripting.FileSystemObject")
'Open file you would like to reconcile
fPath = Application.GetOpenFilename(filefilter:="Excel Files,*.xl*; *.xm*")
' MultiSelect:=True)
If fPath <> False Then
fName = fso.GetFileName(fPath)
Workbooks.Open fileName:=fPath
End If
'Unfilter sheet only if there is a filter
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
'last row of newly opened file
AgentBreakdownlRow = Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("C" & Rows.Count).End(xlUp).Row
'Give everyone the same date and week
Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("H2:I2").Copy Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("H3:I" & AgentBreakdownlRow)
'Sort A - Z Vendor file
Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("A1:Q" & AgentBreakdownlRow).Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes
'Format date cells
Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("I:I").NumberFormat = "m/dd/yyyy"
''last row of master sheet
lRow = Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("A" & Rows.Count).End(xlUp).Row
'
''Check what vendor and filter it accordingly in per agent breakdown sheet
vendor = Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("A2").Value
DT = Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("I2").Value
'Apply Vendor Filter
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("A1:S" & lRow).AutoFilter Field:=1, Criteria1:=vendor
'Apply Date Filter
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("A1:S" & lRow).AutoFilter Field:=9, Criteria1:=DT
'Clear the sorted field
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").AutoFilter.Sort.SortFields.Clear
'Sort A - Z Master file
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").AutoFilter.Sort.SortFields.Add Order:=xlAscending, _
SortOn:=xlSortOnValues, Key:=Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("E1:E" & lRow)
'Applying the sort
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").AutoFilter.Sort.Apply
'adjust training hours adjustment column of master sheet based on columns total training hours and training hours
'adjustment of vendor sheet
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Activate
Worksheets("Per Agent Per Day Hr Breakdown").Select
fRow = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
lastRow = Worksheets("Per Agent Per Day Hr Breakdown").Range("A" & Rows.Count).End(xlUp).Row
MsgBox fRow
MsgBox lastRow
'The formulas I am trying to use are
'Range("O2").Formula = "=SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E2, '[" & fName & "]Per Agent Per Day Hr Breakdown'!$J:$J) + SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E2, '[" & fName & "]Per Agent Per Day Hr Breakdown'!$O:$O) - J2"
'Range("O2").Select
'Selection.AutoFill Destination:=Range("O2" & ":O" & lRow), Type:=xlFillDefault
'With Range("O2" & ":O" & lRow)
' .Value = .Value
'End With
' Range("N" & fRow).Formula = "=SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E" & fRow & ", '[" & fName & "]Per Agent Per Day Hr Breakdown'!$K:$K) + SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E" & fRow & ", '[" & fName & "]Per Agent Per Day Hr Breakdown'!$N:$N) - K" & fRow & ""
'Range("N" & fRow).Select
'Selection.AutoFill Destination:=Range("N" & fRow & ":N" & lastRow), Type:=xlFillDefault
'With Range("N" & fRow & ":N" & lastRow)
' .Value = .Value
'End With
'Range("M" & fRow).Formula = "=VLOOKUP(E&fRow, '[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$M, 11, FALSE)"
''Range("M" & fRow).Select
'Selection.AutoFill Destination:=Range("M" & fRow & ":M" & lastRow), Type:=xlFillDefault
'With Range("M" & fRow & ":M" & lastRow)
'.Value = .Value
'End With
//drag down the formula ignore the hidden cells