I've the forum a couple of times to check for answers, but this is the first time I ask a question.
I wrote a code that needs to find a couple of strings, and the actions are based on which strings it has found first. The problem is: right now I have a bunch of "ifs" and I think my code could be smaller. I think I'm writing the same thing twice, but I can't see how to write only once Also, I created some functions to make the code smaller.
I thought about using a Loop until I find one of the words, but I can't find a good way to exit the loop and tell which the word I found. Or use Select Case.
Thank you for your help and I apologize if I haven't followed some rule here.
Below is the function that I use in the code:
Function FindCell(FoundCell As Range, Text As String, RowUnid1 As Integer, RowUnid2 As Integer)
Set wb = ActiveWorkbook
With wb.Sheets("Preparation")
Set FoundCell = .Range(("A" & RowUnid1), ("A" & RowUnid2)).Find(What:=Text, LookIn:=xlValues, SearchOrder:=xlByColumns)
End With
End Function
This is part of the code, with comments explaining
Call FindCell(FoundCell, "Próxima Intervenção:", RowUnid1, RowUnid2)
If FoundCell Is Nothing Then 'If it doesn't find "Próxima Intervenção:"
Call FindCell(FoundCell, "Próximas Interven", RowUnid1, RowUnid2) 'Tries to find "Próximas Interven"
If FoundCell Is Nothing Then 'If it does not find "Próximas Interven"
Call FindCell(FoundCell, "Previsão de uti", RowUnid1, RowUnid2) 'Tries to find "Previsão de uti"
If FoundCell Is Nothing Then 'If it does not find "Previsão de uti"
Call FindCell(FoundCell, "Previsão para", RowUnid1, RowUnid2) 'Tries to find "Previsão para"
If FoundCell Is Nothing Then 'If it does not find "Previsão para"
Call FindRow(RowContato, "Contato", RowUnid1, RowUnid2) 'Finds "Contato", which is a string it will always find
Call BreakLineTest(RowObjetivo, RowContato, "F", i) 'This is a function that determines wether it's going to copy 1, 2 or 3 lines, starting at the row where it found the word "objetivo" (RowObjetivo) (This part is not in here in the pasted code), using the row "contato" as a parameter
Else 'If it finds "Previsão para"
RowPrevisao = FoundCell.Row 'assigns the number of the row where it found "Previsão para"
Call BreakLineTest(RowObjetivo, RowPrevisao, "F", i) 'Now it uses RowPrevisao as a parameter
End If
Else 'If it finds "Previsão de"
RowPrevisao = FoundCell.Row
Call BreakLineTest(RowObjetivo, RowPrevisao, "F", i) 'Now it uses RowPrevisao as a parameter
End If
Else 'If it finds "Próximas Interven"
RowProxInt = FoundCell.Row
Call BreakLineTest(RowObjetivo, RowProxInt, "F", i) 'Uses RowProxInt as a parameter
Call FindCell(FoundCell, "Previsão de uti", RowUnid1, RowUnid2) 'Finds "Previsao de uti"
If FoundCell Is Nothing Then 'If it does not find Previsão de"
Call FindCell(FoundCell, "Previsão para", RowUnid1, RowUnid2) 'finds "Previsão para"
If FoundCell Is Nothing Then 'If it doesn't find "Previsão para"
Call FindRow(RowContato, "Contato", RowUnid1, RowUnid2) 'Finds "contato"
Call BreakLineTest(RowProxInt, RowContato, "G", i) 'Uses RowContato as a parameter
Else 'If it finds "Previsão para"
RowPrevisao = FoundCell.Row
Call BreakLineTest(RowProxInt, RowPrevisao, "G", i) 'Uses RowProxInt as a parameter
End If
Else 'If it finds "Previsão de"
RowPrevisao = FoundCell.Row
Call BreakLineTest(RowProxInt, RowPrevisao, "G", i) 'Uses ProxInt as parameter
End If
End If
Else 'If it finds "Próxima Intervenção"
RowProxInt = FoundCell.Row
Call BreakLineTest(RowObjetivo, RowProxInt, "F", i) 'Uses ProxInt as parameter
Call FindCell(FoundCell, "Previsão de uti", RowUnid1, RowUnid2) 'Tries to find "Previsão de"
If FoundCell Is Nothing Then 'If it doesn't find "Previsão de"
Call FindCell(FoundCell, "Previsão para uti", RowUnid1, RowUnid2) 'Tries to find "Previsão para"
If FoundCell Is Nothing Then 'If it doesn't find "Previsão para"
Call FindRow(RowContato, "Contato", RowUnid1, RowUnid2) 'Finds "contato"
Call BreakLineTest(RowProxInt, RowContato, "G", i)
Else 'If it finds "Previsão para"
RowPrevisao = FoundCell.Row
Call BreakLineTest(RowProxInt, RowPrevisao, "G", i)
End If
Else 'If it finds "Previsão de"
RowPrevisao = FoundCell.Row
Call BreakLineTest(RowProxInt, RowPrevisao, "G", i)
End If
End If
Aucun commentaire:
Enregistrer un commentaire