Excel VBA Copy and paste loop with logic condition [Solved/Closed]
Yogibearv2
Posts Monday October 20, 2014Registration date
October 21, 2014
Last seen
-
Oct 20, 2014 at 07:34 PM - Latest reply: Yogibearv
- Oct 28, 2014 at 04:27 PM
Hi, I am new to VBA and would appreciate some help with a problem I am trying to solve.
I would like a macro that is able to copy information from a cell (in this case I7) into another on the same worksheet (B7). I then would like the macro to check what is written in Cell D5, If the value is "NO", then copy I8 to B8 and so on (offset by one row) .... When D5 changes to "OK" I would like the macro to stop.
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
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.
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.
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.
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.
has helped 1785 users this month