CODEWITHSUNDEEP
arraysvbaexcel
Khazar
Khazar

Reputation: 83

Declaring VBA Array and If Function

I am beginner with VBA and I need your help for some issues.

You will find my code below. I get a compile error with Mtable.

Thanks.

Sub GatheringofExpense()

    Dim Branches As Worksheet
    Dim Final As Worksheet
    Dim i As Integer
    Dim lrow As Range
    Dim lcol As Range

    Set Branches = Worksheets("Branches")
    Set Final = Worksheets("Final")

    'Defining last row and last column in the table for our Array
    lrow = Range("A1000000").End(xlUp).Row
    lcol = Range("XFD4").End(xlToLeft).Column

    Mtable = Range(Cells(4, 1), Cells(lrow, lcol))  'Assigning  array for table

    For i = 1 To UBound(Mtable, 1)
        If Branches.Range("A" & i)="Barda" And Range("B" & i)="Fuzuli" Then
           Range("A" & i).End(xlToRight).Copy
           Final.Range("A1000000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
        End If
    Next i
End Sub

Upvotes: 0

Views: 87

Answers (1)

paul bica
paul bica

Reputation: 10715

Use Option Explicit, change Int to Long, declare array as Variant, and qualify all ranges

Try this

Option Explicit

Public Sub GatheringOfExpense()
    Dim branches As Worksheet, final As Worksheet, lRow As Long, lCol As Long

    Set branches = Worksheets("Branches")
    Set final = Worksheets("Final")

    With branches   'Define last row and last column in "Branches" sheet, for our Array
        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        lCol = .Cells(4, .Columns.Count).End(xlToLeft).Column

        Dim tblArr As Variant, nextRow As Long, r As Long

        tblArr = .Range(.Cells(4, 1), .Cells(lRow, lCol))   'Assig array to table
        nextRow = final.Cells(final.Rows.Count, "A").End(xlUp).Row + 1

        Application.ScreenUpdating = False
        For r = 1 To UBound(tblArr)
            If tblArr(r, 1) = "Barda" And tblArr(r, 2) = "Fuzuli" Then
               .Cells(r + 4 - 1, lCol).Copy
                final.Cells(nextRow, "A").PasteSpecial xlPasteAll
                nextRow = nextRow + 1
            End If
        Next
        Application.ScreenUpdating = True
    End With
End Sub

Upvotes: 1

Related Questions