Use array values in a formula - vba

Question

Would really appreaciate a friendly nudge to the right direction.

I am trying to use an array as a variable to populate a column with values based on COUNTIFS formula, like "a cell - a formula with new element of the array as a variable". So AF2 - Countifs for Brand2 AF3 - Countifs for Brand3 .. Now I get the value ONLY for the final element of the array (and I can see how excel loops through the rest) for the whole range. Tried more or less everything known to me and looked through earlier questions - to no avail. Would appreciate a little help.

How it would look graphically

Option Explicit
Sub Summa_po()

Dim Mesyaz1 As Date
Dim Mesyaz2 As Date
Dim Mesyaz3 As Date
Dim Brand()
Dim i As Long

Worksheets(1).Activate

Mesyaz1 = DateAdd("m", -1, Now)
Mesyaz2 = DateAdd("m", -2, Now)
Mesyaz3 = DateAdd("m", -3, Now)
Brand = Array("Brand1", "Brand2", "Brand3", "Brand4")
For i = LBound(Brand) To UBound(Brand) 'Here I tried also For i=0 to UBound(Brand)
With ActiveSheet.Range("AF:AF")
.Formula = "=COUNTIFS(C:C," & RTrim(Month(Mesyaz3)) & ",H:H,""Head"",F:F," & Chr(34) & Brand(i) & Chr(34) & ")"
.AutoFill Destination:=ActiveSheet.Range("AF2:AF55") 'I used to draw it to the last cell but it is not a problem appararently
  End With
  Next i

[AF1] = "Head"
End Sub

But, what your problem is? Do you receive an error? If yes, what error and on which code line. If not, what does your code produces against what you need? — FaneDuru
– FaneDuru, Commented Mar 1, 2020 at 14:20
As I written, the column is getting populated by the formula with the last element of the array. So e.g. (C:C;12;H:H;"Head";F:F;"Brand 4"). Brand 1, Brand2, Brand3 are not saved — D_ob_d
– D_ob_d, Commented Mar 1, 2020 at 14:25
Some sample data would help. You are applying the formula to the whole column (which is going to take a while - pretty sure is not what you want) and you're repating the process for the 4 brands. Question: How do you decide which brand should be included in the formula? — Ricardo Diaz
– Ricardo Diaz, Commented Mar 1, 2020 at 14:31
But, this is what your code does. Please put a Stop before End With and check the formulas when code stops. You will see that it uses the second array element (for the first you have to use 0) and then the third one. The previous formulas will be replaced by that last iteration... How do you like tho formulas to really look? — FaneDuru
– FaneDuru, Commented Mar 1, 2020 at 14:33
What would be the formula if you do it manually directly in Excel (not vba)? Something like this...=COUNTIFS(C:C,12;H:H,"Head",F:F,"Brand1") + COUNTIFS(C:C,12,H:H,"Head",F:F,"Brand2") + COUNTIFS(C:C,12,H:H,"Head",F:F,"Brand3") + COUNTIFS(C:C,12,H:H,"Head",F:F,"Brand4") — Ricardo Diaz
– Ricardo Diaz, Commented Mar 1, 2020 at 14:39

Ricardo Diaz · Accepted Answer · 2020-03-01 15:46:42Z

1

Your explanation doesn't match your code, but re-reading them and your last image confirmed what I suspected.

Read the comments and adjust it to fit your needs

EDIT: Adjusted the counter for the brand array

Code:

Sub Summa_po()

    Dim targetSheet As Worksheet
    Dim targetRange As Range
    Dim cell As Range

    Dim Mesyaz1 As Date
    Dim Mesyaz2 As Date
    Dim Mesyaz3 As Date
    Dim Brand() As Variant

    Dim firstRow As Long
    Dim lastRow As Long

    Dim counter As Long

    ' If you need to work with a specific sheet change the next line to ThisWorkbook.Worksheets("NAME OF THE SHEET")
    Set targetSheet = ThisWorkbook.ActiveSheet

    ' Define brands array
    Brand = Array("Brand1", "Brand2", "Brand3", "Brand4")

    ' Define start row
    firstRow = 2

    ' Find last row in column AF uncomment next line
    ' lastRow = targetSheet.Cells(targetSheet.Rows.Count, "AF").End(xlUp).Row
    ' Or set directly the last row
    lastRow = 55

    ' Calc previous months
    Mesyaz1 = DateAdd("m", -1, Date)
    Mesyaz2 = DateAdd("m", -2, Date)
    Mesyaz3 = DateAdd("m", -3, Date)

    ' Define the target range in column AF
    Set targetRange = targetSheet.Range("AF" & firstRow & ":AF" & lastRow)

    ' Loop through each cell in range
    For Each cell In targetRange.Cells

        If counter = UBound(Brand) + 1 Then
            counter = 1
        Else
            counter = counter + 1
        End If

        cell.Formula = "=COUNTIFS(C:C," & Month(Mesyaz3) & ",H:H,""Head"",F:F," & Chr(34) & Brand(counter - 1) & Chr(34) & ")"

    Next cell

    ' Are you assigning this to a name? better use Thisworkbook.Names("")
    [AF1] = "Head"

End Sub

Let me know if it works

edited Mar 1, 2020 at 15:46

answered Mar 1, 2020 at 14:48

Ricardo Diaz

5,7262 gold badges24 silver badges34 bronze badges

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

3 Comments

D_ob_d Over a year ago

Thanks so much for your help! There is a Run-time error 9, subsript out of range on the cell.Formula = "=COUNTIFS(C:C," & RTrim(Month(Mesyaz3)) & ",H:H,""Head"",F:F," & Chr(34) & Brand(counter - 1) & Chr(34) & ")" Will delve into it, may be will manage to fix it

Ricardo Diaz Over a year ago

I have adjusted the way to manage the array counter. Please replace the code with the new one and adjust it accordingly. Let me know how it goes

D_ob_d Over a year ago

Worked Perfectly! Thank you so much for teaching me the method! It's fantactic!

FaneDuru · Accepted Answer · 2020-03-01 15:11:55Z

0

Please replace your next code:

    For i = LBound(Brand) To UBound(Brand) 'Here I tried also For i=0 to UBound(Brand)
        With ActiveSheet.Range("AF:AF")
            .Formula = "=COUNTIFS(C:C," & RTrim(Month(Mesyaz3)) & ",H:H,""Head"",F:F," & Chr(34) & Brand(i) & Chr(34) & ")"
            .AutoFill Destination:=ActiveSheet.Range("AF2:AF55") 'I used to draw it to the last cell but it is not a problem appararently
       End With
   Next i

With the next one:

  For i = LBound(Brand) To UBound(Brand) 
        Range("AF" & i + 2).Formula = "=COUNTIFS(C:C," & RTrim(Month(Mesyaz3)) & _
                   ",H:H,""Head"",F:F," & Chr(34) & Brand(i) & Chr(34) & ")"
  Next i

AutoFill does not make any sense here. There is not any reference to be iterated for your Brands. They must be added only by the code...

edited Mar 1, 2020 at 15:11

answered Mar 1, 2020 at 14:58

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

3 Comments

D_ob_d Over a year ago

You are my personal Jesus - saved me from a two day agony! You are absolutely right - Autofill was the bug!!!

FaneDuru Over a year ago

@Anna: I am afraid, not exactly... AutoFill was only the bad circumstance. The trick has been done especially by using Range.("AF" & i). Otherwise, your iteration created a formula on all column, but referring to the last array element...

D_ob_d Over a year ago

Guru, yeah, I realize it. Just never came across this method of populating a range. It is so elegant. Have been around VBA not too long.

Collectives™ on Stack Overflow

Use array values in a formula - vba

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related