Insert a row based on the cell value and fill the necessary fields from another sheet

Tharabai · Oct 3, 2015

I need to insert a row based on the cell value in column o. If the cell value begins with 2 or value does not contain alphabets then a row to be inserted and fill the values.
I have a sheet with the column A to Q. For the inserted rows the values should be same as prior row for the columns A,B,C,D,G,L and for others the value to be filled from the data in the another sheet.
For the column E, the value should be Aux if the prior value is "cont". And Colum F should be filled from the data from sheet4 based on the value from Q, E. For Ex, If the cell value in column Q is "KDH", column O is "201" and Column E is "Aux " then the value for that should be picked from Sheet4. Similarly for all rows.

Also, if the data in column J to be modified. If the cell value does not contain alphabet then cell value in column J should be "Replacing Contractor".

File uploaded and coloring done for easy reference.

vletm · Oct 3, 2015

Could You write sample how to insert new(/edit?) row?
Step by step, please.

Edcronos · Oct 3, 2015

I'm sorry,
with the translator, is not well understandable, the rule added to the line

edit:

Code:

Sub Macro1()
    FRow = 6
    With Sheets("Sheet3")

        URow = .Cells(Rows.Count, "E").End(xlUp).Row

        For r = URow To FRow Step -1
            If IsNumeric(Left(.Cells(r, "O").Value2, 1)) = True Then
                If .Cells(r, "E").Value2 = "Aux" Then
                    If .Cells(r, "M").Value2 = "" Then
                        .Rows(r + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                    End If
                End If
            End If
        Next
    End With
End Sub

Tharabai · Oct 3, 2015

Hi,

inserting a row should be based on the cell values in column Q and O.

If the cell value in column is non alphabet (if cell value is 201 3, 201 4 also) then the row should be inserted.

After insertion, if the column E value is "Contractor" then the value of inserted row in column E should be "Aux".

All other column F, H, I, K should be picked from sheet4 based on the column value of Q, O and E.

Hope I made it clear now !!!

Edcronos · Oct 3, 2015

well, I am struggling to understand
but the part that I think I understand

Code:

Sub Macro1()
    FRow = 6
    With Sheets("Sheet3")

        URow = .Cells(Rows.Count, "E").End(xlUp).Row

        For r = URow To FRow Step -1
        v = .Cells(r, "O").Value2
            If IsNumeric(Left(v, 1)) = True Or v = "201 3" Or v = "201 4" Then
           
                If .Cells(r, "E").Value2 = "Contractor" Then
                .Rows(r + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                .Cells(r + 1, "E").Value2 = "Aux"


                End If
            End If
        Next
    End With
End Sub

Edcronos · Oct 3, 2015

the table Sheet4 is fixed?
why is it easier to scan it and go on adding sheet3 the corresponding values

Edcronos · Oct 3, 2015

complicated
but something I could

EDIT:::::

Code:

Sub Macro1()

    With Sheets("Sheet3")

        For rw = .Cells(Rows.Count, "E").End(xlUp).Row To 6 Step -1    'a
            If .Cells(rw, "E").Value2 = "Contractor" Then    '-1
                v = .Cells(rw, "O").Value2

                For c = Cells(3, "E").Column To Cells(3, "K").Column    'b

                    If v = Sheets("Sheet4").Cells(3, c).Value2 Then    '1
                        vq = .Cells(rw, "Q").Value2

                        For r2 = 4 To 19    'c

                            If vq = Sheets("Sheet4").Cells(r2, "C").Value2 Then    '2

                                If UCase(Sheets("Sheet4").Cells(r2, "d").Value2) = UCase("Aux") Then    '3
                                    c2 = Cells(1, "e").Column
                                    .Rows(rw + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                                    .Cells(rw + 1, "e").Value2 = "Aux"
                                    r2a = r2

                                    For vf = 1 To 4    'd
                                        c2 = c2 + 1
                                        If c2 = 7 Or c2 = 10 Then c2 = c2 + 1
                                        .Cells(rw + 1, c2).Value2 = Sheets("Sheet4").Cells(r2a, c).Value2
                                        r2a = r2a + 1
                                    Next    'd

                                    GoTo proximo:
                                End If    '3
                            End If    '2

                        Next    'c


                    End If    '1
                Next    'b
            End If '-1
proximo:
        Next    'a
    End With
End Sub

EDIT:::::::

Tharabai · Oct 4, 2015

It works for the inserted lines..
1. But the values for the contractors should also be filled from Sheet4(columnd E, F, H, I, J). If the rows and column in sheet4 are dynamic will this work.

2. Only the columns E, F, H, I, K are filled. The other column values for the inserted rows should be same as the one for which the row is inserted. (previous row values).

Edcronos · Oct 4, 2015

For me it is difficult to know what goes where, with this information
even with a translator,
I do not even know if the table Sheet4 will have more lines or more columns, I put in a way that is easy to adjust
but their distribution data does not help the process
if this process is common, I opine for a change in the distribution of the data, the table, the Sheet4

Tharabai · Oct 4, 2015

If the data in sheet4 is constant (only that rows and columns) can we get the details of columns E, F, H, I, K with the cell value contractor in column E from sheet 4

Edcronos · Oct 4, 2015

Code:

Sub Macro1()

    With Sheets("Sheet3")

        For rw = .Cells(Rows.Count, "E").End(xlUp).Row To 6 Step -1    'a
            If .Cells(rw, "E").Value2 = "Contractor" Then    '-1
                v = .Cells(rw, "O").Value2
                For c = Cells(3, "E").Column To Cells(3, "K").Column    'b
                    If v = Sheets("Sheet4").Cells(3, c).Value2 Then    '1
                        vq = .Cells(rw, "Q").Value2
                        For r2 = 4 To 19    'c
                            If vq = Sheets("Sheet4").Cells(r2, "C").Value2 Then    '2
                                If UCase(Sheets("Sheet4").Cells(r2, "d").Value2) = UCase("Aux") Then    '3
                                    c2 = Cells(1, "e").Column
                                    .Rows(rw + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                                    .Range("a" & rw + 1, "q" & rw + 1).Value2 = .Range("a" & rw, "q" & rw).Value2
                                    .Cells(rw + 1, "e").Value2 = "Aux"
                                    r2a = r2
                                    For vf = 1 To 4    'd
                                        c2 = c2 + 1
                                        If c2 = 7 Or c2 = 10 Then c2 = c2 + 1
                                        .Cells(rw + 1, c2).Value2 = Sheets("Sheet4").Cells(r2a, c).Value2
                                        r2a = r2a + 1
                                    Next    'd
                                    GoTo proximo:
                                End If    '3
                            End If    '2
                        Next    'c
                    End If    '1
                Next    'b
            End If '-1
proximo:
        Next    'a
      
    End With
End Sub

I do not know if I understand

Tharabai · Oct 4, 2015

Partially I got the output, except for the rows with the cell value as "contractor" for which the columns F, H, I, K should be filled from Sheet4. This is same as "Aux" in which we are inserting the rows and filling the values where as for the contractor we already have rows in which only cell value to be filled.

I tried to modify the code to pick the values for contractor before inserting the rows, but its not picking the values for the cell value in alphabet (eg. ADD, Brake)

Please shed some details on the below coding as am trying to understand. Why we have used Vf = 1 to 4 and also the line
If c2 = 7 Or c2 = 10 Then c2 = c2 + 1

First I will run the below macro named "contractor" then I will run the macro for inserting and filling the rows.

Code:

Sub contractor()
  With Sheets("Sheet3")
  For rw = .Cells(Rows.Count, "E").End(xlUp).Row To 6 Step -1  'a
  If .Cells(rw, "E").Value2 = "Contractor" Then  '-1
  v = .Cells(rw, "O").Value2
  For c = Cells(3, "E").Column To Cells(3, "K").Column  'b
  If v = Sheets("Sheet4").Cells(3, c).Value2 Then  '1
  vq = .Cells(rw, "Q").Value2
  For r2 = 4 To 19  'c
  If vq = Sheets("Sheet4").Cells(r2, "C").Value2 Then  '2
  If UCase(Sheets("Sheet4").Cells(r2, "d").Value2) = UCase("CONTRACTOR") Then  '3
  c2 = Cells(1, "e").Column
  '.Rows(rw + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
  '.Cells(rw + 1, "e").Value2 = "Aux"
  r2a = r2
  For vf = 1 To 4  'd
  c2 = c2 + 1
  If c2 = 7 Or c2 = 10 Then c2 = c2 + 1
  .Cells(rw, c2).Value2 = Sheets("Sheet4").Cells(r2a, c).Value2
  r2a = r2a + 1
  Next  'd
  GoTo proximo:
  End If  '3
  End If  '2
  Next  'c

  End If  '1
  Next  'b
  End If '-1
proximo:
  Next  'a
  End With
End Sub

Edcronos · Oct 4, 2015

that's' cause your data structure is chaotic for such automation
so I suggested changing the format of the table Sheet4

'is to run the lines of Sheet4
For VF = 1 To 4

'jumps the columns of sheet1
c2 = c2 + 1

'jumps the columns that are contrary to the placement of data Sheet4 "7 and 10"
If c2 = 7 Or c2 = 10 Then c2 = c2 + 1

.Cells (Rw + 1, c2) = .Value2 Sheets ("Sheet4"). Cells (2a, c) .Value2
R2a = 2a + 1
Next 'd

Edcronos · Oct 4, 2015

to catch (eg. ADD, Brake)
You have to change the check loop columns

Code:

 For c = Cells(3, "E").Column To Cells(3, "K").Column    'b

For c = Cells(3, "E").Column To Cells(3, "M").Column 'b

this macro already adds lines

the macro was this mess that I can not understand the request, and the structure of the spreadsheet does not help

the macro worked or not?

I think no point in attending this forum, I can not understand anything anyway

Edcronos · Oct 4, 2015

Code:

Sub Macro1()

    With Sheets("Sheet3")

        For rw = .Cells(Rows.Count, "E").End(xlUp).Row To 6 Step -1    'a
            If .Cells(rw, "E").Value2 = "Contractor" Then    '-1
                v = .Cells(rw, "O").Value2
                For c = Cells(3, "E").Column To Cells(3, "m").Column    'b
                    If v = Sheets("Sheet4").Cells(3, c).Value2 Then    '1
                        vq = .Cells(rw, "Q").Value2
                        For r2 = 4 To 19    'c
                            If vq = Sheets("Sheet4").Cells(r2, "C").Value2 Then    '2
                            vq2 = UCase(Sheets("Sheet4").Cells(r2, "d").Value2)
                           
                                If vq2 = "AUX" Or vq2 = "CONTRACTOR" Then  '3
                                    c2 = Cells(1, "e").Column
                                    .Rows(rw + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                                    .Range("a" & rw + 1, "q" & rw + 1).Value2 = .Range("a" & rw, "q" & rw).Value2
                                    If c < 12 Then .Cells(rw + 1, "e").Value2 = "Aux"
                                    For r4 = r2 To r2 + 3  'd
                                        c2 = c2 + 1
                                        If c2 = 7 Or c2 = 10 Then c2 = c2 + 1
                                        .Cells(rw + 1, c2).Value2 = Sheets("Sheet4").Cells(r4, c).Value2
                                    Next    'd
                                    GoTo proximo:
                                End If    '3
                            End If    '2
                        Next    'c
                    End If    '1
                Next    'b
            End If '-1
proximo:
        Next    'a
       
    End With
End Sub

I continued without understanding, but sees it helps you

Edcronos · Oct 4, 2015

The macro is taking the data correctly ??
I have no idea if I'm on the right track

Tharabai · Oct 5, 2015

Yes.. you are on the right track.. it worked for me.. thank you

Insert a row based on the cell value and fill the necessary fields from another sheet

Tharabai

Member

Attachments

vletm

Excel Ninja

Edcronos

Member

Tharabai

Member

Edcronos

Member

Edcronos

Member

Edcronos

Member

Tharabai

Member

Edcronos

Member

Tharabai

Member

Edcronos

Member

Tharabai

Member

Edcronos

Member

Edcronos

Member

Edcronos

Member

Edcronos

Member

Tharabai

Member