vba is there a way to compare values from two different sheets or files?

Question

I find some difficulties when I try to compare values of two different Workbooks. The code has to delete the rows that do not follow the if condition, that is, if the value from the workbook "Nuevo hoja de cálculo" is between an interval of values from another workbook called "Formato CO-08a", it continues calculating a few variables (m,y_n, y) and if the value does accomplish the second if condition, then the row is deleted.

I have also tried to do it working with sheets from the same workbook, copying in the Workbook "Nuevo hoja de calculo" the sheet from the workbook "Formato CO-08a". But it still doesn't work, as the program doesn't achieve to read the values from this copied sheet. The example I wrote below works with sheets from the same workbook, but my goal is to work with 2 different workbooks.

For i = 1 To Sheets("Seleccion_ECs").listaopciones.ListCount
Line1:
        For Z = 0 To 7
            Debug.Print Z
            If (Sheets("Seleccion_ECs").Cells(i + 1, E).Value >= ThisWorkbook.Sheets("Requisitos Contenedor + ATI").Cells(40 + Z, 4).Value) And (Sheets("Seleccion_ECs").Cells(i + 1, E).Value <= ThisWorkbook.Sheets("Requisitos Contenedor + ATI").Cells(41 + Z, 4).Value) Then

                m = (ThisWorkbook.Sheets("Requisitos Contenedor + ATI").Cells(41 + Z, 2).Value - ThisWorkbook.Sheets("Requisitos Contenedor + ATI").Cells(40 + Z, 2).Value) / (ThisWorkbook.Sheets("Requisitos Contenedor + ATI").Cells(41 + Z, 4).Value - ThisWorkbook.Sheets("Requisitos Contenedor + ATI").Cells(40 + Z, 4).Value)

                y = m * (Sheets("Seleccion_ECs").Cells(i + 1, E).Value - Sheets("Requisitos Contenedor").Cells(40 + Z, 2).Value) + Sheets("Requisitos Contenedor").Cells(40 + Z, 4).Value

                y_n = Sheets("Seleccion_ECs").Cells(i + 1, Q).Value

                Debug.Print ("Se ha encontrado un intervalo")

                If y_n > 1.02 * y Then 'Si se sale en un 2 por ciento de la zona aceptada, se elimina la fila.

                    Worksheets("Seleccion_ECs").Rows(i + 1).Delete

                    GoTo Line1

                Else:

                    i = i + 1 'Preguntar si vale esto teniendo el for i=0

                    GoTo Line1

                End If

            End If

        Next Z

Next i

Thank you in advance

You need to reference workbooks consistently in your code, and using Goto is not generally recommended. — SJR

AcsErno AcsErno · Accepted Answer · 2019-05-08T13:19:35

I recommend using the following technique for referencing cells in multiple workbooks/worksheets. This is especially safe at longer processing sessions when the user may change the active workbook/worksheet, and it also save you time by less typing.

Dim wbSource as Workbook, wbDest as Workbook
Dim shSEC as Worksheet, shREQ as Worksheet

Set wbSource = Thisworkbook
Set wbDest = <.... this is the reference of the other file you're gonna use>
Set shSEC = wbSource.Worksheets("Seleccion_ECs")
...
If shSEC.Cells(i + 1, E).Value >= shREQ.Cells(40 + Z, 4).Value) ...

You should check success after each Set by inserting If Err.Number <> 0 Then <error hadler>so you make sure everything is fine before the loop.

You can replace GoTo ... with Exit For. The prog will work the same way but it is a bit more elegant :)

vba is there a way to compare values from two different sheets or files?

1 Answers