There must be a smarter way of re-writing this?!?

[omnivl]

Hi im kind of limited with my vba but im keen to learn more, is there a smarter way or writing the below?

If dt_name = "M22" ThenwbDatatable.Worksheets(dt_name).Range("E2:E2").Value = "-"


For i = LBound(C, 1) To UBound(C, 1)
        If C(i, 2) = Range("switch_State") Then
        
        AGG1 = C(i, 5)
        AGG2 = C(i, 6)
        AGG3 = C(i, 7)
        AGG4 = C(i, 8)
        AGG5 = C(i, 9)
        AGG6 = C(i, 10)
        AGG7 = C(i, 11)
        AGG8 = C(i, 12)
        AGG9 = C(i, 13)
        AGG10 = C(i, 14)
        
        If AGG1 <> "|" Then
        finalFormula = "(SUMIFS(DB!W:W,DB!$B:$B," & AGG1 & ",DB!W:W,1)-SUMIFS(DB!T:T,DB!$B:$B," & AGG1 & ",DB!T:T,1))/3"
         
         If AGG2 <> "|" Then
         finalFormula = AGG1 & "+" & "(SUMIFS(DB!W:W,DB!$B:$B," & AGG2 & ",DB!W:W,1)-SUMIFS(DB!T:T,DB!$B:$B," & AGG2 & ",DB!T:T,1))/3"
         
         If AGG3 <> "|" Then
         finalFormula = AGG2 & "+" & "(SUMIFS(DB!W:W,DB!$B:$B," & AGG3 & ",DB!W:W,1)-SUMIFS(DB!T:T,DB!$B:$B," & AGG3 & ",DB!T:T,1))/3"
         
        
        End If
        End If
        End If
        
        wbDatatable.Worksheets(dt_name).Range("F2:F2").Value = "-"
        
        End If
    Next


End If

 

[omnivl]

Ive worked on it a bit more and got...but still seems messy

If dt_name = "M22" Then


For i = LBound(C, 1) To UBound(C, 1)
        If C(i, 2) = Range("switch_State") Then
        
        AGG1 = C(i, 5)
        AGG2 = C(i, 6)
        AGG3 = C(i, 7)
        AGG4 = C(i, 8)
        AGG5 = C(i, 9)
        
        If AGG1 <> "|" Then
        finalFormula = for22_1 & """" & AGG1 & """" & for22_2 & """" & AGG1 & """" & for22_3
         
         If AGG2 <> "|" Then
         finalFormula = finalFormula & "+" & for22_1 & """" & AGG2 & """" & for22_2 & """" & AGG2 & """" & for22_3


         If AGG3 <> "|" Then
         finalFormula = finalFormula & "+" & for22_1 & """" & AGG3 & """" & for22_2 & """" & AGG3 & """" & for22_3
         
         If AGG4 <> "|" Then
         finalFormula = finalFormula & "+" & for22_1 & """" & AGG4 & """" & for22_2 & """" & AGG4 & """" & for22_3
         
         If AGG5 <> "|" Then
         finalFormula = finalFormula & "+" & for22_1 & """" & AGG5 & """" & for22_2 & """" & AGG5 & """" & for22_3
         
         
        
        End If
        End If
        End If
        End If
        End If
        
        wbDatatable.Worksheets(dt_name).Cells(1 + i, 5) = "-"
        wbDatatable.Worksheets(dt_name).Cells(1 + i, 6) = "=" & finalFormula

 

[Teeroy]

You haven't really said what your desired outcome is so I have some questions.
Why are you running a loop and only assessing if the conditions = the switch_state Named Range?
Are all the switch IF conditions supposed to be embedded?

 

[omnivl]

You haven't really said what your desired outcome is so I have some questions.
Why are you running a loop and only assessing if the conditions = the switch_state Named Range?
Are all the switch IF conditions supposed to be embedded?

Thats right first it looks for a State based on a Named Range then I have the following table with different categories i need aggregated.
So it grabs Apple and then looks at the database and aggregates both Apple and Apple[D1] does that make sense? Then it constructs a formula (like sumif) in VBA and inserts it to a cell

A

B

C

D

E

F

G

H

I

J

K

L

M

N

BLANK

State

Region

Category

AGG 1

AGG 2

AGG 3

AGG 4

AGG 5

AGG 6

AGG 7

AGG 8

AGG 9

AGG 10

VIC

Local

Apple

Apple

Apple[D1]

|

|

|

|

|

|

|

|

VIC

Interstate

Apple

Apple

Apple[D2]

|

|

|

|

|

|

|

|

VIC

Local

Orange

Orange

Orange[D1]

|

|

|

|

|

|

|

|

VIC

Local

Berry & Apple

Berry

Apple

Apple[D1]

|

|

|

|

|

|

|

<tbody>
</tbody>

 

[Teeroy]

The code below won't be exactly right because I don't know what your variables are or the final formula format but it should put you on the right path.

Sub modified()
Dim AGG() As Variant
Dim RowArray() As Variant
Dim c_Range As Range

C = Range("A2:N5")
Set c_Range = Range("A2:N5")

For i = LBound(C, 1) To UBound(C, 1)
    If C(i, 2) = Range("switch_State") Then
        AGG = Application.Transpose(Application.Transpose(c_Range.Rows(i).Cells))
        EndState = Application.Match("|", AGG, 0)
        If IsError(EndState) Then
            EndState = c_Range.Columns.Count - 1
        End If
        finalFormula = ""
        For j = 4 To EndState
            finalFormula = finalFormula & "+" & for22_1 & """" & AGG(j) & """" & for22_2 & """" & AGG(j) & """" & for22_3
        Next j
    End If
    'wbDatatable.Worksheets(dt_name).Cells(1 + i, 5) = "-"
    'wbDatatable.Worksheets(dt_name).Cells(1 + i, 6) = "=" & finalFormula
    MsgBox finalFormula
Next i

End Sub

 

[omnivl]

The code below won't be exactly right because I don't know what your variables are or the final formula format but it should put you on the right path.

Sub modified()
Dim AGG() As Variant
Dim RowArray() As Variant
Dim c_Range As Range

C = Range("A2:N5")
Set c_Range = Range("A2:N5")

For i = LBound(C, 1) To UBound(C, 1)
    If C(i, 2) = Range("switch_State") Then
        AGG = Application.Transpose(Application.Transpose(c_Range.Rows(i).Cells))
        EndState = Application.Match("|", AGG, 0)
        If IsError(EndState) Then
            EndState = c_Range.Columns.Count - 1
        End If
        finalFormula = ""
        For j = 4 To EndState
            finalFormula = finalFormula & "+" & for22_1 & """" & AGG(j) & """" & for22_2 & """" & AGG(j) & """" & for22_3
        Next j
    End If
    'wbDatatable.Worksheets(dt_name).Cells(1 + i, 5) = "-"
    'wbDatatable.Worksheets(dt_name).Cells(1 + i, 6) = "=" & finalFormula
    MsgBox finalFormula
Next i

End Sub

thanks that makes sense !!!!

 

[Teeroy]

I'm glad to see that you can get some help from it.