Excel Vba loops

[excelda]

Hello everyone I'm sure this has been covered a million times but I have not found a solution on the web or in one of the excel vba books I have. I am not a programmer just a spreadsheet user that plays with simple vba to automate some spreadsheets I use at work.
What I am trying to do is copy data in 2 columns that sets a start and stop for a vlookup. from the v lookup I average the results then paste the results back in my data table.

I never know how many lines of data I will have so to me in seems like a do while loop should be the best. I'm just not sure how to implement it.
If i record a macro it looks like this for the first line.

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 6/27/2012 by Charles Dugger
'
'
Range("J32:K32").Select
Selection.Copy
Range("J26").Select
ActiveSheet.Paste
Range("O26").Select
Application.CutCopyMode = False
Selection.Copy
Range("O32").Select
Application.Run "PERSONAL.XLS!pastspecal"
End Sub


Help how in the world do I set this up in a loop.


Sorry if this is a first grade question but this has been bugging me for a week with no progress and I have tried with zero luck.

 

[excelda]

Yes!! I made it work.
Here is my solution I'm quite sure there are better ways but it works

Private Sub CommandButton2_Click()
x = 32
y = 32
p = 32
Do While Cells(x, 10) <> ""
'select first depth and paste it to vlookup
Cells(x, 10).Select
Selection.Copy
Range("J26").Select
ActiveSheet.Paste
'selects second depth and paste to v lookup
Cells(y, 11).Select
Selection.Copy
Range("K26").Select
ActiveSheet.Paste
'select watercut value and paste to table
Range("O26").Select
Selection.Copy
Cells(p, 15).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
x = x + 1
y = y + 1
p = p + 1
Loop
End Sub

 

[drsarao]

Hi,
Welcome to Board.
You beat me to it!

A simpler, faster version:

Sub CopyPaste()
lr = Cells(Rows.Count, "J").End(xlUp).Row 'last row in column J
For x = 32 To lr  'need only one counter
    Cells(x, "J").Copy Destination:=Range("J26") 'copy first depth to vlookup
    Cells(x, "K").Copy Destination:=Range("K26") 'copy second depth vlookup
    Range("O26").Copy                            'copy watercut
    Cells(x, "O").PasteSpecial Paste:=xlPasteValues  'paste value to table
Next x
End Sub

.Selects and .Activates are usually not needed and they slow down the code.
Only 1 counter is needed here as you are incrementing ALL by 1 thru each pass.
Finding the last row and using it in a For...Next loop is more efficient than checking for empty cell on each pass in a While....loop

 

[excelda]

Thanks that had been driving crazy to the point of no sleep.
Your way is much prettier mine is down and dirty.

 

[drsarao]

Thanks that had been driving crazy to the point of no sleep.
Your way is much prettier mine is down and dirty.

Everybody starts dirty!