Error referring to a cell on another worksheet

Question

I have the code written below that for some reason is not working. The problem is on the definition of the variable b that uses information located on the worksheets(2). All the code before that line works great.

Note that the variable ind is global and is defined on a previous sub that runs before this one.

The code was written on the "This Workbook" tab.

Would be great if someone could share some light on what the issue might be.

Tanks a lot.

Sub verifica() ' verifica e pinta a vermelho os casos mal atribuidos
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = Worksheets(1)
    Set ws2 = Worksheets(2)

    Dim n As Integer 'contador de incerências,
    Dim n1 As Integer 'contador de casos de carga em falta
    Dim n2 As Integer 'contador de casos de carga em excesso
    ws1.Activate

    'verifica incoerências nos coeficientes e nos casos de carga
    n = 0
    For i = 0 To ind
        Range("Case1").Offset(i, 0).Select
        p = 0
        Do While ActiveCell.Value <> ""
            If Range("Case1").Offset(i, p).Value <> ws2.Range("Case2").Offset(i, p).Value Then
                Range("Case1").Offset(i, p).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                 End With
                 n = n + 1
            End If
            p = p + 1
            Range("Case1").Offset(i, p).Select
        Loop
    Next i
    'verifica para cada comb, se há casos de carga em em falta ou em excesso
    n1 = 0
    n2 = 0

    For i = 1 To ind
        a = Range(Range("Case1").Offset(i - 1, 0), Cells(Range("Case1").Offset(i - 1, 0).Row, Columns.Count).End(xlToLeft)).Count
        With ws2
            b = .Range(.Range("Case2").Offset(i - 1, 0), Cells(.Range("Case2").Offset(i - 1, 0).Row, Columns.Count).End(xlToLeft)).Count
        End With
        MsgBox b
        If a > b Then
            n1 = n1 + 1
        ElseIf a < b Then
            n2 = n2 + 1
        End If
    Next i

    If n = 0 Then
        MsgBox "Tudo OK!"
    Else
        MsgBox "Nº de incoerências:  " & n
    End If

Range("J2") = n
Range("P2") = n1
Range("V2") = n2
End Sub

score 1 · Accepted Answer · answered Feb 28 '16 at 19:15

Try this:

b = .Range(.Range("Case2").Offset(i - 1, 0), .Cells(.Range("Case2").Offset(i - 1, 0).Row, .Columns.Count).End(xlToLeft)).Count

You're using With (yay!) but didn't totally link up all cells to it (boo!). Just add the . before Cells() and Columns() and you should be good.

Without this, VBA can get confused, as Columns() refers to the active sheet, but using .Columns() in your With block will get all the Columns() for the sheet you're using With with. ...does that make sense?

Worked great thanks a lot :D. Lesson learned. – user2292821 Feb 28 '16 at 19:37 — user2292821, Feb 28 '16 at 19:37

Error referring to a cell on another worksheet

1 Answers1