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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top