Running an Excel macro with an IF statement?

Question

I have a large (2,500 row) sheet in which I need to insert a considerable number of blank rows, then copy the contents of the row above into this. I select the rows I need to perform this operation on using an IF statement which inserts the word 'both' into a cell if a condition is met. I then filter the column which contains this cell so I end up with a filtered list of between 2 and 300 rows. Currently I use a short macro:

Sub InsertCopyRow()
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
End Sub

to perform this operation, but I have to run the macro manually on each selected row, which is quite time consuming.

Is there a way of automating it so that the macro will run on every row where the target cell contains the word 'both', as in:

=IF(H1="both","run macro 'InsertCopyRow'","") ?

Answer 1

I don't think so. The IF function doesn't have a way to call a macro.

That being said, why not modify your macro to look for the "both" flag and do its thing when it finds one? I would suggest having it operate on the current selection or a specific column.

Hope that helps.

Answer 2

Yeah, you can easily do that with a For/Next loop. However your explanation for what you are trying to do is kind of vague, so I can't really give you help that will do a real good job of accomplishing what you want to do, but should help get you in that direction. Try something like this.

Dim x As Long
For x = 2500 To 1 Step -1

Next x

Like I said, your explanation is very vague I have no idea what column you put your if statement in, what the true goal is that you are trying to accomplish, etc.

Since you are inserting rows you really need to work from the bottom of the worksheet up. However, you are copying the entire row of the last row you were on so that could create some hangups as far as the word "both" and copying it and therefore inserting more rows than you want to.

Answer 3

What you can do is use the Vis. Basic If statement. In your case you will have to combine it with some kind of loop such that you start the loop, run (IF statement, condition, your three lines of macro, END IF), NEXT LOOP.

Here is what the macro should do:
The loop starts and looks for the IF condition (whatever you want to use for "both") . If the condition is met you run your 3 lines of macro,then END IF then start the loop again, if condition is not met it will END IF and loop again, etc.
Try getting into Visual Basic then do a help/find/conditionals for some IF examples.