VBA: Problems with For Loop (Amateur Coder)

[vishal_c99]

Hi all,

I am struggling with a simple piece of code, which is as follows:

Sub Distance()
Dim i, j, lastrow As Integer
Dim a1, a2, b1, b2, c1, c2 As Long

Set j.Value = 2

lastrow = ActiveSheet.Cells(65536, 1).End(xlUp).Row

For i = 2 To lastrow
a1 = Cells(i, 1).Value
a2 = Cells(i, 2).Value
b1 = Cells(i + 1, 1).Value
b2 = Cells(i + 1, 2).Value
c1 = Abs(a1 - b1)
c2 = Abs(a2 - b2)

If c1 < 0.01 And c2 < 0.01 Then
Cells(j, 4) = a1
Cells(j, 5) = a2
Cells(j + 1, 4) = b1
Cells(j + 1, 5) = b2
j = j + 2
Next i

End If

End Sub

The runtime keeps giving me a compile error "Next without For". This is frustrating because I seem to have a Next after a For.

Please advise as I am new to VBA coding.

Thanks in advance,

Vishal

 

[Sektor]

Sub Distance()

    Dim i, j, lastrow As Integer
    Dim a1, a2, b1, b2, c1, c2 As Long
    
    Set j.Value = 2
    
    lastrow = ActiveSheet.Cells(65536, 1).End(xlUp).Row
    
    For i = 2 To lastrow
        
        a1 = Cells(i, 1).Value
        a2 = Cells(i, 2).Value
        b1 = Cells(i + 1, 1).Value
        b2 = Cells(i + 1, 2).Value
        c1 = Abs(a1 - b1)
        c2 = Abs(a2 - b2)
        
        If c1 < 0.01 And c2 < 0.01 Then

            Cells(j, 4) = a1
            Cells(j, 5) = a2
            Cells(j + 1, 4) = b1
            Cells(j + 1, 5) = b2
            j = j + 2
        
[B][COLOR="Red"]        End If
    
    Next i[/COLOR][/B]
    
End Sub

 

[Sektor]

By the way, variables i, j, a1, a2, b1, b2, c1 are Variant type.
Declaration should look like this:

    Dim i As Integer, j As Integer, lastrow As Integer
    Dim a1 As Long, a2 As Long, b1 As Long, b2 As Long, c1 As Long, c2 As Long

 

[vishal_c99]

Thanks a ton, Sektor. This seems to have resolved the issue.

A very very basic question - I want to include long decimals in my analysis, and I figured "Long" is just for integers.

What should I use for Decimals?

Thanks a lot!

Vishal

 

[Sektor]

Actually, you can't directly create variable of Decimal type. Here's workaround:

Sub TryDec()

    Dim dec As Variant
    Dim someVar As Double
    
    someVar = 10.02
    dec = CDec(someVar)
    
    ' Make sure dec is Decimal
    MsgBox "dec is of type: " & TypeName(dec)
    
End Sub

 

[hiker95]

vishal_c99,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Distance()
Dim i As Long, j As Long, lastrow As Long
Dim a1 As Double, a2 As Double, b1 As Double, b2 As Double, c1 As Double, c2 As Double
Set j.Value = 2
lastrow = ActiveSheet.Cells(65536, 1).End(xlUp).Row
For i = 2 To lastrow
  a1 = Cells(i, 1).Value
  a2 = Cells(i, 2).Value
  b1 = Cells(i + 1, 1).Value
  b2 = Cells(i + 1, 2).Value
  c1 = Abs(a1 - b1)
  c2 = Abs(a2 - b2)
  If c1 < 0.01 And c2 < 0.01 Then
    Cells(j, 4) = a1
    Cells(j, 5) = a2
    Cells(j + 1, 4) = b1
    Cells(j + 1, 5) = b2
    j = j + 2
  End If
Next i
End Sub