Please help me to stop my VBA loop using values :)

KarlH

New Member
Joined
May 13, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
hi all,

I have a simple range copying to fill a column. Idea is to split 408 contracts with yearly amounts into month s. Code is below- value 1 & value 2 reference cells in my excel , which are used to work out how many rows need to be copied/filled..for some reason, my code does nto work. Is there any chance you can help me to get the Loop to stop when value1 = value 2 ? Many manty thanks ! (value 3 is not used yet, so you can ignore it..)
Code:
Sub Get_value()
Dim value1 As Long
Dim value2 As Long
Dim value3 As Long
value1 = ThisWorkbook.Sheets(2).Range("L1").Value 'value from sheet1
value2 = ThisWorkbook.Sheets(2).Range("O1").Value 'value from sheet2
value3 = ThisWorkbook.Sheets(2).Range("d1").Value 'value from sheet2
Do Until value1 = value2
    Sheets("REPORT").Select
    Range("A10").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("DATA").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.End(xlDown).Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Loop
Do Until value1 = value2
End Sub
 
Last edited by a moderator:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,026
Office Version
  1. 365
Platform
  1. Windows
The problem is that you define value1, value2, and value3 outside of your loop (above it).
Within your loop, those three values are never changed.

So since the values are never changed within the loop, you have 1 of 2 scenarios that will happen:
1. If value1 = value2, your loop will stop right away
2. If value1 <> value2, your will get caught in an infinite loop, as those values can never be equal if nothing is changing their value inside the loop
 

KarlH

New Member
Joined
May 13, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Ah - thanks Joe. very useful
to understand.
based on this I have modified like this... and it works ! - Next thing to do is to make sure every contract has 84 months all correctly labelled 2014-2020... that should not be as much fun to work out... :)
its not elegant , as in part I recorded the macro ( obvious I am sure) , but it now does work, and does the exercise way faster than I can. This does allow an entire data base of yearly contract values to be split into months, or a payroll of 110 cost centres with 130 possible transactions to be made into a data base, so it really doe shave an application, and makes me want to learn more ( and share ) . Nice one Joe !
Code:
Sub Button1()
Do Until (ThisWorkbook.Sheets(2).Range("L1") = ThisWorkbook.Sheets(2).Range("O1"))
Sheets("REPORT").Select
Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("DATA").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlDown).Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Loop
End Sub
 

KarlH

New Member
Joined
May 13, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Looking at this, I think I can make it smoother in operation if I start the loop after the data has been copied ready for duplication.... I'll give it a try and share in a few days..gotta get back to the "normal " work now...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,258
Messages
5,641,162
Members
417,195
Latest member
Vishal kumar

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