Sub not working after assigning addresses of cells where some data is found to variables

Question

I have a strange problem. That code works as long as I don't assign addresses of cells to the variables komorka_k and komorka_y. Since 2 lines of the code marked with "LINE 1" and "LINE 2" are disabled, VBA macro works properly. What is the reason for such an activity? How is it possible that assigning a value not connected with any other part of a submodule makes it acting differently?

Public stara_wartosc As Variant
Public czy_wiekszy_zakres As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x, y As Integer
Dim x_err As Integer
Const y_err = 4
Dim nowa_wartosc As Variant
Dim komorka_x As String
Dim komorka_y As String
Const kon_col = 72

komorka_x = ""
komorka_y = ""

x = Target.row
y = Target.Column

nowa_wartosc = Target.Value

If czy_wiekszy_zakres = True Then
    stara_wartosc = nowa_wartosc
End If

On Error GoTo TypeMismatch

If stara_wartosc <> nowa_wartosc And czy_wiekszy_zakres = False Then

    If Target.Worksheet.Cells(x, 2).Value = "" Or Target.Worksheet.Cells(x, 2).Value = 0 Then

        Application.EnableEvents = False

            Target.ClearContents
            MsgBox Prompt:="Zmieniłeś wartość komórki bez wpisania numeru zlecenia." & vbCrLf & "Wpisz nr zlecenia!", Title:="ZACHOWUJESZ SIĘ NIEWŁAŚCIWIE, MÓJ DROGI!"
            Target.Worksheet.Cells(x, 2).Activate

        Application.EnableEvents = True

            Exit Sub

    End If

    With ActiveWorkbook.Worksheets("Errata")

        komorka_x = .Range("A:A").Find(x, LookIn:=xlValues).Address 'LINE 1
        komorka_y = .Range("B:B").Find(y, LookIn:=xlValues).Address 'LINE 2

        x_err = .Cells(Rows.Count, 1).End(xlUp).row + 1

        If .Cells(x_err, 1).Value = 0 Or .Cells(x_err, 1).Value = "" Then
        .Cells(x_err, 1).Value = x
        End If
        If .Cells(x_err, 2).Value = 0 Or .Cells(x_err, 2).Value = "" Then
        .Cells(x_err, 2).Value = y
        End If

Set_values:
        .Cells(x_err, y_err - 1).Value = stara_wartosc
        .Cells(x_err, y_err).Value = Target.Value
        .Cells(x_err, y_err + 1).Value = Target.Worksheet.Cells(x, 2).Value

    End With

End If

TypeMismatch:
If Err = 13 Then
    Exit Sub
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        stara_wartosc = Target.Value
        czy_wiekszy_zakres = False
    Else
        czy_wiekszy_zakres = True
    End If
End Sub

Note: ① `Dim x, y As Integer` defines `y` as `Integer` but `x` as `Variant` you need to specify a type for every variable. ② Excel has more rows than `Integer` can handle, therefore always use `Long` instead of `Integer` (There is no benefit in using `Integer` at all): `Dim x As Long, y As Long` — Pᴇʜ, Mar 19 '18 at 10:57
Chances are at least one of the values is not being found, hence error. — SJR, Mar 19 '18 at 11:01
Thank you for your advice but it didn't help. 1. I didn't know that Dim var1,var2 As SomeType always defines var1 As Variant. 2. I don't need to use Long variables because of a relatively small maximum amount of rows that I assume my worksheet to have. — Paweł, Mar 19 '18 at 11:04
@Paweł you should yous `Long` anyway. There is no benefit in using `Integer` at all in VBA ([Here is why](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long)) you can just always use `Long` instead of `Integer`. It prevents you from running into any issues and doesn't cost anything! — Pᴇʜ, Mar 19 '18 at 11:06

Pᴇʜ · Answer 1 · 2018-03-19T11:03:21.013

Probably

komorka_x = .Range("A:A").Find(x, LookIn:=xlValues).Address
komorka_y = .Range("B:B").Find(y, LookIn:=xlValues).Address

doesn't find anything. Therefore .Address fails because no find result no address.

Then because of On Error GoTo TypeMismatch it jumps to the error handling here.

So make sure .Find is not nothing:

Dim FoundX As Range
Set FoundX = .Range("A:A").Find(x, LookIn:=xlValues)
If Not FoundX Is Nothing Then
    komorka_x = FoundX.Address
Else
    MsgBox "Nothing found for x=" & x
End If

Sub not working after assigning addresses of cells where some data is found to variables

1 Answers1