Find VBA Multiple criterias

[agfac]

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

[SamT]

: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]

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

[SamT]

Ignore please. I see the problem.

See new edit above.

You read me too fast for my typing (and thinking :D)

[agfac]

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)

[GTO]

Hi there,

Could you post a sample workbook, with similar data in the right places?

Mark

[agfac]

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]

Here it is:

[agfac]

Here it is:


https://www.dropbox.com/sh/kvbfgudqbuz2pay/0hHcqupt0w

[GTO]

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]

I've deleted unnecessary sheets and there it is:

https://www.dropbox.com/s/j243ncg0l0fnri2/Workbook1.xlsm

[GTO]

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

[GTO]

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)

[SamT]

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

[SamT]

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]

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!

[GTO]

You are most welcome :-)

[snb]

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