Continue to Run Next Macro After "IF" Condition is Met

ybh · May 10, 2022

Hi All,
I have a seemingly simple macro but just couldn't make it work.
I use a simple IF..THEN command, as my example below, if Col B >0, copy Col C to Col E
I want to have the macro keeps running from Row 2 to 6, but it will stop if say, first condition is met.

I attached the excel file here with the macro command.
Can anyone advise how should I make it work?
[Copy Cell C to Cell E, if Cell B>0, continuously until the end]

Thanks.

rollis13 · May 10, 2022

You didn't show your macro. What you need is a For/Next.

Code:

For x = 2 To 6
'
'here goes your If/Then/End If
'with reference to cells like this:
'... Range("B" & x) ... ecc.
'
Next x

Marc L · May 10, 2022

Hi, just reproducing the same Excel formula an Excel beginner can operate manually :

Range("E2:E" & [A1].CurrentRegion.Rows.Count).Formula = "=IF(B2>0,C2,"""")"

ybh · May 10, 2022

rollis13 said:
You didn't show your macro. What you need is a For/Next.

Code:

For x = 2 To 6 ' 'here goes your If/Then/End If 'with reference to cells like this: '... Range("B" & x) ... ecc. ' Next x

Hello Rollis13,
Thanks for your reply, my bad for not enable macro in the file.
I re-attached again.

Basically my macro looks something simple as follow:

>>> use code - tags <<<

Code:

Sub Macro2()

If Range("B2").Value > 0 Then
Range("C2").Select
Selection.Copy
Range("E2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ElseIf Range("B3").Value > 0 Then
Range("C3").Select
Selection.Copy
Range("E3").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ElseIf Range("B4").Value > 0 Then
Range("C4").Select
Selection.Copy
Range("E4").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ElseIf Range("B5").Value > 0 Then
Range("C5").Select
Selection.Copy
Range("E5").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ElseIf Range("B6").Value > 0 Then
Range("C6").Select
Selection.Copy
Range("E6").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End If
End Sub

How do I use the For/Next command you mentioned?
Can you briefly include in my macro for me to understand better?
Thanks.

Regards,
ybh

ybh · May 10, 2022

Marc L said:
Hi, just reproducing the same Excel formula an Excel beginner can operate manually :

Range("E2:E" & [A1].CurrentRegion.Rows.Count).Formula = "=IF(B2>0,C2,"""")"

Hi Marc,
Thanks for the recommendation.
Actually I simplify the format for this discussion.
I need the concept and then use a macro to run a more complex spreadsheet.
I just re-attached an excel example with my macro embedded, seeking advice from all the masters here.

Regards,
ybh

Marc L · May 10, 2022

As a reminder according to TEBV rule my original post is the unique VBA codeline necessary !

rollis13 · May 10, 2022

This could be a solution:

Code:

Option Explicit
Sub Macro2()
    Dim x      As Long
    For x = 2 To 6
        If Range("B" & x).Value > 0 Then
            Range("C" & x).Copy Range("E" & x)
        End If
    Next x
    Application.CutCopyMode = False
End Sub

ybh · May 10, 2022

rollis13 said:

This could be a solution:

Code:

Option Explicit
Sub Macro2()
    Dim x      As Long
    For x = 2 To 6
        If Range("B" & x).Value > 0 Then
            Range("C" & x).Copy Range("E" & x)
        End If
    Next x
    Application.CutCopyMode = False
End Sub

Thank you very much rollis13.
It works! Big thumb up

Don't mind I continue from here with another quick question.
Attached is the sample with your macro embedded.

I coupled this with a common function which will trigger macro run whenever there's is a change in cell value.
Something like this:

In my spreadsheet, it looks like this:

If I manually change the value in Cell H1, no issue it will call the macro promptly
But for Cell H2, I program something which will change based on a condition in Cell H3. But somehow when Cell H2 value changes due to this linked condition to Cell H3, it won't call the macro. Seems like it only works if user manual change the value like in Cell H1.
In the attached file, you can try to key in something in Cell H3, Cell H2 will follow suit but not able to call the macro.

Any idea or suggestion?
Thanks in advance.

Regards,
ybh

rollis13 · May 10, 2022

The Event Worksheet_Change (and other) doesn't trigger by formula, you need some sort of workaround, change the previous macro and add this other that uses the event WorkSheet_Calculate:

Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H1")) Is Nothing Then '<- changed
    Call rollis13
End If
End Sub
Private Sub Worksheet_Calculate()
    Dim rng    As Range
    Set rng = Range("H2")
    If Not Intersect(rng, rng) Is Nothing Then
        If rng.Value > 0 Then Call rollis13
    End If
End Sub

Since this is a completely different topic it would've been better to start a new thread.

ybh · May 11, 2022

rollis13 said:
The Event Worksheet_Change (and other) doesn't trigger by formula, you need some sort of workaround, change the previous macro and add this other that uses the event WorkSheet_Calculate:

Code:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("H1")) Is Nothing Then '<- changed Call rollis13 End If End Sub Private Sub Worksheet_Calculate() Dim rng As Range Set rng = Range("H2") If Not Intersect(rng, rng) Is Nothing Then If rng.Value > 0 Then Call rollis13 End If End Sub

Since this is a completely different topic it would've been better to start a new thread.

Good Day to you rollis13!
That event Worksheet Calculate fits perfectly into my project.
Thanks a bunch!

On the macro that you proposed previously:
>>> use code - tags <<<

Code:

Sub rollis13()
Dim x As Long
For x = 2 To 6
If Range("B" & x).Value > 0 Then
Range("C" & x).Copy Range("E" & x)
End If
Next x
Application.CutCopyMode = False
End Sub

I need some guidelines on the paste step.
I am attaching the updated file for ease of discussion.

Say if I want to paste to another Worksheet, "Compile".
In this Compile Worksheet, usually there will be data from A5 onwards.
There is this macro line that copies to the last row of data I always use:

>>> use code - tags <<<

Code:

lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    Range("A" & lastRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

May I know how do I incorporate this copy function into you earlier code?
As you are using "x = 2 to 6" and the row in Worksheet Compile does not tally with this.

Thanks in advance.

Regards,
ybh

rollis13 · May 11, 2022

As per your example "x = 2 to 6" is a fixed range of rows in source sheet.

Code:

Sub rollis13()
    Dim x      As Long
    Dim lastrow As Long
    lastrow = Sheets("Compile").Cells(Rows.Count, "A").End(xlUp).Row + 1 'destination first row
    For x = 2 To 6                                'source rows
        If Range("B" & x).Value > 0 Then
            Range("C" & x).Copy
            Sheets("Compile").Range("A" & lastrow).PasteSpecial Paste:=xlPasteValues
            lastrow = lastrow + 1
        End If
    Next x
    Application.CutCopyMode = False
End Sub

ybh · May 11, 2022

rollis13 said:

As per your example "x = 2 to 6" is a fixed range of rows in source sheet.

Code:

Sub rollis13()
    Dim x      As Long
    Dim lastrow As Long
    lastrow = Sheets("Compile").Cells(Rows.Count, "A").End(xlUp).Row + 1 'destination first row
    For x = 2 To 6                                'source rows
        If Range("B" & x).Value > 0 Then
            Range("C" & x).Copy
            Sheets("Compile").Range("A" & lastrow).PasteSpecial Paste:=xlPasteValues
            lastrow = lastrow + 1
        End If
    Next x
    Application.CutCopyMode = False
End Sub

Thank you rollis13.
Another step forward, works perfect.

One last question pertaining the Worksheet_Calculate code you share:

>>> use code - tags <<<

Code:

Private Sub Worksheet_Calculate()
Dim rng    As Range
Set rng = Range("H2")
If Not Intersect(rng, rng) Is Nothing Then
If rng.Value > 0 Then Call rollis13
End If
End Sub

I want to add another feature to my project worksheet.
I am just wondering if something can be programmed so that any changes in Column L will trigger the macro?
As previously mentioned, the cells within Column L are formula linked.
I can see that the code you proposed above tied strictly to only Cell H2.
Worksheet_Change won't work for formula linked cell.

As usual, I updated the sample file with your latest edition.
Thanks.

Regards,
ybh

rollis13 · May 11, 2022

As mentioned in post #9 I have no other interest in following this thread because now this is a completely different topic. I invite you to start a new thread with a title related to this new topic.

ybh · May 12, 2022

rollis13 said:
As mentioned in post #9 I have no other interest in following this thread because now this is a completely different topic. I invite you to start a new thread with a title related to this new topic.

Thank you very much for your help.
Will do so in another thread if needed.

Continue to Run Next Macro After "IF" Condition is Met

ybh

New Member

Attachments

rollis13

Member

Marc L

Excel Ninja

ybh

New Member

Attachments

ybh

New Member

Marc L

Excel Ninja

rollis13

Member

ybh

New Member

Attachments

rollis13

Member

ybh

New Member

Attachments

rollis13

Member

ybh

New Member

Attachments

rollis13

Member

ybh

New Member