|
how do I write a loop macro?
I have a spreadsheet that calculates profit by customer by grade. I have
over 1000 customers that I want to use this calculation on. I created the macro but now I want to have the macro go to the next row and next until all 1000 have been calculated. How do I make it so the macro loops to the next then the next etc. Below is the macro that needs to run for each row. Please help.!!! thanks Sheets("Calc").Select Range("B5").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[12]C[2]" Range("B6").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" Range("G4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[1]" Range("K4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[-4]" Range("K5").Select ActiveWindow.SmallScroll Down:=45 Range("M75:N75").Select Selection.Copy Sheets("Customer List").Select Range("J17").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" |
how do I write a loop macro?
Hi,
I'm not sure what you mean by "next row" in this case. As I see it your macro: 1. Changes formulas in the worksheet 'Calc' to refer to cells in 'Customer List' 2. Copies M75:N75 from 'Calc' and pastes as values into J17 in 'Customer List' In part 1, the formulas you're changing are not all in one row, and refer to cells that aren't all in one row. If you can explain what moves between macro loops it'll help. A loop in itself is easy: sub demo() dim i as integer for i = 1 to 1000 'do stuff next i end sub "Kim" wrote: I have a spreadsheet that calculates profit by customer by grade. I have over 1000 customers that I want to use this calculation on. I created the macro but now I want to have the macro go to the next row and next until all 1000 have been calculated. How do I make it so the macro loops to the next then the next etc. Below is the macro that needs to run for each row. Please help.!!! thanks Sheets("Calc").Select Range("B5").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[12]C[2]" Range("B6").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" Range("G4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[1]" Range("K4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[-4]" Range("K5").Select ActiveWindow.SmallScroll Down:=45 Range("M75:N75").Select Selection.Copy Sheets("Customer List").Select Range("J17").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" |
how do I write a loop macro?
Hi Kim, It looks like you stared with a recorded macro, It really isn't
neccesary to select object in VBA to manipulate them. I hammered this out pretty quick but didn't test it. Copy your workbook and try this. Public Sub Calc() Dim aWorksheet As Worksheet Dim I As Long Dim Lastrow As Long Set aWorksheet = Worksheets("Calc") 'May require modification 'Count number of used cells in worksheet calc 'column 1 to determine last row Lastrow = aWorksheet.Cells(Rows.Count, 1).End(xlUp).Row '2 Assumes column headers For I = 2 To Lastrow With aWorksheet .Range("B5").FormulaR1C1 = "='Customer List'!R[12]C[2]" .Range("B6").FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" .Range("G4").FormulaR1C1 = "='Customer List'!R[13]C[1]" .Range("K4").FormulaR1C1 = "='Customer List'!R[13]C[-4]" .Range("M75:N75").Copy End With Worksheets("Customer List").Range("J12").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" Next End Sub "Kim" wrote: I have a spreadsheet that calculates profit by customer by grade. I have over 1000 customers that I want to use this calculation on. I created the macro but now I want to have the macro go to the next row and next until all 1000 have been calculated. How do I make it so the macro loops to the next then the next etc. Below is the macro that needs to run for each row. Please help.!!! thanks Sheets("Calc").Select Range("B5").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[12]C[2]" Range("B6").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" Range("G4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[1]" Range("K4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[-4]" Range("K5").Select ActiveWindow.SmallScroll Down:=45 Range("M75:N75").Select Selection.Copy Sheets("Customer List").Select Range("J17").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" |
how do I write a loop macro?
Jeff - thanks, but I am getting a syntax error
on this part? Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Can you offer me any other help on this. thanks so much - I think my job depends on this model working right!!!! "Jeff" wrote: Hi Kim, It looks like you stared with a recorded macro, It really isn't neccesary to select object in VBA to manipulate them. I hammered this out pretty quick but didn't test it. Copy your workbook and try this. Public Sub Calc() Dim aWorksheet As Worksheet Dim I As Long Dim Lastrow As Long Set aWorksheet = Worksheets("Calc") 'May require modification 'Count number of used cells in worksheet calc 'column 1 to determine last row Lastrow = aWorksheet.Cells(Rows.Count, 1).End(xlUp).Row '2 Assumes column headers For I = 2 To Lastrow With aWorksheet .Range("B5").FormulaR1C1 = "='Customer List'!R[12]C[2]" .Range("B6").FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" .Range("G4").FormulaR1C1 = "='Customer List'!R[13]C[1]" .Range("K4").FormulaR1C1 = "='Customer List'!R[13]C[-4]" .Range("M75:N75").Copy End With Worksheets("Customer List").Range("J12").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" Next End Sub "Kim" wrote: I have a spreadsheet that calculates profit by customer by grade. I have over 1000 customers that I want to use this calculation on. I created the macro but now I want to have the macro go to the next row and next until all 1000 have been calculated. How do I make it so the macro loops to the next then the next etc. Below is the macro that needs to run for each row. Please help.!!! thanks Sheets("Calc").Select Range("B5").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[12]C[2]" Range("B6").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" Range("G4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[1]" Range("K4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[-4]" Range("K5").Select ActiveWindow.SmallScroll Down:=45 Range("M75:N75").Select Selection.Copy Sheets("Customer List").Select Range("J17").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" |
how do I write a loop macro?
I assume the Words are red, Simple fix
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False "Kim" wrote: Jeff - thanks, but I am getting a syntax error on this part? Can you offer me any other help on this. thanks so much - I think my job depends on this model working right!!!! "Jeff" wrote: Hi Kim, It looks like you stared with a recorded macro, It really isn't neccesary to select object in VBA to manipulate them. I hammered this out pretty quick but didn't test it. Copy your workbook and try this. Public Sub Calc() Dim aWorksheet As Worksheet Dim I As Long Dim Lastrow As Long Set aWorksheet = Worksheets("Calc") 'May require modification 'Count number of used cells in worksheet calc 'column 1 to determine last row Lastrow = aWorksheet.Cells(Rows.Count, 1).End(xlUp).Row '2 Assumes column headers For I = 2 To Lastrow With aWorksheet .Range("B5").FormulaR1C1 = "='Customer List'!R[12]C[2]" .Range("B6").FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" .Range("G4").FormulaR1C1 = "='Customer List'!R[13]C[1]" .Range("K4").FormulaR1C1 = "='Customer List'!R[13]C[-4]" .Range("M75:N75").Copy End With Worksheets("Customer List").Range("J12").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" Next End Sub "Kim" wrote: I have a spreadsheet that calculates profit by customer by grade. I have over 1000 customers that I want to use this calculation on. I created the macro but now I want to have the macro go to the next row and next until all 1000 have been calculated. How do I make it so the macro loops to the next then the next etc. Below is the macro that needs to run for each row. Please help.!!! thanks Sheets("Calc").Select Range("B5").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[12]C[2]" Range("B6").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" Range("G4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[1]" Range("K4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[-4]" Range("K5").Select ActiveWindow.SmallScroll Down:=45 Range("M75:N75").Select Selection.Copy Sheets("Customer List").Select Range("J17").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" |
how do I write a loop macro?
Make sure all parameters are on the same line.
"Kim" wrote: Jeff - thanks, but I am getting a syntax error on this part? Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Can you offer me any other help on this. thanks so much - I think my job depends on this model working right!!!! "Jeff" wrote: Hi Kim, It looks like you stared with a recorded macro, It really isn't neccesary to select object in VBA to manipulate them. I hammered this out pretty quick but didn't test it. Copy your workbook and try this. Public Sub Calc() Dim aWorksheet As Worksheet Dim I As Long Dim Lastrow As Long Set aWorksheet = Worksheets("Calc") 'May require modification 'Count number of used cells in worksheet calc 'column 1 to determine last row Lastrow = aWorksheet.Cells(Rows.Count, 1).End(xlUp).Row '2 Assumes column headers For I = 2 To Lastrow With aWorksheet .Range("B5").FormulaR1C1 = "='Customer List'!R[12]C[2]" .Range("B6").FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" .Range("G4").FormulaR1C1 = "='Customer List'!R[13]C[1]" .Range("K4").FormulaR1C1 = "='Customer List'!R[13]C[-4]" .Range("M75:N75").Copy End With Worksheets("Customer List").Range("J12").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" Next End Sub "Kim" wrote: I have a spreadsheet that calculates profit by customer by grade. I have over 1000 customers that I want to use this calculation on. I created the macro but now I want to have the macro go to the next row and next until all 1000 have been calculated. How do I make it so the macro loops to the next then the next etc. Below is the macro that needs to run for each row. Please help.!!! thanks Sheets("Calc").Select Range("B5").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[12]C[2]" Range("B6").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" Range("G4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[1]" Range("K4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[-4]" Range("K5").Select ActiveWindow.SmallScroll Down:=45 Range("M75:N75").Select Selection.Copy Sheets("Customer List").Select Range("J17").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" |
how do I write a loop macro?
Ok, I think I know what you want, but I had to make some guesses on how your
worksheets are setup. I assumed your customer list starts in row 17. I also assume cells B5, B6, K4, & G4 in "Calc" are part of some combination of functions and the results are displayed in M75 & N75. So what you want to do is get the values from each customer and put those values in cells B5, B6, K4, & G4, then copy the results in M75 & N75 and paste them next to the customer. Am I right? If so, this code will work for you. If not, tell me I'm stupid. Hope this helps! If so, let me know, click "YES" below. Option Explicit Sub TransferValues() Dim wksList As Worksheet Dim LastRow As Long Dim i As Long ' find last row of customer column Set wksList = Sheets("Customer List") LastRow = wksList.Cells(Rows.Count, "A").End(xlUp).Row For i = 17 To LastRow With Sheets("Calc") .Range("B5").Value = wksList.Cells(i, "D").Value .Range("B6").Value = wksList.Cells(i, "E").Value * 1000 .Range("K4").Value = wksList.Cells(i, "G").Value .Range("G4").Value = wksList.Cells(i, "H").Value .Range("M75:N75").Copy End With ' paste M75:N75 range in J:K With wksList.Cells(i, "J") .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Style = "Comma" End With Application.CutCopyMode = False Next i End Sub -- Cheers, Ryan "Kim" wrote: I have a spreadsheet that calculates profit by customer by grade. I have over 1000 customers that I want to use this calculation on. I created the macro but now I want to have the macro go to the next row and next until all 1000 have been calculated. How do I make it so the macro loops to the next then the next etc. Below is the macro that needs to run for each row. Please help.!!! thanks Sheets("Calc").Select Range("B5").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[12]C[2]" Range("B6").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" Range("G4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[1]" Range("K4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[-4]" Range("K5").Select ActiveWindow.SmallScroll Down:=45 Range("M75:N75").Select Selection.Copy Sheets("Customer List").Select Range("J17").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" |
how do I write a loop macro?
YOU ARE AWESOME!!!!
How do I learn this stuff? Here I thought I was good at excel but these macros and VB formulas are what I now need to learn. thank you so much!!!!:-)) "Ryan H" wrote: Ok, I think I know what you want, but I had to make some guesses on how your worksheets are setup. I assumed your customer list starts in row 17. I also assume cells B5, B6, K4, & G4 in "Calc" are part of some combination of functions and the results are displayed in M75 & N75. So what you want to do is get the values from each customer and put those values in cells B5, B6, K4, & G4, then copy the results in M75 & N75 and paste them next to the customer. Am I right? If so, this code will work for you. If not, tell me I'm stupid. Hope this helps! If so, let me know, click "YES" below. Option Explicit Sub TransferValues() Dim wksList As Worksheet Dim LastRow As Long Dim i As Long ' find last row of customer column Set wksList = Sheets("Customer List") LastRow = wksList.Cells(Rows.Count, "A").End(xlUp).Row For i = 17 To LastRow With Sheets("Calc") .Range("B5").Value = wksList.Cells(i, "D").Value .Range("B6").Value = wksList.Cells(i, "E").Value * 1000 .Range("K4").Value = wksList.Cells(i, "G").Value .Range("G4").Value = wksList.Cells(i, "H").Value .Range("M75:N75").Copy End With ' paste M75:N75 range in J:K With wksList.Cells(i, "J") .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Style = "Comma" End With Application.CutCopyMode = False Next i End Sub -- Cheers, Ryan "Kim" wrote: I have a spreadsheet that calculates profit by customer by grade. I have over 1000 customers that I want to use this calculation on. I created the macro but now I want to have the macro go to the next row and next until all 1000 have been calculated. How do I make it so the macro loops to the next then the next etc. Below is the macro that needs to run for each row. Please help.!!! thanks Sheets("Calc").Select Range("B5").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[12]C[2]" Range("B6").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" Range("G4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[1]" Range("K4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[-4]" Range("K5").Select ActiveWindow.SmallScroll Down:=45 Range("M75:N75").Select Selection.Copy Sheets("Customer List").Select Range("J17").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" |
how do I write a loop macro?
Jeff - thanks for trying to help.
I still could not get it to work this way - Ryan H posted also and his worked the first time. Double checked to make sure the results were right and they are after doing several manually (or individually) on the calc sheet. thanks again for your help. I am sure it would have worked with what you did, I think I was messing it up. Kim "Jeff" wrote: I assume the Words are red, Simple fix Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False "Kim" wrote: Jeff - thanks, but I am getting a syntax error on this part? Can you offer me any other help on this. thanks so much - I think my job depends on this model working right!!!! "Jeff" wrote: Hi Kim, It looks like you stared with a recorded macro, It really isn't neccesary to select object in VBA to manipulate them. I hammered this out pretty quick but didn't test it. Copy your workbook and try this. Public Sub Calc() Dim aWorksheet As Worksheet Dim I As Long Dim Lastrow As Long Set aWorksheet = Worksheets("Calc") 'May require modification 'Count number of used cells in worksheet calc 'column 1 to determine last row Lastrow = aWorksheet.Cells(Rows.Count, 1).End(xlUp).Row '2 Assumes column headers For I = 2 To Lastrow With aWorksheet .Range("B5").FormulaR1C1 = "='Customer List'!R[12]C[2]" .Range("B6").FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" .Range("G4").FormulaR1C1 = "='Customer List'!R[13]C[1]" .Range("K4").FormulaR1C1 = "='Customer List'!R[13]C[-4]" .Range("M75:N75").Copy End With Worksheets("Customer List").Range("J12").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" Next End Sub "Kim" wrote: I have a spreadsheet that calculates profit by customer by grade. I have over 1000 customers that I want to use this calculation on. I created the macro but now I want to have the macro go to the next row and next until all 1000 have been calculated. How do I make it so the macro loops to the next then the next etc. Below is the macro that needs to run for each row. Please help.!!! thanks Sheets("Calc").Select Range("B5").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[12]C[2]" Range("B6").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" Range("G4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[1]" Range("K4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[-4]" Range("K5").Select ActiveWindow.SmallScroll Down:=45 Range("M75:N75").Select Selection.Copy Sheets("Customer List").Select Range("J17").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" |
how do I write a loop macro?
Good! I didn't get a "your stupid" relpy, lol. I thought I was good at
Excel, until I started learning macros a few years ago. Then I got hooked. I bought a few books and my boss gave me some projects to work on. I worked on them at work and at home. This forum was a huge help in the learning process along with the macro recorder. And when you think you know it all, someone else shows you something new. VBA is impossible to fully learn. I'd love to meet someone who knows "everything" in VBA. I'm actually in the process of teaching myself C# so I can get a job as a C# programmer. -- Cheers, Ryan "Kim" wrote: YOU ARE AWESOME!!!! How do I learn this stuff? Here I thought I was good at excel but these macros and VB formulas are what I now need to learn. thank you so much!!!!:-)) "Ryan H" wrote: Ok, I think I know what you want, but I had to make some guesses on how your worksheets are setup. I assumed your customer list starts in row 17. I also assume cells B5, B6, K4, & G4 in "Calc" are part of some combination of functions and the results are displayed in M75 & N75. So what you want to do is get the values from each customer and put those values in cells B5, B6, K4, & G4, then copy the results in M75 & N75 and paste them next to the customer. Am I right? If so, this code will work for you. If not, tell me I'm stupid. Hope this helps! If so, let me know, click "YES" below. Option Explicit Sub TransferValues() Dim wksList As Worksheet Dim LastRow As Long Dim i As Long ' find last row of customer column Set wksList = Sheets("Customer List") LastRow = wksList.Cells(Rows.Count, "A").End(xlUp).Row For i = 17 To LastRow With Sheets("Calc") .Range("B5").Value = wksList.Cells(i, "D").Value .Range("B6").Value = wksList.Cells(i, "E").Value * 1000 .Range("K4").Value = wksList.Cells(i, "G").Value .Range("G4").Value = wksList.Cells(i, "H").Value .Range("M75:N75").Copy End With ' paste M75:N75 range in J:K With wksList.Cells(i, "J") .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Style = "Comma" End With Application.CutCopyMode = False Next i End Sub -- Cheers, Ryan "Kim" wrote: I have a spreadsheet that calculates profit by customer by grade. I have over 1000 customers that I want to use this calculation on. I created the macro but now I want to have the macro go to the next row and next until all 1000 have been calculated. How do I make it so the macro loops to the next then the next etc. Below is the macro that needs to run for each row. Please help.!!! thanks Sheets("Calc").Select Range("B5").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[12]C[2]" Range("B6").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[11]C[3]*1000" Range("G4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[1]" Range("K4").Select ActiveCell.FormulaR1C1 = "='Customer List'!R[13]C[-4]" Range("K5").Select ActiveWindow.SmallScroll Down:=45 Range("M75:N75").Select Selection.Copy Sheets("Customer List").Select Range("J17").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Style = "Comma" |
| All times are GMT +1. The time now is 08:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com