KarlH

New Member
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

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
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
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...

Replies
1
Views
394
Replies
1
Views
293
Replies
11
Views
138
Replies
26
Views
727
Replies
1
Views
218

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.

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

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