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

omnivl

Board Regular
Joined
Aug 25, 2014
Messages
53
Hi im kind of limited with my vba but im keen to learn more, is there a smarter way or writing the below?

Code:
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Ive worked on it a bit more and got...but still seems messy

Code:
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
 
Upvote 0
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?
 
Upvote 0
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

ABCDEFGHIJKLMN
BLANKStateRegionCategoryAGG 1AGG 2AGG 3AGG 4AGG 5AGG 6AGG 7AGG 8AGG 9AGG 10
VICLocalAppleAppleApple[D1]||||||||
VICInterstateAppleAppleApple[D2]||||||||
VICLocalOrangeOrangeOrange[D1]||||||||
VICLocalBerry & AppleBerryAppleApple[D1]|||||||

<tbody>
</tbody>
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.

Code:
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 :)!!!!
 
Upvote 0
I'm glad to see that you can get some help from it.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top