(Solved) – Problem with multiple criteria in autofilter in Excel 2013, In 2016 work fine

(solved)-–-problem-with-multiple-criteria-in-autofilter-in-excel-2013,-in-2016-work-fine

Im working in this code, it works well in my home pc with office 2016 but in office 2013 doesn´t work and pops out multiple troubles. I want to discard any bad code so it can work fine. Thank you. The base data is “SISCAD” and “CARGA” is when the filtered data is going to be paste. Also im kind of new and is my first post.

Dim ws As Excel.Worksheet
Dim FilterArea As Excel.Range
Dim RowsCount As Integer
Dim nro_filas As Integer

Sub LLENADO()

Application.ScreenUpdating = False
Windows("LIQUIDACIÓN.xlsx").Activate
'Crear Hoja CARGA
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveWorkbook.Sheets(Worksheets.Count).Name = "CARGA"
'copia cabecera carga de hoja data:
   Windows("MEJORA.xlsm").Activate
   Sheets("DATA").Visible = True
   Sheets("DATA").Select
   Rows("1:1").Select
   Selection.Copy
   Windows("LIQUIDACIÓN.xlsx").Activate
   Range("A1").Select
   ActiveSheet.Paste
'Formato texto a serie/grupo de material/OC
Sheets("CARGA").Range("K:K,N:N,M:M").NumberFormat = "@"
Sheets("SISCAD").Activate

With Sheets("SISCAD") '<--| reference your sheet
Set ws = ActiveSheet
       .AutoFilterMode = False '<--| remove any existing filtering
       With Sheets("SISCAD").Range("A1", .Cells(.Rows.Count, 5).End(xlUp)) '<--| reference its range in columns A:B from row 1 down to column A last not empty row
           .AutoFilter Field:=1, Criteria1:="X", Operator:=xlFilterValues '<--| filter on referenced range 1st column with "OPEN" or "CLOSED" values
           .AutoFilter Field:=5, Criteria1:=Array("CHIP", "EQUIPO"), Operator:=xlFilterValues '<--| filter on referenced range 2nd column with "1/0/1900" value


ws.AutoFilter.Range.Offset(1, 3).Resize(ws.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(12).Copy Sheets("CARGA").Range("K2")
ws.AutoFilter.Range.Offset(1, 6).Resize(ws.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(12).Copy Sheets("CARGA").Range("J2")
ws.AutoFilter.Range.Offset(1, 17).Resize(ws.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(12).Copy Sheets("CARGA").Range("H2")
ws.AutoFilter.Range.Offset(1, 21).Resize(ws.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(12).Copy Sheets("CARGA").Range("M2")
ws.AutoFilter.Range.Offset(1, 20).Resize(ws.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(12).Copy Sheets("CARGA").Range("N2")
ws.AutoFilter.Range.Offset(1, 8).Resize(ws.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(12).Copy Sheets("CARGA").Range("T2")


       End With
End With
Sheets("CARGA").Select
Sheets("CARGA").Range("J:J").Select
With Selection
   .NumberFormat = "General"
   .Value = .Value
End With

Dim k As Long

   k = Sheets("CARGA").Range("K1", Sheets("CARGA").Range("K1").End(xlDown)).Rows.Count
       RowsCount = k

Call formula

Windows("LIQUIDACIÓN.xlsx").Activate
'Crear Hoja CARGA
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveWorkbook.Sheets(Worksheets.Count).Name = "RESUMEN"
Windows("MEJORA.xlsm").Activate
   Sheets("DATA").Visible = True
   Sheets("DATA").Select
   Rows("45:56").Select
   Selection.Copy
   Windows("LIQUIDACIÓN.xlsx").Activate
   Range("A1").Select
   ActiveSheet.Paste

   Sheets("SISCAD").Activate

''SUMA MONTO DE VENTA EN CUOTAS
With Sheets("SISCAD") '<--| reference your sheet
Set ws = ActiveSheet
       .AutoFilterMode = False '<--| remove any existing filtering
       With Sheets("SISCAD").Range("A1", .Cells(.Rows.Count, 5).End(xlUp)) '<--| reference its range in columns A:B from row 1 down to column A last not empty row
           .AutoFilter Field:=1, Criteria1:="C", Operator:=xlFilterValues '<--| filter on referenced range 1st column with "OPEN" or "CLOSED" values
           .AutoFilter Field:=5, Criteria1:=Array("EQUIPO"), Operator:=xlFilterValues  '<--| filter on referenced range 2nd column with "1/0/1900" value
Dim rc As Range, vc As Variant
   Set rc = ws.AutoFilter.Range.Offset(1, 24).Resize(ws.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(12)
   vc = Application.WorksheetFunction.Sum(rc)
   Sheets("RESUMEN").Range("A2").Value = WorksheetFunction.RoundUp(vc, 3)

       End With
End With

''SUMA MONTO DE VENTA EN RA
With Sheets("SISCAD") '<--| reference your sheet
Set ws = ActiveSheet
       .AutoFilterMode = False '<--| remove any existing filtering
       With Sheets("SISCAD").Range("A1", .Cells(.Rows.Count, 5).End(xlUp)) '<--| reference its range in columns A:B from row 1 down to column A last not empty row
           .AutoFilter Field:=1, Criteria1:="X", Operator:=xlFilterValues '<--| filter on referenced range 1st column with "OPEN" or "CLOSED" values
           .AutoFilter Field:=5, Criteria1:=Array("RA"), Operator:=xlFilterValues  '<--| filter on referenced range 2nd column with "1/0/1900" value
Dim ra As Range, vra As Variant
   Set ra = ws.AutoFilter.Range.Offset(1, 8).Resize(ws.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(12)
   vra = Application.WorksheetFunction.Sum(ra)
   Sheets("RESUMEN").Range("A3").Value = WorksheetFunction.RoundUp(vra, 3)

       End With
End With

'' Suma de monto de Carga en RESUMEN

   Dim r As Range
   Dim v As Variant
   Set r = Sheets("CARGA").Range("w2", Sheets("CARGA").Cells(Sheets("CARGA").Rows.Count, 23).End(xlUp))
   v = Application.WorksheetFunction.Sum(r)
   Sheets("RESUMEN").Range("B9").Value = WorksheetFunction.RoundUp(v, 3)
   Sheets("RESUMEN").Range("F7").Value = WorksheetFunction.RoundUp(v, 3)

ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveWorkbook.Sheets(Worksheets.Count).Name = "CARTA"
Windows("MEJORA.xlsm").Activate
   Sheets("DATA").Visible = True
   Sheets("DATA").Select
   Rows("57:88").Select
   Selection.Copy
   Windows("LIQUIDACIÓN.xlsx").Activate
   Range("A1").Select
   ActiveSheet.Paste


   Sheets("CARGA").Select
Sheets("CARGA").Range("A10:A12", "A17").Select
With Selection
   .NumberFormat = "General"
   .Value = .Value
End With

Windows("MEJORA.xlsm").Activate

Sheets("DATA").Visible = False
Windows("LIQUIDACIÓN.xlsx").Activate

Application.ScreenUpdating = True

End Sub

Sub formula()

For x = 2 To RowsCount
Sheets("CARGA").Select
' 1.ENTRADA DE DATOS
   cliente = Cells(x, 3).Value
   clase_pedido = Cells(x, 4).Value
   org_venta = Cells(x, 5).Value
   canal_dist = Cells(x, 6).Value
   sector = Cells(x, 7).Value
   fecha_precio = Cells(x, 8)
   fecha_hoy = Cells(x, 9)
   precio = Cells(x, 20)
   igv = Cells(x, 21)
   p_85 = Cells(x, 22)
   p_cad = Cells(x, 23)
   'p_base = Cells(x, 24)
   'entrega = Cells(x, 25)
   'dif= Cells(x, 25)
   resulatado1 = Cells(x, 27)
   resulatado2 = Cells(x, 28)

' 2.PROCESO LOGICO O DE CALCULO

   cliente = Application.VLookup(Sheets("SISCAD").Cells(2, 2), Range(Workbooks("MEJORA").Sheets("DATA").Cells(15, 3), Workbooks("MEJORA").Sheets("DATA").Cells(43, 11)), 2, False)
   'Cells(f, 9) = Application.VLookup(Cells(i, 3), Range(Workbooks("SEGUIMIENTO 2016-Beta").Sheets("DETALLE").Cells(3, 4), Workbooks("SEGUIMIENTO 2016-Beta").Sheets("DETALLE").Cells(u, 4)), 1, False)
 '"= VLOOKUP(SISCAD!RC,'[MEJORA.xlsm]DATA'!R1C28:R29C29,2,0)"
   clase_pedido = "ZKE"
   org_venta = Application.VLookup(Sheets("SISCAD").Cells(2, 2), Range(Workbooks("MEJORA").Sheets("DATA").Cells(15, 3), Workbooks("MEJORA").Sheets("DATA").Cells(43, 11)), 5, False)
   canal_dist = "13"
   sector = "21"
   fecha_cierre = Sheets("CARGA").Cells(x, 8)
   fecha_hoy = Date
   precio = Sheets("SISCAD").Cells(x, 9)
   igv = precio / 1.18
   p_85 = precio * (Application.VLookup(Sheets("SISCAD").Cells(2, 2), Range(Workbooks("MEJORA").Sheets("DATA").Cells(15, 3), Workbooks("MEJORA").Sheets("DATA").Cells(43, 11)), 4, False))
   p_cad = igv * (Application.VLookup(Sheets("SISCAD").Cells(2, 2), Range(Workbooks("MEJORA").Sheets("DATA").Cells(15, 3), Workbooks("MEJORA").Sheets("DATA").Cells(243, 11)), 4, False))
   p_base = ""
   estado = ""
   resulatado1 = WorksheetFunction.Text(fecha_cierre, "dd") & "." & WorksheetFunction.Text(fecha_cierre, "mm") & "." & WorksheetFunction.Text(fecha_cierre, "yyyy")
   resulatado2 = WorksheetFunction.Text(fecha_hoy, "dd") & "." & WorksheetFunction.Text(fecha_hoy, "mm") & "." & WorksheetFunction.Text(fecha_hoy, "yyyy")

' 3.RESULTADO
   Cells(x, 3).Value = cliente
   Cells(x, 4).Value = clase_pedido
   Cells(x, 5).Value = org_venta
   Cells(x, 6).Value = canal_dist
   Cells(x, 7).Value = sector
   Cells(x, 9).Value = fecha_hoy
   Cells(x, 15).Value = "10"
   Cells(x, 20).Value = precio
   Cells(x, 21).Value = igv
   Cells(x, 22).Value = p_85
   Cells(x, 23).Value = p_cad
   'Cells(x, 24).Value = p_base
   'Cells(x, 25).Value = entrega
   'Cells(x, 26).Value = dif
   Cells(x, 27) = resulatado1
   Cells(x, 28) = resulatado2

Next x



Application.CutCopyMode = False

   filas = Range("C1").End(xlDown).Row
   Range("AA2", "AB" & filas).Cut
   Range("H2").Select
   ActiveSheet.Paste


Application.CutCopyMode = True

End Sub


Leave a Reply

Your email address will not be published. Required fields are marked *