Create a vba code to insert row based on two-column condition

[Ganeshan]

Hi Everyone,
I am new to this forum and have been trying to learn some VBA coding to automate some data. My question is -

I would like to insert a row when there is a value change in two-column cells in an excel
For example, columns F and G where F has a string value of "Buy" and "Sell" and column G has string "D" and "S".

My code should add a row when the column is sorted the last value is
"Buy" - "D"
"Buy" - "S"
"Sell" - "D"
"Sell" - "S"

 

[tjdickinson]

Try this where rows are only shifted down at the end of the process (if there are any) and no helper column is used:

Option Explicit
Sub Macro1()

    Dim TimeArray As Variant
    Dim i As Long
    Dim TimeCheck As String
    Dim rng As Range
  
    Application.ScreenUpdating = False
  
    TimeArray = Array("8:00", "8:20", "9:10", "10:00", "10:20", "11:10", "12:05", "12:30", "13:00", "13:50", "14:40", "15:00", "15:50")
  
    For i = 4 To 15
        If Len(Cells(i, "A")) > 0 And InStr(Cells(i, "A"), Chr(10)) > 0 Then
            TimeCheck = Split(Cells(i, "A"), Chr(10))(0)
            If IsNumeric(Application.Match(TimeCheck, TimeArray, 0)) = False Then
                If rng Is Nothing Then
                    Set rng = Cells(i, "A")
                Else
                    Set rng = Union(rng, Cells(i, "A"))
                End If
            End If
        End If
    Next i
  
    If Not rng Is Nothing Then
        rng.EntireRow.Delete Shift:=xlDown
    End If
  
    Application.ScreenUpdating = False

End Sub

Thanks, Trebor! I gave it a try, but nothing happened. (I removed the ScreenUpdating function, too, so that's not the problem.) I ran it with the rest of the code (for the other formatting things that happen before it), and I ran it on its own, and it gave the same result. (The other formatting things did happen, but the insert rows did not happen.)

I tried also changing the rng.EntireRow element to .Insert (instead of .Delete), but that didn't do anything either.

I'm not familiar (yet) with the IsNumeric command, but I suppose from looking at it that it's checking to see if the variables are ...well...numeric. Would the fact that the elements in the array are strings (with a : in them) be interfering with this?

I'm also wondering if having the "8:00" element in the array is problematic. The times start in A3 with 8:00 (sometimes shown as 8:05), but it's irrelevant because no row ever needs to be inserted before A3. Thus it can start checking in A4 with the time 8:20.

I just realised as well that I never posted the data from my sheet. Of course, it's slightly different for each person's timetable, so I'll just give two examples. But hopefully this will help visualise what's going on in the sheet itself.

First, here's a "full" timetable (when no rows need to be inserted):

21-22 January_3112_6.4.3.3001_Leerkrachten TEST.xlsx
	A	B	C	D	E	F
3	8:00 8:20
4	8:20 9:10				FRA 5 HUM 5 LAT-MT 6 LAT-MT	MEETING MIS
5	9:10 10:00			FRA 3 HUM 3 LAT
6	10:00 10:20			TOEZ. pauze
7	10:20 11:10			FRA 3 HUM 3 LAT	FRA 3 HUM 3 LAT
8	11:10 12:00
9	12:05 12:30	MEETING Lunch
10	12:30 13:00
11	13:00 13:50			FRA 4 HUM 4 LAT	FRA 4 HUM 4 LAT
12	13:50 14:40
13	14:40 15:00				TOEZ. pauze
14	15:00 15:50			FRA 5 HUM 5 LAT-MT 6 LAT-MT
15	15:50 16:40
BTH

And here's one that has missing rows. In this example, a row would need to be inserted before rows 6, 10, and 12. (This one also gives an example of 8:05 in A3, but again, it's irrelevant.)

21-22 January_3112_6.4.3.3001_Leerkrachten TEST.xlsx
	A	B	C	D	E	F
3	8:05 8:20		TOEZ. pauze		TOEZ. pauze
4	8:20 9:10		AARD 3 HUM 3 LAT		FYS 4 HUM 4 LAT	MEETING MIS
5	9:10 10:20
6	10:20 11:10		AARD 4 HUM 4 LAT
7	11:10 12:00				AARD 6 LAT-MT
8	12:05 12:30	MEETING Lunch
9	12:30 13:50
10	13:50 14:40			CHM 3 HUM 3 LAT	AARD 5 HUM 5 LAT-MT
11	14:40 15:50
12	15:50 16:40		FYS 3 HUM 3 LAT	CHM 4 HUM 4 LAT
DHE

Thanks again! I really appreciate your help!

 

[Trebor76]

Try this where the entry in Col. A is matched to the array position:

Option Explicit
Sub Macro1()

    Dim strTimes() As String
    Dim i As Long, j As Long
    Dim TimeCheck As String
    Dim rng As Range
  
    Application.ScreenUpdating = False
  
    strTimes = Split("8:00,8:20,9:10,10:00,10:20,11:10,12:05,12:30,13:00,13:50,14:40,15:00,15:50", ",") 'Array should just probably start at 8:20??
    j = 1 'This would be zero if we were checking from the first item in the 'strTimes' array
  
    For i = 3 To 15 'Should just start at Row 4??
        If i > 3 And Len(Cells(i, "A")) > 0 Then
            If Left(Cells(i, "A"), 1) = "1" Or Left(Cells(i, "A"), 1) = "2" Then
                TimeCheck = Left(Cells(i, "A"), 5)
            Else
                TimeCheck = Left(Cells(i, "A"), 4)
            End If
            If TimeCheck <> strTimes(j) Then
                If rng Is Nothing Then
                    Set rng = Cells(i, "A")
                Else
                    Set rng = Union(rng, Cells(i, "A"))
                End If
                i = i - 1
            End If
            j = j + 1
        End If
    Next i
  
    If Not rng Is Nothing Then
        rng.EntireRow.Insert Shift:=xlDown
    End If
  
    Application.ScreenUpdating = True

End Sub

The code also inserted a Row before 12 as there is no 15:00 time stamp.

If this is still wrong please start a new thread with a link to this one if you think it will help provide a solution.

Thanks,

Robert

 

[tjdickinson]

Try this where the entry in Col. A is matched to the array position:

Option Explicit
Sub Macro1()

    Dim strTimes() As String
    Dim i As Long, j As Long
    Dim TimeCheck As String
    Dim rng As Range
 
    Application.ScreenUpdating = False
 
    strTimes = Split("8:00,8:20,9:10,10:00,10:20,11:10,12:05,12:30,13:00,13:50,14:40,15:00,15:50", ",") 'Array should just probably start at 8:20??
    j = 1 'This would be zero if we were checking from the first item in the 'strTimes' array
 
    For i = 3 To 15 'Should just start at Row 4??
        If i > 3 And Len(Cells(i, "A")) > 0 Then
            If Left(Cells(i, "A"), 1) = "1" Or Left(Cells(i, "A"), 1) = "2" Then
                TimeCheck = Left(Cells(i, "A"), 5)
            Else
                TimeCheck = Left(Cells(i, "A"), 4)
            End If
            If TimeCheck <> strTimes(j) Then
                If rng Is Nothing Then
                    Set rng = Cells(i, "A")
                Else
                    Set rng = Union(rng, Cells(i, "A"))
                End If
                i = i - 1
            End If
            j = j + 1
        End If
    Next i
 
    If Not rng Is Nothing Then
        rng.EntireRow.Insert Shift:=xlDown
    End If
 
    Application.ScreenUpdating = True

End Sub

The code also inserted a Row before 12 as there is no 15:00 time stamp.

If this is still wrong please start a new thread with a link to this one if you think it will help provide a solution.

Thanks,

Robert

Thanks, Robert! It's very close--it works fine generally, but it doesn't insert two rows next to each other when needed. I started a new thread here, as you suggested.