# 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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,527
Messages
5,837,875
Members
430,520
Latest member
VenkateshRajaganesan

### 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.

### Which adblocker are you using?

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