Declaring VBA Array and If Function

Question

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

You never declared Mtable. Good practice is to use Option Explicit at the top of all of your modules. In any event -- you have declared lrow and lcol to be Range but are treating them as if they were Long. Also -- don't use Integer for row indices. It isn't large enough of a data type for all possible rows. Use Long instead. — John Coleman
– John Coleman, Commented May 26, 2018 at 19:54
The code that you have above doesn't throw a compile error. Are you sure that it isn't a run-time error? If so -- what does the error message actually say? — John Coleman
– John Coleman, Commented May 26, 2018 at 20:09
@JohnColeman If throws Compile Errors when I click "compile" on it (with or without Option Explicit) — ashleedawg
– ashleedawg, Commented May 26, 2018 at 20:10
@Khazar If you don't declare a variable then it implicitly is variant already, so I somewhat skeptical of your description of the problem and its solution. Perhaps you had a global declaration of Mtable that was of an inconsistent type. — John Coleman
– John Coleman, Commented May 26, 2018 at 21:24

paul bica · Accepted Answer · 2018-05-26 22:09:16Z

1

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

answered May 26, 2018 at 22:09

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Khazar Over a year ago

I changed somethings you mentioned on your code without fully copied your code. Now Everything is working properly. I used option explicit, declare my array as variant, lrow lcol as long and changed int to long. Thanks for your help.

Collectives™ on Stack Overflow

Declaring VBA Array and If Function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related