Excel Macro Repeating IF and Else

Question

I am currently working on an Excel VBA macro script where it will run a simple TRUE or False test on an active cell. My problem: I cannot get this to work until the end of the list. It starts only once and ends the program. I need this VB script to run the IF and ELSE test to the bottom of the list.

Description of the problem:

Let's say I have a list of dates from A1 to A9999 and next to it (F1: F9999) there is also a list with text on it. list F1: F9999 contains only two values. (a) SUCH DATE; and (b) NOT SAME.

• Perform a True or False check on the F1: F9999 list.

• If the value of the active cell is equal to the text "SAME DATE" (TRUE), it will ignore and go to the next element in the list, and then repeat the number 1.

• If the value of the active cell is equal to the text "SAME DATE" (FALSE), it will insert a row above it, then move on to the next element in the list, and then repeat the number 1
• The TRUE or FALSE test will run to the end of the list.
• The TRUE or FALSE test will stop working if it reaches the bottom of the list.
• By the way, the number of elements in the list is incompatible. I just put F1: F9999 there, for example, for purposes.

here is my code!

Sub IFandElseTest() If ActiveCell.Value = "Same Date" Then Range(Selection, Cells(ActiveCell.Row, 1)).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ActiveCell.Offset(1, 0).Select Else: ActiveCell.Offset(1, 0).Select Range(Selection, Cells(ActiveCell.Row, 1)).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End If End Sub 

Stick if you could help me with this.

Answer 1

Give it a try.

Explanation:

• You should avoid using .Select/ActiveCell , etc. You might want to see this link
• When working with the last line, it is better not to hard code the values, but to dynamically find the last line. You might want to see this link
• Working with objects if the current sheet is not a sheet that you want to work with?
• The next FOR loop will cross the line from the bottom and move up.

The code:

 Sub Sample() Dim ws As Worksheet Dim LRow As Long, i As Long Dim insertRange As Range '~~> Chnage this to the relevant sheet Set ws = ThisWorkbook.Sheets("Sheet1") '~~> Work with the relevant sheet With ws '~~> Get the last row of the desired column LRow = .Range("E" & .Rows.Count).End(xlUp).Row '~~> Loop from last row up For i = LRow To 1 Step -1 '~~> Check for the condition '~~> UCASE changes to Upper case '~~> TRIM removes unwanted space from before and after If UCase(Trim(.Range("E" & i).Value)) = "SAME DATE" Then '~~> Insert the rows .Rows(i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End If Next i End With End Sub 

Screenshot:

Subsequent observation of comments

It really worked! BUT, one final modification. in your code: Set ws = ThisWorkbook.Sheets ("Sheet1") Is it possible that you can set WS as the active sheet. The reason for this is because the worksheet name is unique and incompatible as well.

As I mentioned, in the first link above, as well as in the comment, do not use Activesheet . Use CodeNames sheet that does not change. See screenshot below.

Blah Blah is the name of the sheet that you see on the worksheet tab, but Sheet1 is the CodeName that will not change. those. you can change the sheet name from Blah Blah to say Kareen , but in the VBA editor you will notice that CodeName does not change :)

Change code

 Set ws = ThisWorkbook.Sheets("Sheet1") 

to

 '~~> Replace Sheet1 with the relevant Code Name Set ws = [Sheet1] 

Answer 2

Edit:

If you do not use the r.copy string, it does more or less what the Siddharth Rout solution does

 Sub insrow() Dim v, r As Range Set r = [d1:e1] v = r.Columns(1).Value Do ' r.copy If v = "Same Date" Then r.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Set r = r.Offset(1) v = r.Columns(1).Value Loop Until v = "" End Sub 

This does not yet include the final condition if the line exceeds line 9999, but it should be easy to add ...