View Full Version : [SOLVED:] Find VBA Multiple criterias
agfac
12-16-2013, 03:00 PM
Hi,
I made an userform to search values on columns in excel and give me the respective value of another column.
Those values are items of comboboxs.
The code is below but there must have some error because it's only working for "efetivo" = 3.
Could someone help me please?
Thanks
Private Sub CommandButton45_Click()
areaci = CB_fcci_potencia_area.Value
efetivo = CB_fcci_potencia_efetivo.Value
Dim searchRange As Range, lastCell As Range
If CB_fcci_potencia_sinal.Value = "Sim" _
And CB_fcci_potencia_ilum.Value = "Sim" _
And CB_fcci_potencia_simul.Value = "Sim" _
And CB_fcci_potencia_det.Value = "Sem SADI" _
And CB_fcci_potencia_sea.Value = "Não" _
Then
Set searchRange = Worksheets("Tabela (potência) (2)").Range("e3:e120")
Set lastCell = searchRange.Cells(searchRange.Cells.Count)
Set areacifound = searchRange.Find(what:=areaci, after:=lastCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If Not areacifound Is Nothing And areacifound.Offset(0, 5) = efetivo Then
firstaddress = areacifound.Address
Do
Set areacifound = searchRange.FindNext(areacifound)
Loop Until areacifound.Address = firstaddress
End If
TextBox23.Value = areacifound.Offset(0, 11)
End If
End Sub
:dunno Try this. :dunno
Private Sub CommandButton45_Click()
areaci = CB_fcci_potencia_area.Value
efetivo = CB_fcci_potencia_efetivo.Value
Dim searchRange As Range, lastCell As Range
If Not (CB_fcci_potencia_sinal.Value = "Sim" _
And CB_fcci_potencia_ilum.Value = "Sim" _
And CB_fcci_potencia_simul.Value = "Sim" _
And CB_fcci_potencia_det.Value = "Sem SADI" _
And CB_fcci_potencia_sea.Value = "Não") _
Then Exit Sub
Set searchRange = Worksheets("Tabela (potência) (2)").Range("e3:e120")
Set lastCell = searchRange.Cells(searchRange.Cells.Count)
Set areacifound = searchRange.Find(what:=areaci, after:=lastCell, MatchCase:=False)
If (Not areacifound Is Nothing) And areacifound.Offset(0, 5) = efetivo Then
firstaddress = areacifound.Address
Else
Do
Set areacifound = searchRange.FindNext(areacifound)
Loop Until (areacifound Is firstaddress Or areacifound.Offset(0, 5) = efetivo)
End If
If (Not areacifound Is Nothing) And areacifound.Offset(0, 5) = efetivo Then _
TextBox23.Value = areacifound.Offset(0, 11)
End Sub
agfac
12-16-2013, 06:20 PM
Change:
If Not areacifound Is Nothing And areacifound.Offset(0, 5) = efetivo Then
firstaddress = areacifound.Address
to:
If Not areacifound Is Nothing
MsgBox("AreaFound Offset Value is " & areacifound.Offset(0, 5).value & _
CHR(13) & "effitivo is " & CB_fcci_potencia_efetivo.Value)
End If
If Not areacifound Is Nothing And areacifound.Offset(0, 5) = efetivo Then
firstaddress = areacifound.Address
This will verify that the problem is in efetivo
But this will not solve the problem. This will only show a msgbox. Am I right?
Thanks
Ignore please. I see the problem.
See new edit above.
You read me too fast for my typing (and thinking :D)
agfac
12-16-2013, 06:55 PM
It's only working to "efetivo" = 3.
To another values of "efetivo" it gives an error (424: Object required) and underline this line:
Loop Until (areacifound Is firstaddress Or areacifound.Offset(0, 5) = efetivo)
Hi there,
Could you post a sample workbook, with similar data in the right places?
Mark
agfac
12-16-2013, 07:01 PM
Hi there,
Could you post a sample workbook, with similar data in the right places?
Mark
Hi,
The workbook is too large (92mb).
I will try to post links of printscreens.
Thanks
agfac
12-16-2013, 07:06 PM
Here it is:
agfac
12-16-2013, 07:06 PM
Here it is:
https://www.dropbox.com/sh/kvbfgudqbuz2pay/0hHcqupt0w
Hi,
The workbook is too large (92mb).
I will try to post links of printscreens.
Thanks
Hi again,
Pictures are of less use, as each and every potential "answer" must create a workbook, hoping that it accurately portrays yours. I don't believe we need all 92MB's, just the range (and any offset cells that are to return a value) being searched. That and the userform working about the way you currently have it. I hope I'm not missing anything, but that is what I gleaned from the code snippet.
Mark
agfac
12-16-2013, 07:20 PM
I've deleted unnecessary sheets and there it is:
https://www.dropbox.com/s/j243ncg0l0fnri2/Workbook1.xlsm
Okay, if I am deciphering what we want to do correctly, the reason why your first code only returned if both conditions were met.
If Not areacifound Is Nothing And areacifound.Offset(0, 5) = efetivo Then
Thus, while areacifound would return a range, this range would always initially be on the same row that offset(,5) would have a value of 3. Does that make sense?
Try something like:
At the top of the module:
Dim areaci As Long ' Integer
Dim efetivo As Long ' Integer
I changed this as there is not really a reason to use Integer, and I chose to coerce to Longs later.
For your Procedure:
Private Sub CommandButton45_Click()
Dim AreaciFound As Range
Dim FirstAddress As String
' IMO, best to specify/force to a number, rather than depend on proper conversion
areaci = CLng(CB_fcci_potencia_area.Value)
efetivo = CLng(CB_fcci_potencia_efetivo.Value)
Dim SearchRange As Range, lastCell As Range
' I pre-populated just for debugging.
CB_fcci_potencia_sinal.Value = "Sim"
CB_fcci_potencia_ilum.Value = "Sim"
CB_fcci_potencia_simul.Value = "Sim"
CB_fcci_potencia_det.Value = "Sem SADI"
CB_fcci_potencia_sea.Value = "Não"
If Not (CB_fcci_potencia_sinal.Value = "Sim" _
And CB_fcci_potencia_ilum.Value = "Sim" _
And CB_fcci_potencia_simul.Value = "Sim" _
And CB_fcci_potencia_det.Value = "Sem SADI" _
And CB_fcci_potencia_sea.Value = "Não") _
Then Exit Sub
' E3 is a Header, so this seemed like it should start at E4
' Set SearchRange = Worksheets("Tabela (potência) (2)").Range("e3:e120")
Set SearchRange = Worksheets("Tabela (potência) (2)").Range("E4:E120")
Set lastCell = SearchRange.Cells(SearchRange.Cells.Count)
Set AreaciFound = SearchRange.Find(What:=areaci, After:=lastCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
'If we are looking for the first row where both conditions are met, first I would just
'test to see if we found 'areaci'.
If AreaciFound Is Nothing Then
MsgBox areaci & "was not found.", vbInformation, vbNullString
Exit Sub
End If
'Then test to see if the offset(,5) equals 'efetivo'. If yes, return the offset(,11) to
'the textbox; else, then use the Do...Loop to go through the rest of the cells where we
'areaci
If AreaciFound.Offset(, 5) = efetivo Then
TextBox23.Value = AreaciFound.Offset(, 11).Value
Else
FirstAddress = AreaciFound.Address
Do
Set AreaciFound = SearchRange.FindNext(AreaciFound)
If AreaciFound.Offset(, 5) = efetivo Then
TextBox23.Value = AreaciFound.Offset(, 11).Value
Exit Do
End If
Loop While Not AreaciFound.Address = FirstAddress
End If
End Sub
I hope that helps :-)
Mark
Yikes! That first part was supposed to look like:
If Not AreaciFound Is Nothing And AreaciFound.Offset(0, 5) = efetivo Then
(My fault, I should have realized copying from the web page would catch extra "stuff" that would not render correctly)
My office computer is down, so expect some typos
Private Sub CommandButton45_Click()
'As written, puts some comments in TextBox23. See comments to leave blank if correct result not found
areaci = CB_fcci_potencia_area.Value
efetivo = CB_fcci_potencia_efetivo.Value
Dim searchRange As Range, lastCell As Range
Dim Result As String
If Not (CB_fcci_potencia_sinal.Value = "Sim" _
And CB_fcci_potencia_ilum.Value = "Sim" _
And CB_fcci_potencia_simul.Value = "Sim" _
And CB_fcci_potencia_det.Value = "Sem SADI" _
And CB_fcci_potencia_sea.Value = "Não") _
Then Exit Sub
Result = FindEfetivo CB_fcci_potencia_area, CB_fcci_potencia_efetivo
'If Result Not "False" Then 'See Function FindEfetivo comments before using
TextBox23.Value = Result
'Else TextBox23.Value = ""
'End If
End Sub
Private Function FindEfetivo(areaci As String, efetivo As String) As String
'Finds areaci is Col (E), Compares Col(J) to efetivo. IF match Returns Col(P). Else Returns message String
Const J As Long = 5 'Offset as Column Letter
Const P As Long = 11
Dim SearchRange As Range
Dim LastCell As Range
Dim AreaciFound As Range
Dim FirstFound As Range
Set searchRange = Worksheets("Tabela (potência) (2)").Range("e3:e120")
Set lastCell = searchRange.Cells(searchRange.Cells.Count)
Set areacifound = searchRange.Find(what:=areaci, after:=lastCell, MatchCase:=False)
If AreaciFound Is Nothing Then
FindEfetivo = "Areaci Not Found" 'Or use "False" 'See Sub above
Exit Function
End If
If areacifound.Offset(0, J) = efetivo Then 'First found is the correct one
FindEfetivo = areacifound.Offset(0, P)
Exit Function
End If
''''Testing
MsgBox(Areaci Found in Row " & AreaciFound.Row & "Efetivo is " & areacifound.Offset(0, J))
''''End Testing
Set FirstFound = AreaciFound
'
Do
Set areacifound = SearchRange.FindNext(areaci)
If areacifound.Offset(0, J) = efetivo Then
FindEfetivo = areacifound.Offset(0, P)
Exit Function
''''Testing
Else
MsgBox(Areaci Found in Row " & AreaciFound.Row & ": Efetivo is " & areacifound.Offset(0, J))
''''End Testing
End If
Loop Until areacifound Is FirstFound 'If Code is here then matching Efetvo was not found
FindEfetivo = "Matching Efetivo Not Found" 'or use "False"
End Sub
To test the Function FindEfetivo(), adjust string values often and run this sub
Sub utFindEfetivo()
'"ut" indicates a Unit Test Sub. Should have same name as sub/function testing
Dim Areaci As String
Dim Efetico As String
'Use various values as needed
Areaci = "String1"
Efetico = "String2"
MSgBox("For Areaci = " & Areaci & " Efetico = " Efetico & " returns: " & FindEfetico(Areaci, Efetico))
End Sub
agfac
12-18-2013, 06:11 PM
Okay, if I am deciphering what we want to do correctly, the reason why your first code only returned if both conditions were met.
If Not areacifound Is Nothing And areacifound.Offset(0, 5) = efetivo Then
Thus, while areacifound would return a range, this range would always initially be on the same row that offset(,5) would have a value of 3. Does that make sense?
Try something like:
At the top of the module:
Dim areaci As Long ' Integer
Dim efetivo As Long ' Integer
I changed this as there is not really a reason to use Integer, and I chose to coerce to Longs later.
For your Procedure:
Private Sub CommandButton45_Click()
Dim AreaciFound As Range
Dim FirstAddress As String
' IMO, best to specify/force to a number, rather than depend on proper conversion
areaci = CLng(CB_fcci_potencia_area.Value)
efetivo = CLng(CB_fcci_potencia_efetivo.Value)
Dim SearchRange As Range, lastCell As Range
' I pre-populated just for debugging.
CB_fcci_potencia_sinal.Value = "Sim"
CB_fcci_potencia_ilum.Value = "Sim"
CB_fcci_potencia_simul.Value = "Sim"
CB_fcci_potencia_det.Value = "Sem SADI"
CB_fcci_potencia_sea.Value = "Não"
If Not (CB_fcci_potencia_sinal.Value = "Sim" _
And CB_fcci_potencia_ilum.Value = "Sim" _
And CB_fcci_potencia_simul.Value = "Sim" _
And CB_fcci_potencia_det.Value = "Sem SADI" _
And CB_fcci_potencia_sea.Value = "Não") _
Then Exit Sub
' E3 is a Header, so this seemed like it should start at E4
' Set SearchRange = Worksheets("Tabela (potência) (2)").Range("e3:e120")
Set SearchRange = Worksheets("Tabela (potência) (2)").Range("E4:E120")
Set lastCell = SearchRange.Cells(SearchRange.Cells.Count)
Set AreaciFound = SearchRange.Find(What:=areaci, After:=lastCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
'If we are looking for the first row where both conditions are met, first I would just
'test to see if we found 'areaci'.
If AreaciFound Is Nothing Then
MsgBox areaci & "was not found.", vbInformation, vbNullString
Exit Sub
End If
'Then test to see if the offset(,5) equals 'efetivo'. If yes, return the offset(,11) to
'the textbox; else, then use the Do...Loop to go through the rest of the cells where we
'areaci
If AreaciFound.Offset(, 5) = efetivo Then
TextBox23.Value = AreaciFound.Offset(, 11).Value
Else
FirstAddress = AreaciFound.Address
Do
Set AreaciFound = SearchRange.FindNext(AreaciFound)
If AreaciFound.Offset(, 5) = efetivo Then
TextBox23.Value = AreaciFound.Offset(, 11).Value
Exit Do
End If
Loop While Not AreaciFound.Address = FirstAddress
End If
End Sub
I hope that helps :-)
Mark
It works!
Thank you very much!
What about using autofilter ?
You can use 1 criterion or multiple criteria.
sub M_snb()
Range("E4:E120").autofilter 1,CB_fcci_potencia_area.Value
Range("J4:J120").autofilter 1,CB_fcci_potencia_efetivo.Value
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.