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]
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Watch MrExcel Video

Forum statistics

Threads
1,099,045
Messages
5,466,219
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top