Running a Macro Several Times

whitefeather

New Member
Joined
May 9, 2016
Messages
29
Hello,

I am using a macro (say, Macro 1) which delivers a value on M8 cell based on the value given on 6 cells (M1 to M6). Now, I have a worksheet of six columns (A to F) and 1000 (variable) rows, which will populate the six cells mentioned earlier for every row and after getting the value by Macro 1, the value will be written in last cell of every row (i.e. G1). Meaning, Macro 1 will be run as the same time as the row count of second worksheet and will write the yielded value at the end cell of every row. How may I achieve this?

Thanks in advance.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I am using a macro (say, Macro 1)
Please post your macro
which will populate the six cells mentioned earlier for every row
What? it will put in exactly the same values from M1 to M6 into all the cells in columns A to F for 1000 (variable) rows?
1000 (variable) rows......the row count of second worksheet
variable/row count of based on what? a certain column?

Can you also give us both worksheet names.

P.S. Can you also reply in the standard board font and not alter it please so I don't have to move forward to read it :rolleyes:
 
Last edited:
Upvote 0
Thanks for your comment. Please find a simplified test file here

https://www.dropbox.com/s/j2ja62ts2ynv9sp/TEST WS.xlsm?dl=0

Here, the macro will deliver a value in B5 cell in sheet 1. In sheet 2, there are data with 16 rows (except title). I want the first three cells of a row to be inputted in the sheet 1- B1, B2 and B3 cells and run the macro then put the output on D2 (for 1st row of data). This will repeat 16 times for each row. The row count will change based on my provided data on sheet 2. It may be 1000. I hope I've described my requirement well.

Sorry for the font as I copy pasted it from another text editor.
 
Upvote 0
A bit confused the only formula (therefore the only value that changes) in Sheet1 is B4 (the ACTUAL INTEREST result) but the D column in Sheet2 is headed ANNUAL RATE (which is a fixed value in Sheet1). Can you explain what value you want in Column D of Sheet2.

I am also a bit confused why you need to transfer the data to Sheet1. Why can't we do all the calculations in Sheet2?
 
Upvote 0
Yes, the only value that Changes in Sheet 1 is B4. The B4 is based on every other cells in sheet 1. The macro 1 runs a goal seek function that changes the value of B5 (Annual Rate) unless the value of B4 becomes '500'. Meaning, the thing I want in D column of sheet 2 is, how much 'ANNUAL RATE' is required to keep the 'ACTUAL INTEREST' value to '500' based on the first three columns of sheet 2.

Yes, we can formulate that in sheet 2, but I just want to know the way to repeat a certain macro in a certain number of times in this given scenario.
 
Upvote 0
If I am understanding correctly then maybe.....

Code:
Sub transCode()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 2 To Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
        Sheets("Sheet2").Cells(i, "A").Resize(, 3).Copy
        Sheets("Sheet1").Range("B1").PasteSpecial Paste:=xlPasteAll, Transpose:=True
        Sheets("Sheet1").Range("B4").GoalSeek Goal:=500, ChangingCell:=Sheets("Sheet1").Range("B5")
        Sheets("Sheet2").Cells(i, "D").Value = Sheets("Sheet1").Cells(5, "B").Value
    Next
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
It worked. Many thanks. But it only works if sheet 1 is selected. If the cursor is on sheet 2, it doesn't work. However, it now serves the purpose. Thanks again.
 
Upvote 0
I can't see anything in the code that would need Sheet1 to be selected and you have removed the file and so I can't test it but as you state "it now serves the purpose" I assume that you are ok as it stands.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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