• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

ybh

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

78850
 

Attachments

rollis13

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

Excel Ninja
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

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

Attachments

Last edited by a moderator:

ybh

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

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

rollis13

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

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

78855


In my spreadsheet, it looks like this:

  1. If I manually change the value in Cell H1, no issue it will call the macro promptly
  2. 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.
  3. 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.
78856

Any idea or suggestion?
Thanks in advance.

Regards,
ybh
 

Attachments

rollis13

New Member
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.
 
Last edited:

ybh

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

Attachments

Last edited by a moderator:

rollis13

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

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

Attachments

Last edited by a moderator:

rollis13

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

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