CODEWITHSUNDEEP
excelvba
soup12
soup12

Reputation: 15

VBA Excel Code Errors IF Else Between a Range

I am trying to write a simple VBA code that requires a user input of a number between -9 and +9. Once the user inputs a number, the code will pull data from specific columns, related to the input number, from one sheet into a new sheet. I have the code written out but I am having a problem with the If Else function. I get an error that reads "Compile error: Syntax error". If I get rid of the Else function the code works great for anything greater than or equal to 0, but I need to have the ability to pull data related to the negative inputs. Should I not use If Else for this code?

Sub AA()
On Error Resume Next
er = Sheets("D2").Range("AA65536").End(xlUp).Row
ec = Range("IV1").End(xlToLeft).Column
r = 3
Range("A3:IV65536").ClearContents
For i = 504 To er
 a = Sheets("D2").Cells(i, "AA")
If WorksheetFunction.IsErr(a) Then a = 0
If a >= Cells(2, 1).Value Then
    For j = 2 To ec
        f = Cells(1, j)
        Cells(r, j) = Sheets("D2").Cells(i, f)
    Next j
    r = r + 1

Else a < Cells(2, 1).Value Then
    For j = 2 To ec
        f = Cells(1, j)
        Cells(r, j) = Sheets("D2").Cells(i, f)
    Next j
    r = r + 1
End If
Next i
End Sub

Upvotes: 0

Views: 256

Answers (3)

Error 1004
Error 1004

Reputation: 8220

Use:

  1. Option Explicit - Helps you to declare all variables

  2. Use Elseif a < Cells(2, 1).Value instead of Else a < Cells(2, 1).Value OR just replace Else a < Cells(2, 1).Value with Else

  3. Variables referring to Last row or last column declare as long

  4. No need to use On Error Resume Next

  5. If you want to loop range use * for each* or *For i=1 to *

Code guidelines:

Option Explicit

Sub Test()

    Dim LastRow As Long, LastColumn As Long, Row As Long, Column As Long
    Dim cell As Range, rng As Range

    With ThisWorkbook.Worksheets("Sheet1") '<- Set the worksheet you want to use

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row '<- Find last row of column A sheet1
        LastColumn = .Cells(7, .Columns.Count).End(xlToLeft).Column '<- Find last column of row 7 sheet1

        Set rng = .Range("A1:A" & LastRow) '<- Set range

        'Method 1
        For Each cell In rng
            If cell.Value = 1 Then
            ElseIf cell.Value = 2 Then
            End If
        Next cell

        'Method 2
        For Row = 2 To LastRow '<- Loop from row 2 to last row
            For Column = 1 To LastColumn '<- Loop from column 1 to last column
            Next Column
        Next Row

    End With

End Sub

Upvotes: 1

Zack E
Zack E

Reputation: 706

Have you tried getting rid of the Else and just using another If Statement? Also I would get rid of the On Error Resume Next especially if you are still testing your code out.

Sub AA()
On Error Resume Next
er = Sheets("D2").Range("AA65536").End(xlUp).Row
ec = Range("IV1").End(xlToLeft).Column
r = 3
Range("A3:IV65536").ClearContents
For i = 504 To er
 a = Sheets("D2").Cells(i, "AA")
If WorksheetFunction.IsErr(a) Then a = 0
If a >= Cells(2, 1).Value Then
    For j = 2 To ec
        f = Cells(1, j)
        Cells(r, j) = Sheets("D2").Cells(i, f)
    Next j
    r = r + 1
End if

If a < Cells(2, 1).Value Then
    For j = 2 To ec
        f = Cells(1, j)
        Cells(r, j) = Sheets("D2").Cells(i, f)
    Next j
    r = r + 1
End If
Next i
End Sub

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Replace 

Else a < Cells(2, 1).Value Then

with:

Else

Upvotes: 3

Related Questions