Excel VBA copy and paste loop with logic condition [Solved/Closed]
Yogibearv
- Posts
- 2
- Registration date
- Monday October 20, 2014
- Last seen
- October 21, 2014
Related:
- Excel macro to copy and paste values if criteria are met
- Copy and paste loop in vba based on cell values - Best answers
- Excel vba copy cell if criteria met - Best answers
- Excel macro to copy and paste from one workbook to another - How-To - Excel
- Macro to copy and paste values ✓ - Forum - Excel
- Excel macro paste in next empty row ✓ - Forum - Excel
- Excel macro copy and paste into next empty column - Forum - Excel
- Excel - A Macro to copy and paste data in next blank cell - How-To - Excel
4 replies
Best answer
- Posts
- 2441
- Registration date
- Sunday September 12, 2010
- Status
Hi Yogibearv,
So you use a formula for D5, that makes more sense. Try this code:
Best regards,
Trowa
So you use a formula for D5, that makes more sense. Try this code:
Sub RunMe()
Dim lRow, x As Integer
lRow = Range("I" & Rows.Count).End(xlUp).Row
x = 6
Do
x = x + 1
Range("B" & x).Value = Range("I" & x).Value
Range("I" & x).ClearContents
Loop Until IsEmpty(Range("I" & x + 1)) Or x = lRow Or Range("D5").Value = "OK"
End Sub
Best regards,
Trowa
A few words of thanks would be greatly appreciated. Add comment
4323 users have said thank you to us this month
- Posts
- 2441
- Registration date
- Sunday September 12, 2010
- Status
Hi Yogibearv,
Macro's are typically used when formula's can't do the job.
You want to put the info from I7 in B7: type = in B7, select I7 and hit enter.
If cell D5 = "NO" then copy I8 to B8, use this formula in B8: =IF(D$5="NO",I8,"").
The thing that is confusing me is your last sentence "When D5 changes to "OK" I would like the macro to stop." When a macro is running, you can't change values and if you could, the macro would run so fast you won't be able to input a value at the right time.
It is possible to ask after every row if the code should keep running.
Let me know your thoughts about this.
Best regards,
Trowa
Macro's are typically used when formula's can't do the job.
You want to put the info from I7 in B7: type = in B7, select I7 and hit enter.
If cell D5 = "NO" then copy I8 to B8, use this formula in B8: =IF(D$5="NO",I8,"").
The thing that is confusing me is your last sentence "When D5 changes to "OK" I would like the macro to stop." When a macro is running, you can't change values and if you could, the macro would run so fast you won't be able to input a value at the right time.
It is possible to ask after every row if the code should keep running.
Let me know your thoughts about this.
Best regards,
Trowa
Hi Trowa,
Thank you for your reply, I will try and clarify what I am trying to do, but please bear with me as I am new to VBA.
The problem that I am working on is 'interactive' in such that the values being entered into the input column (B) are entered one by one and evaluated by the formulas in the workbook connected to that column. Once a certain sequence is 'recognised' by the formulas this is highlighted by conditional formating and the input by the user (me) is stopped.
I have at this stage managed to create Three small macros attached to buttons on the worksheet, that: 1.copy the values from column B to Column I, 2. Clear the values in Column B 3. Invert the values in in Column I.
At this stage I would like the values to be copied one at a time from I7 onwards to the corresponding cell in column B i.e I7 to B7, I8 to B8 etc.
This would be simple as you have stated but I would like this to happen in steps of one value at a time until either Cell D5 changes to "OK" or a blank cell is encountered in Column I. As far as I understand this can be achieved by creating a macro that incorporates a loop function and a Do until function or similar.
I have tried for two days to work this out using various sources to try and learn VBA but at this point I am still unable to get my head around it...
In the end I hope to be able to combine all the steps into one macro that is triggered by a True / False statement in yet other cell governed by yet another formula...
i.e If J7 for example returns "True" after 35 values entered I would like the final Macro to run.
I am not asking for the complete answer to this problem (although it would be nice ;), just some guidance on how to write the section of the macro as described above... taking into account as I have stated, that I am a complete beginner...
I hope this helps explain a little better what I am trying to do.
Many thanks
Yogibearv
Thank you for your reply, I will try and clarify what I am trying to do, but please bear with me as I am new to VBA.
The problem that I am working on is 'interactive' in such that the values being entered into the input column (B) are entered one by one and evaluated by the formulas in the workbook connected to that column. Once a certain sequence is 'recognised' by the formulas this is highlighted by conditional formating and the input by the user (me) is stopped.
I have at this stage managed to create Three small macros attached to buttons on the worksheet, that: 1.copy the values from column B to Column I, 2. Clear the values in Column B 3. Invert the values in in Column I.
At this stage I would like the values to be copied one at a time from I7 onwards to the corresponding cell in column B i.e I7 to B7, I8 to B8 etc.
This would be simple as you have stated but I would like this to happen in steps of one value at a time until either Cell D5 changes to "OK" or a blank cell is encountered in Column I. As far as I understand this can be achieved by creating a macro that incorporates a loop function and a Do until function or similar.
I have tried for two days to work this out using various sources to try and learn VBA but at this point I am still unable to get my head around it...
In the end I hope to be able to combine all the steps into one macro that is triggered by a True / False statement in yet other cell governed by yet another formula...
i.e If J7 for example returns "True" after 35 values entered I would like the final Macro to run.
I am not asking for the complete answer to this problem (although it would be nice ;), just some guidance on how to write the section of the macro as described above... taking into account as I have stated, that I am a complete beginner...
I hope this helps explain a little better what I am trying to do.
Many thanks
Yogibearv
- Posts
- 2441
- Registration date
- Sunday September 12, 2010
- Status
Hi Yogibearv,
Still not entirely clear. :(
What makes the value in cell D5 change into OK while running the code?
What do you mean by "Invert the values in in Column I."?
Can you provide examples?
Here is a code which will loop through column I starting at row 7. The values in column I will be moved to column B. The loop will stop when an empty cell is found in Column I or when the last row used is reached.
Please consider the options to insert a picture using the "mountain" button at the top of the message body or to upload your file using a file sharing site like www.speedyshare.com or ge.tt and post back the download link. Always be careful with sensitive info. This may help in getting your point across to me.
Best regards,
Trowa
Still not entirely clear. :(
What makes the value in cell D5 change into OK while running the code?
What do you mean by "Invert the values in in Column I."?
Can you provide examples?
Here is a code which will loop through column I starting at row 7. The values in column I will be moved to column B. The loop will stop when an empty cell is found in Column I or when the last row used is reached.
Sub RunMe()
Dim lRow, x As Integer
lRow = Range("I" & Rows.Count).End(xlUp).Row
x = 6
Do
x = x + 1
Range("B" & x).Value = Range("I" & x).Value
Range("I" & x).ClearContents
Loop Until IsEmpty(Range("I" & x + 1)) Or x = lRow
End Sub
Please consider the options to insert a picture using the "mountain" button at the top of the message body or to upload your file using a file sharing site like www.speedyshare.com or ge.tt and post back the download link. Always be careful with sensitive info. This may help in getting your point across to me.
Best regards,
Trowa
Hi Trowa,
Thank you for your help on this it has been really useful so far.
The value in Cell D5 changes from "NO" to "OK" when the formula in D5 recognises that a certain criteria has been fulfilled.
For example; The formula could add all the numbers in the Column B and when the total is greater than 10 it will change to "OK". this is just an example as the formula is a bit more complex but the result is the same, the formula looks a the values that are in "B" and returns "NO" or "OK" depending on the values it finds.
so the idea would be for the macro to copy the numbers across one at a time, then (pause?) after each copy paste and "look" at D5. If D5 is "NO" then copy the next number. If the condition is met by the formula in D5 and you get "OK" the macro can stop.
Is this possible or does a macro have to finish before excel is able / allowed to update formulas etc.?
If the macro has to finish completely then I guess what I am asking is not possible.
Looking forward to your thoughts
Kind regards.
Yogibearv
Thank you for your help on this it has been really useful so far.
The value in Cell D5 changes from "NO" to "OK" when the formula in D5 recognises that a certain criteria has been fulfilled.
For example; The formula could add all the numbers in the Column B and when the total is greater than 10 it will change to "OK". this is just an example as the formula is a bit more complex but the result is the same, the formula looks a the values that are in "B" and returns "NO" or "OK" depending on the values it finds.
so the idea would be for the macro to copy the numbers across one at a time, then (pause?) after each copy paste and "look" at D5. If D5 is "NO" then copy the next number. If the condition is met by the formula in D5 and you get "OK" the macro can stop.
Is this possible or does a macro have to finish before excel is able / allowed to update formulas etc.?
If the macro has to finish completely then I guess what I am asking is not possible.
Looking forward to your thoughts
Kind regards.
Yogibearv