How to implement an array with VBA Excel?

Question

I have this code here, and I want to add an array with [12] (the 12 months).

The lines with // are the ones that I need to implement but I don't understand well how to do it!

The rest works fine :)

Sub titleHere()
Dim i As Long, var As Long
var = 0
//ARRAY[12] arr = {"Jan", "Fev", "Mar", etc...}

//Sheets("arr[0]").Cells(4, 3).Value = Sheets("INTRO").Cells(5, 2).Value
//For m = 1 To 11
    For i = 3 To 32
        If Cells(i, 19).Value = "C" Or Cells(i, 19).Value = "c" Then
            If Cells(i, 20) = 0 Then
                var = Sheets("INTRO").Cells(2, 2).Value
            Else
                var = Sheets("INTRO").Cells(2, 2).Value - Cells(i, 20).Value
          End If
        Else
            var = 0
        End If
//        Sheets("arr[m]").Cells(4, 3) = Sheets("arr[m]").Cells(4, 3) - var
        Next
//    Next
End Sub

arr = Array("Jan", "Fev", "Mar") then use Sheets(arr(0)) and so on. — Rory
– Rory, Commented Oct 29, 2020 at 15:42
Once you've done what @Rory suggested, change the sheet statement outside the FOR loop to: Sheets(arr(0)).Cells(4, 3).Value = Sheets("INTRO").Cells(5, 2).Value. Change the FOR statement to: For m = LBound(arr) To UBound(arr). Then change sheet statement inside the FOR loop to: Sheets(arr(m)).Cells(4, 3) = Sheets(arr(m)).Cells(4, 3) - var — Zac
– Zac, Commented Oct 29, 2020 at 15:49
If INTRO is one worksheet, Worksheets(arr(m)) is another (Jan to Dec), where is Cells(i, 19) located, in a third worksheet? In which worksheet is the code located? — VBasic2008
– VBasic2008, Commented Oct 29, 2020 at 15:50
Yes INTRO and Jan, Fev, Mars... are all worksheets! The objective is to take a value in INTRO and change it in every worksheet :) — Pedro Bernardos
– Pedro Bernardos, Commented Oct 29, 2020 at 16:00

VBasic2008 · Accepted Answer · 2020-10-29 16:05:59Z

1

Loop Worksheets by Month

Not sure if I understood everything correctly, but with a few tweaks it should help you to get the job done.
Off course, change the months to the months in your language.

A Quick Fix

Option Explicit

Sub titleHere()
    
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The workbook containing this code.
    
    Dim ws1 As Worksheet
    Set ws1 = wb.Worksheets("INTRO")
    Dim ws2 As Worksheet
    Set ws2 = wb.ActiveSheet ' ?
    
    Dim arr As Variant
    arr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
                "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    
    Dim m As Long
    Dim i As Long
    Dim var As Long
    
    Dim ws As Worksheet
    Set ws = Worksheets(arr(LBound(arr)))
    ws.Cells(4, 3).Value = ws1.Cells(5, 2).Value
    
    For m = LBound(arr) + 1 To UBound(arr)
        Set ws = wb.Worksheets(arr(m))
        For i = 3 To 32
            If StrComp(ws2.Cells(i, 19).Value, "c", vbTextCompare) = 0 Then
                If ws2.Cells(i, 20) = 0 Then
                    var = ws1.Cells(2, 2).Value
                Else
                    var = ws1.Cells(2, 2).Value - ws2.Cells(i, 20).Value
                End If
            Else
                var = 0
            End If
                ws.Cells(4, 3) = ws.Cells(4, 3) - var
        Next i
    Next m

End Sub

answered Oct 29, 2020 at 16:05

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

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

11 Comments

Pedro Bernardos Over a year ago

Why do you use Worksheet and Workbook? I dont understand the utility if we use Sheets("") ?

VBasic2008 Over a year ago

Sheets is a collection of worksheets, charts ... If the code is about worksheets, then I like to use Worksheets. You cannot have cells and ranges in a chart. The workbook is an object that contains worksheets (sheets), so if you use the exact workbook and exact worksheet (aka if you qualify it or create a reference to it), then there is a slim chance of error. You will encounter those errors soon enough.

Pedro Bernardos Over a year ago

Ok I understand!! I'm gonna try it :D By the way, I need to do one thing only during the first [12] for loop. I tried this but I says error: If m = arr(0) Then .... Else .... Enf If I tried with m = 0 too and it doesn't work too!

Dan Donoghue Over a year ago

Hi Pe Dro, I assume this is the follow on from Yesterdays code? Small issue in what you shared: Sheets("arr(1)") You don't need double quotes as it's a variable resulting in a string so make it Sheets(arr(1)). There is another spot in the same line where you need to remove the double quotes also. Also when commenting in VBA use a single quote ' instead of //// This way if anyone copies and pastes your code into the editor it will compile with the comments rather than erroring.

Dan Donoghue Over a year ago

also put , arr as variant on the end of your DIM line, best to dimension everything. Also add m as long

|

Collectives™ on Stack Overflow

How to implement an array with VBA Excel?

1 Answer 1

Loop Worksheets by Month

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Loop Worksheets by Month

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related