Excel VBA copy and paste loop with logic condition Solved/Closed

Question

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. 

Many thanks in advance!

TrowaD · Accepted Answer

Hi Yogibearv,

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

TrowaD · Answer

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

Yogibearv · Answer

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

Yogibearv · Answer

Hi Trowa,

Works perfectly! Fantastic help. 

Looks so simple now its done :)- 

This will save me loads of time, once again a big Thanks!

Kind regards

Yogibearv

Excel VBA copy and paste loop with logic condition

4 responses

Similar discussions

Subscribe To Our Newsletter!