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"