user2572003
user2572003

Reputation: 741

Macro says ' End if with out block if ' but all the if blocks are there

i have writen a macro to get some excel data from one sheet to another so that i can use the data easily according to conditions

Sub page(rowNumber As Integer)

    Dim row_number As Integer
    Dim iterator As Integer
    Dim srch_string As String ' Searching for the string view
    Dim IdMethod As String
    Dim UIValues As String
    Dim Name As String
    Dim SheetName As String
    Dim number As Integer
    Dim result As String

    result = " "
    IdMethod = " "
    UIValues = " "
    row_number = rowNumber
    iterator = row_number
    Name = ActiveWorkbook.FullName
    srch_string = "onclick"
    number = 3
    SheetName = "page"
    Application.Worksheets(SheetName).Activate
    'while   i= rowNumber
    Do While iterator > 0
        If srch_string = Worksheets(SheetName).Cells(iterator, 5) Then
          Exit Do
        Else
          IdMethod = IdMethod & ActiveWorkbook.Worksheets(SheetName).Cells(iterator, 4)
          UIValues = UIValues & ActiveWorkbook.Worksheets(SheetName).Cells(iterator, 5)
          For Index = 2 To Worksheets("param").UsedRange.Rows.Count
             If ActiveWorkbook.Worksheets(SheetName).Cells(iterator, 5) = ActiveWorkbook.Worksheets("param").Cells(Index, 1) Then
                    Do While munber > 0
                        If result <> srch_string Then
                            Exit Do
                        Else
                            result = ActiveWorkbook.Worksheets(SheetName).Cells(iterator, 5)
                            ActiveWorkbook.Worksheets("param").Cells(number, 1) = ActiveWorkbook.Worksheets(SheetName).Cells(iterator, 5)
                            number = number + 1
                            iterator = iterator - 1
                        End If
             End If
             iterator = iterator - 1
    End If
    Loop
    End Sub

but the macro says End if with out block if ... am not sure about all the syntaxes but all the ifs are placed correctly as far as i know not able to figure out what went wrong

Upvotes: 0

Views: 78

Answers (2)

hstay
hstay

Reputation: 1439

You are missing Next Index that closes

    For Index = 2 To Worksheets("param").UsedRange.Rows.Count

EDIT

And as @simoco and @brain pointed out, you also need also to close Do While nunber > 0 with Loop.

Upvotes: 1

Pallav Raj
Pallav Raj

Reputation: 1744

Sub page(rowNumber As Integer)

    Dim row_number As Integer
    Dim iterator As Integer
    Dim srch_string As String ' Searching for the string view
    Dim IdMethod As String
    Dim UIValues As String
    Dim Name As String
    Dim SheetName As String
    Dim number As Integer
    Dim result As String

    result = " "
    IdMethod = " "
    UIValues = " "
    row_number = rowNumber
    iterator = row_number
    Name = ActiveWorkbook.FullName
    srch_string = "onclick"
    number = 3
    SheetName = "page"
    Application.Worksheets(SheetName).Activate
    'while   i= rowNumber
    Do While iterator > 0
        If srch_string = Worksheets(SheetName).Cells(iterator, 5) Then
          Exit Do
        Else
          IdMethod = IdMethod & ActiveWorkbook.Worksheets(SheetName).Cells(iterator, 4)
          UIValues = UIValues & ActiveWorkbook.Worksheets(SheetName).Cells(iterator, 5)
          For Index = 2 To Worksheets("param").UsedRange.Rows.Count
             If ActiveWorkbook.Worksheets(SheetName).Cells(iterator, 5) = ActiveWorkbook.Worksheets("param").Cells(Index, 1) Then
                    Do While munber > 0
                        If result <> srch_string Then
                            Exit Do
                        Else
                            result = ActiveWorkbook.Worksheets(SheetName).Cells(iterator, 5)
                            ActiveWorkbook.Worksheets("param").Cells(number, 1) = ActiveWorkbook.Worksheets(SheetName).Cells(iterator, 5)
                            number = number + 1
                            iterator = iterator - 1
                        End If
                   Next '<- Add Next Here (Missing in your code)
             End If
         Next '<- Add Next Here (Missing in your code)
             iterator = iterator - 1
    End If
    Loop
    End Sub

Upvotes: 3

Related Questions