VBA loop calculation

Proche17

New Member
Joined
Jan 21, 2021
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi, any help for my VBA issue would be very helpful.

I want to run certain variables in a formula, and want all results for all data back ordered in the same column.

Referring to the added sheet:
i have a simple calculation, variables depend on the number in cell B2 on tab'calculation'.
What i would like to have as result, is in column F on tab 'data' all results of the calculation on tab 'calculation', so result should be:
in cell F2 = 990, in cell F3 = 1.513, in cell F4 = 2.024, ....

Thank you in advance!
 

Attachments

  • screenshot 2.png
    screenshot 2.png
    17.1 KB · Views: 5
  • screenshot 1.PNG
    screenshot 1.PNG
    22 KB · Views: 4

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows
try this:

VBA Code:
Sub TJ()
    Dim lastRow As Long
    
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
        For i = 2 To lastRow
            .Range("f" & i).Value = (.Range("c" & i).Value + .Range("d" & i).Value) * (1 - .Range("e" & i).Value)
        Next i
    End With
End Sub
 

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows
I forgot to add sort option, try this:

VBA Code:
Sub TJ()
    Dim lastRow As Long
   
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
        For i = 2 To lastRow
            .Range("f" & i).Value = (.Range("c" & i).Value + .Range("d" & i).Value) * (1 - .Range("e" & i).Value)
        Next i
    End With
   
    With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter
        .Sort Key1:=.Range("f1"), Order1:=xlAscending, Header:=xlGuess
    End With
End Sub
 

Proche17

New Member
Joined
Jan 21, 2021
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Sweet, thanks for your swift reply!

I've seen that you put the formula of the calculation in the VBA Code (.Range("f" & i).Value = (.Range("c" & i).Value + .Range("d" & i).Value) * (1 - .Range("e" & i).Value))

is there a possibility to not do this, and and use only cell B1 (the number of which all data in cells B5, D5 and F5 are linked to) as variable?
Reason why i am asking this, is that this is just a simple example of the original file in which i want to use the VBA - original file is a long calculation sheet, depending on some variables.
Thank you!
 

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows
Each row should have B1 value in F column? I'm not getting you.
 

Proche17

New Member
Joined
Jan 21, 2021
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi, no, in cell B1 you can now find the value'1'. Cells B5, D5 and F5 are linked to cell B1, as the values in B5, D5 and F5 are index matched to the other tab.
Eg. when value '1' is in cell B1; automatically B5 = 1.000, D5 = 100 and F5 = 10%, all linked to number 1 - name 'Alex'.
When value '2' is in cell B2 all data of number 2, name 'Brad' is shown (1.500 - 200 - 11%) and so on.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,354
Members
414,060
Latest member
hermanseck

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
Top