how do I write a loop macro?

K

Kim

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"
 
S

Sam Wilson

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
 
J

Jeff

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
 
K

Kim

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 said:
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 said:
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"
Click to expand...
Click to expand...
 
J

Jeff

I assume the Words are red, Simple fix

Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Kim said:
Jeff - thanks, but I am getting a syntax error
on this part?
Can you offer me any other help on this.
Click to expand...
thanks so much - I think my job depends on this model working right!!!!


Jeff said:
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 said:
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"
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff

Make sure all parameters are on the same line.

Kim said:
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 said:
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 said:
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"
Click to expand...
Click to expand...
Click to expand...
 
R

Ryan H

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
 
K

Kim

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 said:
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 said:
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"
Click to expand...
Click to expand...
 
K

Kim

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 said:
I assume the Words are red, Simple fix

Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Kim said:
Jeff - thanks, but I am getting a syntax error
on this part?
Can you offer me any other help on this.
Click to expand...
thanks so much - I think my job depends on this model working right!!!!


Jeff said:
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



:

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"
Click to expand...
Click to expand...
Click to expand...
 
R

Ryan H

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 said:
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 said:
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 said:
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"
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Totals An Easier Way?? 5
File Name changes with dates and Deleting Rows of Zeros 0
Advanced Filter works in recorder but not while running macro 5
Run-time error 1004 paste method of worksheet class failed 3
Different Results from the Same Macro 2
I'd like to modify mt Macro ... 9
Run macro from active workbook whose name keeps changing 20
HUGE macro 13

Top