Excel Vba loops

excelda

New Member
Joined
Jun 27, 2012
Messages
43
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

excelda

New Member
Joined
Jun 27, 2012
Messages
43
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

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Welcome to Board.
You beat me to it!

A simpler, faster version:
Code:
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

New Member
Joined
Jun 27, 2012
Messages
43
Thanks that had been driving crazy to the point of no sleep.
Your way is much prettier mine is down and dirty. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,089
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top