Looping Through Calculations

[LaBamba]

I know there are several ways to loop, but i have never combined them with calculations.

Let me explain my end result:

I have a column that shows me value...even though its formatted as a txt field...this column will dictate what calculation to perform.
Lets assume this column is column X.

If column x is 1...it is to perform a multiplication of column AA and BB
If its 2, it is to perform a sum of two, AA and AB, and then multiply by BB.

I will need it to loop all the way down the column and also give me the results in my masterworkbook.

Ideas gang?

Much appreciated.

[snb]

My idea: post a sample workbook.

[LaBamba]

1202112022
Ok attached is a sample...so I would need it to recognize column headers instead of column #'s...because that always varies.
Sample2 is the data
Sample1 is my data/macrosheet...i currently only has a general module for column header functions and such.

I need it calculate based on the Rank column in sample 2.
For ranks 1...multiply Qty * Price and report the calculation in sample1 under rank 1
Rank 2R i need to sum qty and T1 and multiple by price and report in sample1 2R
3 and 4+ are the same as 1.

Let me know your thoughts.

appreciate it.

[LaBamba]

Im getting errors with the code...and I'm just trying to name my fields at this point..take a look at the code below.


Sub Calculations() On Error GoTo errorHandler
Dim wbCurrent As Workbook
Dim wbMaster As Workbook
Dim wsPS As Worksheet
Dim currentrank As String
Dim strSearch As String

Dim dlr As Range
Dim qt1 As Range
Dim qt2 As Range
Dim y As Long
Dim mytotal As Double

Dim i As Integer
Dim x As Integer

Set wbMaster = ThisWorkbook
Set wbCurrent = ActiveWorkbook
Set wsPS = ActiveSheet

wsPS.Select
y = ActiveSheet.UsedRange.Rows.Count
strSearch = Cells(1, 1).Value
Set dlr = Range(Cells(y, FindColumnHeader("WAC")), Cells(2, FindColumnHeader("WAC")))
Set qt1 = Range(Cells(y, FindColumnHeader("Opportunity @ PUM")), Cells(2, FindColumnHeader("Opportunity @ PUM")))
Set qt2 = Range(Cells(y, FindColumnHeader("Max Qty @ Quoted Pricing")), Cells(2, FindColumnHeader("Max Qty @ Quoted Pricing")))
currentrank = Range(FindColumnHeader("Current Rank") & y).Value




ExitRoutine:
Application.ScreenUpdating = True
Set wbCurrent = Nothing
Set wbMaster = Nothing
Exit Sub


errorHandler:
MsgBox "Error: " & Err.Description, vbCritical
GoTo ExitRoutine
End Sub

current rank is where the ranking information goes...and then i will use a when value = whatever, calculate. Am should it not be a string? I'm getting application defined or object defined error when i define current rank.

Let me know your thoughts.
Help is much appreciated.

[jolivanes]

If your attached workbook is an example of what effort you want to put into helping people understand your problem, I would say that you have slim or no chance of someone helping you.
People would like to see before and after for a few instances, like ten or so.

[LaBamba]

I started using vba a couple of months ago and fell in love with its utility and also the forum the excel community has provided.

I understand if the workbook provided seems useless...If i could provide a before and after, i would think I'm on the right track. I am stuck on the initial stages...the concepts, the dimensions. If you look at my code, i haven't even begun to write my For Each loop.

I know what I'm after is possible and turn to this forum because it has helped me in the past...conceptually and then i take the necessary steps to complete the task.

I understand if i failed at this thread, perhaps i will write it differently.

Thanks for your continued support.

[jolivanes]

This puts the results in Column BC, not in your master workbook (I could not find that anywhere!!!)
The c.Offset(,31) is Column BC. Change that if you have information or data in that Column.
Better yet, make sure you try it on a copy of your workbook.





Sub Try()
Dim c As Range, lr As Long
lr = Cells(Rows.Count, 24).End(xlUp).Row
Application.ScreenUpdating = False
For Each c In Range("X1:X" & lr)
Select Case c.Value
Case 1
c.Offset(, 31).Value = c.Offset(, 3).Value * c.Offset(, 30).Value
Case 2
c.Offset(, 31).Value = (c.Offset(, 3).Value + c.Offset(, 4).Value) * c.Offset(, 30).Value
End Select
Next c
Application.ScreenUpdating = True
End Sub


Hope it helps you at least a little bit