Darkveemon1
New Member
- Joined
- Jul 12, 2023
- Messages
- 11
- Office Version
- 365
- Platform
- Windows
Hi, I'm new, thanks in advance for the help. I have some VBA code that is bithering me and I cant figure why it doesn't work.
I'm writing a function that accepts a string (right now the number is formatted as string) and returns an integer (I'm debugging in my own way to see the problem) to find some anomalies.
I'll explain briefly what am I trying to do.
In the first sheet I have some values (supporto) as String and each one has a code associated (prodotto).
I'm using those values to make a search in the other sheets.
1) I search the value in the second sheet (errore) in the column F, if I find it there the function sets a String (risultato) to "KO" (wich will be the output of the function in the end). If the value isn't there the function ends and sets the String to "OK"
2) I'm using a boolean (anomalia) to set if the value is an anomalie
3) Then I go on the first sheet, search for the prodotto (wich is my input value) and save its raw position in an Integer (i)
After that I go to the Cell E in the same line and save its value (this is the associated code I mentioned earlier) this works perfectly
i = ricerca.Row
prodottoTLD = Cells(i, 5).Value
4) And now the problem begins
After that I need to search if the input is present in the third and fourth sheets
5) If I don't find it my Input is still an anomalie and the code ends.
If I find it I have to check if the respective prodotto I find in this sheets matches the one I saved earlier (of the first sheet), the supporto and prodotto here are saved in different columns, but doesn't matter. If it matches it isn't an anomalie anymore, the String risultato is set to "OK" and the code ends.
If I find it, but the prodotto is different it is still an anomalie, the code ends.
The code seems to work, if I search in the third sheet it finds the correct index, but now prodottoMetro = Cells(i, 10).Value (and the respective prodottoBus in the fourth sheet) returns 0 instead of the value of the cell and I don't understand why.
I'll leave my code below.
Thx in advance for the help
I'm writing a function that accepts a string (right now the number is formatted as string) and returns an integer (I'm debugging in my own way to see the problem) to find some anomalies.
I'll explain briefly what am I trying to do.
In the first sheet I have some values (supporto) as String and each one has a code associated (prodotto).
I'm using those values to make a search in the other sheets.
1) I search the value in the second sheet (errore) in the column F, if I find it there the function sets a String (risultato) to "KO" (wich will be the output of the function in the end). If the value isn't there the function ends and sets the String to "OK"
2) I'm using a boolean (anomalia) to set if the value is an anomalie
3) Then I go on the first sheet, search for the prodotto (wich is my input value) and save its raw position in an Integer (i)
After that I go to the Cell E in the same line and save its value (this is the associated code I mentioned earlier) this works perfectly
i = ricerca.Row
prodottoTLD = Cells(i, 5).Value
4) And now the problem begins
After that I need to search if the input is present in the third and fourth sheets
5) If I don't find it my Input is still an anomalie and the code ends.
If I find it I have to check if the respective prodotto I find in this sheets matches the one I saved earlier (of the first sheet), the supporto and prodotto here are saved in different columns, but doesn't matter. If it matches it isn't an anomalie anymore, the String risultato is set to "OK" and the code ends.
If I find it, but the prodotto is different it is still an anomalie, the code ends.
The code seems to work, if I search in the third sheet it finds the correct index, but now prodottoMetro = Cells(i, 10).Value (and the respective prodottoBus in the fourth sheet) returns 0 instead of the value of the cell and I don't understand why.
I'll leave my code below.
Thx in advance for the help
VBA Code:
Function Validato_Con_Errore(supporto As String) As Integer
' supporto è il numero supporto da cercare
Dim wb As Workbook
Dim tld As Worksheet
Dim errore As Worksheet
Dim metro As Worksheet
Dim bus As Worksheet
Dim supptld, supportoErrore, supportoMetro, supportoBus, prodottoTLD, prodottoMetro, prodottoBus, prodottoVal As Range
Dim ricerca As Range
Dim i As Integer 'indice di riga
'Dim numProdotto As Integer ' codice prodotto teledistribuito
'Dim prodottoVal As Integer ' prodotto che è stato validato
Dim anomalia As Boolean
Dim risultato As String
Set wb = ThisWorkbook
Set tld = wb.Sheets("Titoli attivati in TLD")
Set errore = wb.Sheets("Dettaglio Convalide in Errore")
Set metro = wb.Sheets("Dettaglio Convalide Metro")
Set bus = wb.Sheets("Dettaglio Convalide Bus")
Set supportoTLD = tld.Columns("D:D") 'colonna dei supporti teledistribuiti
Set prodottoTLD = tld.Columns("E:E") 'colonna dei prodotti teledistribuiti
Set supportoErrore = errore.Columns("F:F") 'colonna dei supporti con convalida errata
Set supportoMetro = metro.Columns("H:H") 'colonna dei supporti validati metro
Set prodottoMetro = metro.Columns("J:J") 'colonna dei prodotti validati i nmetro
Set supportoBus = bus.Columns("H:H") 'colonna dei prodotti validati su bus
Set prodottoBus = bus.Columns("J:J") 'colonna die prodotti validati su bus
'controllo se il numero tesssera ha dato errori in convalida a luglio
With supportoErrore
Set ricerca = .Find(supporto, LookIn:=xlValues)
If Not ricerca Is Nothing Then
i = ricerca.Row
risultato = "KO"
anomalia = True
Do
Set ricerca = .FindNext(ricerca)
Loop While Not ricerca Is Nothing
Else
risultato = "OK"
anomalia = False
End If
End With
'se li ha dati, mi segno il codice articolo che è stato teledistribuito
While anomalia = True
With supportoTLD
Set ricerca = .Find(supporto, LookIn:=xlValues)
If Not ricerca Is Nothing Then
i = ricerca.Row
prodottoTLD = Cells(i, 5).Value 'il codice prodotto è nella colonna E, quindi prendo la cella di quella colonna sulla riga i
Do
Set ricerca = .FindNext(ricerca)
Loop While Not ricerca Is Nothing
End If
End With
With supportoMetro
Set ricerca = .Find(supporto, LookIn:=xlValues)
If Not ricerca Is Nothing Then
i = ricerca.Row
prodottoMetro = Cells(i, 10).Value 'il codice prodotto è nella colonna J, quindi prendo la celladi quella colonna sulla riga i
If prodottoTLD = prodottoMetro Then
anomalia = False
risultato = "OK"
End If
Do
Set ricerca = .FindNext(ricerca)
Loop While Not ricerca Is Nothing
End If
End With
With supportoBus
Set ricerca = .Find(supporto, LookIn:=xlValues)
If Not ricerca Is Nothing Then
i = ricerca.Row
prodottoBus = Cells(i, 10).Value 'il codice prodotto è nella colonna J, quindi prendo la celladi quella colonna sulla riga i
If prodottoTLD = prodottoBus Then
anomalia = False
risultato = "OK"
End If
Do
Set ricerca = .FindNext(ricerca)
Loop While Not ricerca Is Nothing
End If
End With
anomalia = False ' se non lo trovo nei due fogli termino il ciclo
Wend
Validato_Con_Errore = prodottoMetro
End Function
Last edited by a moderator: