Trouble using .Find over multiple sheets with a loop

chakalido

New Member
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
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

vencimiento = gCell.Offset(0, 11).Value
For o = 1 To 2
Next
vencimiento = gCell.Offset(0, 12).Value
For p = 1 To Len(vencimiento)
If IsNumeric(Mid(vencimiento, p, 1)) Then
End If
Next
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]``````

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

Replies
0
Views
113
Replies
0
Views
643
Replies
1
Views
78
Replies
7
Views
88
Replies
26
Views
688

1,129,791
Messages
5,638,336
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back