I have a macro named ENGINECALC, it gives a output for each row in sheet 1. But i need to click on each row to run it and copy the output manually. I want to loop this macro and the output should be save in a sheet 2 in each row.
Sub loop1()
'
' loop1 Macro
'
Sheets("Sheet1").Select
Range("A1").Select
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FinalRow
Call ENGINECALC
#I don't know how to do it. Can anyone help in this please.
you will need to explain more what ENGINECALC is/does and more specifically why it needs to be a seperate macro
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Sorry for not briefing
I made this Code now,
I want to loop the particular portion of this code. if u can find
Selection.ClearContents
I want to loop the code after this, which starts with
Sheets("Sheet2").Select
I want to do the calculation of each row from Sheet 2, as it copies and pastes the values in Calculation sheet
and paste the output from Calculation sheet (Cell J8 ) in each row of timetoregen sheet cells $A$
My full code:
Sub Calfile()
'
' Calfile Macro
'
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range
Set wb1 = ActiveWorkbook
Set PasteStart = [Sheet1!A1]
ChDir ("I'll enter my directory here")
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.csv (*.csv),")
If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)
For Each Sheet In wb2.Sheets
With Sheet.UsedRange
.Copy PasteStart
Set PasteStart = PasteStart.Offset(.Rows.Count)
End With
Next Sheet
End If
wb2.Close
' Input parameters are copied and pasted in sheet 2
Sheets("Sheet1").Select
ab = Sheets("Sheet1").Cells.Find(What:="C_FRSMOKEEQN_AVG", After:=Sheets(1).Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
Columns(ab).Select
Selection.Copy
Sheets("sheet2").Select
Columns("A").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ac = Sheets("Sheet1").Cells.Find(What:="E_NOXD_AVG", After:=Sheets(1).Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
Columns(ac).Select
Selection.Copy
Sheets("sheet2").Select
Columns("B").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ad = Sheets("Sheet1").Cells.Find(What:="C_VFREXG_AVG", After:=Sheets(1).Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
Columns(ad).Select
Selection.Copy
Sheets("sheet2").Select
Columns("C").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ae = Sheets("Sheet1").Cells.Find(What:="T_ATURB01_AVG", After:=Sheets(1).Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
Columns(ae).Select
Selection.Copy
Sheets("sheet2").Select
Columns("D").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("C1").Select
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Range("E2").Select
ActiveCell.FormulaR1C1 = "m³/min"
Range("E3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]*60"
Range("E3").Select
Selection.AutoFill Destination:=Range("E3:E268")
Range("E3:E268").Select
Sheets("Sheet2").Select
Range("F1").Select
ActiveCell.FormulaR1C1 = "T_BDPF01"
Range("F2").Select
ActiveCell.FormulaR1C1 = "deg C"
Range("F3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]-20"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F268")
Range("F3:F268").Select
Sheets("Sheet2").Select
Columns("E:F").Select
Application.CutCopyMode = False
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:F").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Sheet2").Select
Range("A3").Select
Selection.Copy
Sheets("Calculation").Select
Range("E19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet2").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Calculation").Select
Range("E16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet2").Select
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Calculation").Select
Range("E15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet2").Select
Range("D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Calculation").Select
Range("E13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calculation").Select
Range("J8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Timetoregen").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'
End Sub
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
hhmmmm does your code actually do what you want it to do?
then im still not quite sure why you need to call a second macro
what are you doing in the rest of Loop 1?
ps there is so much redundant coding in there and over reliance on select/paste
pps you shouldnt use SHEET as variable for worksheet...its too close to sheets which is an actual function
may i suggest you use ws1, ws2, etc
I'm learning to code. this is my first macro and big one.I'll learn and improve and this code works. I can explain in simple what i want.
I need to know how to loop a part of a code. i dont know how to start. If anyone can give a example, it will be helpful. I'm a slow leaner, I'll do it somehow. Thanks for giving your time and sorry for bad codes.
Sriramroxx
I feel for you but as suggested above, read the forum rules starting with #2 and after that #6.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks