Trouble using .Find over multiple sheets with a loop

chakalido

New Member
Joined
Jul 18, 2014
Messages
16
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]
 

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.

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top