0
I am currently working on a code where I have to filter a certain list and go through the filtered data with a FOR and also perform some comparisons with IF. Follows the code
Sub compararValores()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim nKm As Variant
Dim locBase As Variant
Dim nmetros As Variant
Dim ext As Variant
Dim exthist As Variant
Dim sentido As String
Dim sentidoHistorico As String
Dim locInicial As Variant
Dim dateBase As Date
Dim dateHist As Date
Dim dateHistProx As Date
Dim hrHist As Variant
Dim hrHistProx As Variant
Dim comp As Variant
Dim compProx As Variant
Dim larg As Variant
Dim largProx As Variant
Dim esp As Variant
Dim espProx As Variant
Dim faixa As Variant
Dim faixaProx As Variant
ext = Base.Range("n2").End(xlDown).Row
For i = 2 To ext
nKm = Base.Range("n" & i).Value
nmetros = Base.Range("o" & i).Value
locBase = nKm & "." & nmetros
sentido = Base.Range("p" & i).Value
dateBase = Base.Range("d" & i).Value
dateBaseProx = Base.Range("d" & i + 1).Value
If (sentido = "N" Or sentido = "n") Then
sentido = "NORTE"
Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("$A$1:$AL$30182").AutoFilter Field:=17, Criteria1:= _
"<" & locBase + 5, Operator:=xlAnd
Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("$A$1:$AL$30182").AutoFilter Field:=18, Criteria1:= _
">" & locBase - 5, Operator:=xlAnd
Else
sentido = "SUL"
Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("$A$1:$AL$30182").AutoFilter Field:=17, Criteria1:= _
">" & locBase - 5, Operator:=xlAnd
Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("$A$1:$AL$30182").AutoFilter Field:=18, Criteria1:= _
"<" & locBase + 5, Operator:=xlAnd
End If
Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("$A$1:$AL$30182").AutoFilter Field:=13, Criteria1:=sentido
exthist = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
For j = 2 To exthist
With Worksheets("Memória Intervenções").AutoFilter.Range
teste = Range("r" & .Offset(i, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
locInicial = CStr(Worksheets("Memória Intervenções").Range("q" & .Offset(j, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2)
locFinal = CStr(Worksheets("Memória Intervenções").Range("r" & .Offset(j, 0).pecialCells(xlCellTypeVisible)(1).Row).Value2)
Debug.Print (locInicial)
Debug.Print (locFinal)
End With
sentidoHistorico = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("m" & j).Value
locInicial = CStr(Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("q" & j).Value)
locFinal = CStr(Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("r" & j).Value)
dateHist = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("b" & j).Value
dateHistProx = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("b" & j + 1).Value
comp = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("f" & j).Value
compProx = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("f" & j + 1).Value
larg = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("g" & j).Value
largProx = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("g" & j + 1).Value
esp = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("h" & j).Value
espProx = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("h" & j + 1).Value
faixa = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("n" & j).Value
faixaProx = Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("n" & j + 1).Value
If (locBase >= locInicial And locBase <= locFinal) Then
If (sentido = sentidoHistorico) Then
If (dateBase <= dateHist) Then
If (dateHist <= dateHistProx) Then
If (comp = compProx And larg = largProx And esp = espProx And faixa = faixaProx) Then
'i = 3
Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("c" & j).Copy
Base.Range("ar" & i).PasteSpecial
Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("c" & j + 1).Copy
Base.Range("au" & i).Offset(0, i).PasteSpecial
Else
If (faixa <> faixaProx) Then
Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("n" & j).Copy
Base.Range("at" & i).PasteSpecial
Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("c" & j).Copy
Base.Range("au" & i).PasteSpecial
End If
Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("c" & j).Copy
Base.Range("ar" & i).PasteSpecial
Workbooks("Histórico 2013-2021.xlsx").Worksheets("Memória Intervenções").Range("n" & j).Copy
Base.Range("as" & i).PasteSpecial
GoTo prox
End If
End If
End If
End If
End If
Next j
Prox: Next i
Workbooks("History 2013-2021.xlsx"). Worksheets("Memory Interventions"). Showalldata Application.Calculation = xlCalculationAutomatic Application.Screenupdating = True End Sub
My question is that my variables locInicial and locFinal do not update values by going to the next J, it is always permanent with the first value of the filtered list. The expected result for the two variables would be the next values of the list.
Rephrase the doubt, put an example of what the data looks like, the expected result, and what you’ve already programmed, makes it easier to understand and help. Behold: How to create a Minimum, Complete and Verifiable example and How to ask a good question?.
– Jean Barbosa
Updated the question
– Iago Gonalves
Still confused, not knowing the initial structure and expected result. But, from what you said is having trouble loading the value in the variables, try to simplify their reference, I believe there are commands there that you should not need. Example: from: ". Range("q" & .Offset(j, 0).Specialcells(xlCellTypeVisible)(1).Row). Value2)" to: ". Range("Q" & j)".
– Jean Barbosa
Thank you for the answer. I updated the question again.
– Iago Gonalves
Making this suggested change, vba would not understand that I am referring to the filtered values and yes to the next values of the list
– Iago Gonalves
I get it. Without knowing the initial data structure, it’s hard to replicate. You may have a problem running a "run" For, without considering the filtered Range only, offset may be impairing cell addressing. An alternative would be to do For in all cells, checking if the line is visible (with an if) and then executing the code you need on that line/address. Example code: For x = 2 To 10 If Range("A" & x).EntireRow.Hidden = False Then Debug.Print Range("A" & x). Value End If Next x
– Jean Barbosa
This is a good way out and I thank you for addressing it. I managed to solve through other similar methods, however, I would like to know why my syntax (shown above in the question) is not working, so in the future not to make the same mistake.
– Iago Gonalves