Macro Code to run in continuously for 105 times

[sd266]

Hi Team, I am pretty novice in vba coding and trying to learn in by recording macros. I have come across a situation where macro recording is failing to fetch me the outcome.


Scenario:
I have data say in Sheet1 in column A,B & C. Now I want the data of A1,B1,C1, to be pasted in Sheet2 cell C3, C5 & C7 and then run/call Macro1(already existing) after waiting for 30 seconds post copying as there are some links which needs to be updated, once Macro1 run is complete I want the output which comes at C9 & C11 of sheet 2 to be pasted at D1 & E1. Next I want to put the A2,B2,C2 of sheet1 again into sheet2 and then call macro1 again and I want to loop this 105 times as max data will be till A105,B105,C105. Now the 2 contingency is that I want the macro to skip to next cell if it finds A1 or C1 to be blank or #N/A.


Let me know if the scenario needs any further clarification


Regards


SD

 

[jbeaucaire]

Something like this?

Option Explicit

Sub RunTemplateLoop()
Dim Rw As Long, LR As Long, CalcMode As Long
Dim ws1 As Worksheet, ws2 As Worksheet

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
Set ws1 = ThisWorkbook.Sheets("Sheet1")                     'adjust name here as needed
Set ws2 = ThisWorkbook.Sheets("Sheet2")                     'adjust name here as needed

With ws1
    LR = .Range("A" & .Rows.Count).End(xlUp).Row            'get number of used rows looking UP column A
    For Rw = 1 To LR                                        'loop one row at a time, adjust starting row1 as needed
        ws2.Range("C3").Value = .Range("A" & Rw).Value      'copy in test values
        ws2.Range("C5").Value = .Range("B" & Rw).Value
        ws2.Range("C7").Value = .Range("C" & Rw).Value
        ws2.Calculate                                       'calculate
        DoEvents                                            'might need to add a Application.Wait command here, but maybe not
        .Range("D" & Rw).Value = ws2.Range("C9").Value      'copy results back out
        .Range("E" & Rw).Value = ws2.Range("C11").Value
    Next Rw
End With

Application.Calculation = CalcMode

End Sub

 

[sd266]

Hi Jerry, Thank you so much for your response. It is definitely towards what I am expecting for but I want to throw in some more conditions as below.

Step 1: The macro2(which I am seeking help to create should copy sheet1 D3 value to sheet2 D3, sheet1 E2 value to sheet2 D5 and then sheet1 E3 value to sheet2 D5 with a condition that none of the corresponding row value in column U,V,W,X are Not Attenteded
Step 2: Once that sheet1 data are copied to sheet2 corresponding cells the macro2 should wait for 45 seconds and then call macro1(already existing in workbook)
Step 3: Once the Macro2 is run if in sheet2 cell P3,P4,P5,P6 are showing result as PASS then it should copy back the result into I3.
Step 4: Then go to the next cell i.e D4,E4 so on and so forth
Step 5: Once it is done with column E it should go to column F and follow the same process just that here it should copy the header from F2 which earlier was E2 and results from this series should be copied to column sheet1 J3 onwards
Step 6: Once done with F it should go to G and follow the same process but F2 shall be replaced by G2 and results should be copied to sheet1 column K3 onwards.

For your reference I am also attaching an image below. Thank you so much for your time.