Hello, I am developing a code to search in an external workbook a determinated value, the fact is that external workbook has many sheets and we are having trouble making the code to search in each sheet. Here I leave you the part of the code that, without the red part, works when we look in a single spreadsheet. Thank you very much for any help.
Code:
CUENTAERROR = 0
'activar el contador para verificar si son nuevas y buscar en el otro libro el numero de documento
CUENTACORRECTA = 0
[COLOR=#ff0000] Do[/COLOR]
For Each ws In SourceWb.Worksheets
If Application.WorksheetFunction.IsText(ws.Name) = True Then Set gCell = ws.Columns("A").Find(what:=IDPEDIDO, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, searchformat:=False)
If gCell Is Nothing And CUENTACORRECTA = 0 Then
cadenaId1 = cadenaId1 & ", " & IDPEDIDO
HojaNueva.Cells(F2, 29) = "NO EXISTE"
CUENTAERROR = CUENTAERROR + 1
End If
If Not gCell Is Nothing Then
HojaNueva.Cells(F2, 29) = "SI EXISTE"
CUENTACORRECTA = CUENTACORRECTA + 1
n = 1
If gCell.Offset(, 1).Value = "Telefónica de España, SAU" Or gCell.Offset(, 1).Value = "TME" Then
iva = gCell.Offset(1, 10).Value
firstAddress = gCell.Address
vencimiento = gCell.Offset(0, 11).Value
cadenaV = ""
cadena6 = ""
For o = 1 To 2
cadenaV = cadenaV & Mid(vencimiento, o, 1)
Next
If cadenaV = "Se" Then
vencimiento = gCell.Offset(0, 12).Value
For p = 1 To Len(vencimiento)
If IsNumeric(Mid(vencimiento, p, 1)) Then
cadena6 = cadena6 & Mid(vencimiento, p, 1)
End If
Next
vencimiento = cadena6 & "D"
End If
impedido = HojaActiva.Cells(F1, 7).Value
imiva = HojaActiva.Cells(F1, 8).Value
imtotal = HojaActiva.Cells(F1, 9).Value
niftelefonica = gCell.Offset(, 2).Value
Do
descripcion = gCell.Offset(n, 1).Value
If IsNumeric(Mid(descripcion, 1, 1)) = True Then
nuevototal = nuevototal + gCell.Offset(n, 11).Value
nuevoimporte = nuevoimporte + gCell.Offset(n, 8).Value
End If
If Not gCell.Offset(n, 0).Value = "" Then
VALIDAR = gCell.Offset(n, 0).Value
End If
If gCell.Offset(n, 1).Value = "Código Responsable" Then
cdgr = gCell.Offset(n, 2).Value
End If
If gCell.Offset(n, 1).Value = "N_EJECUCION" Then
nexec = gCell.Offset(n, 2).Value
End If
If gCell.Offset(n, 1).Value = "CENTRO_COSTE" Then
ccos = gCell.Offset(n, 2).Value
End If
If gCell.Offset(n, 1).Value = "Service Order" Then
sor = gCell.Offset(n, 2).Value
End If
If gCell.Offset(n, 1).Value = "Quote" Then
quote = gCell.Offset(n, 2).Value
End If
n = n + 1
Loop While IDPEDIDO = VALIDAR And n <> 56519
nuevoiva = nuevoimporte * (iva / 100)
If Round(nuevoimporte, 2) <> Round(impedido, 2) Then
HojaNueva.Cells(F2, 26) = "ERROR"
CUENTAERROR = CUENTAERROR + 1
Else
HojaNueva.Cells(F2, 26) = "OK"
End If
If Round(nuevototal, 2) = Round(imtotal, 2) Then
HojaNueva.Cells(F2, 27) = "OK"
Else
HojaNueva.Cells(F2, 27) = "ERROR"
CUENTAERROR = CUENTAERROR + 1
End If
If numeros <> niftelefonica Then
HojaNueva.Cells(F2, 28) = "ERROR"
CUENTAERROR = CUENTAERROR + 1
Else
HojaNueva.Cells(F2, 28) = "OK"
End If
End If
End If
Next ws
[COLOR=#ff0000] Set gCell = ws.Columns("A").FindNext(gCell)
Loop While gCell Is Nothing And CUENTACORRECTA <> 1
Set gCell = Nothing[/COLOR]
