Index Match, find and replace or add row in VBA

[abrcrmdl23]

Hello All,

I am new here and fairly new to VBA in general. I have been able to piece together part of what I need but I am struggling to finish the code.

In a nutshell I need the macro (Module 4 - AddtoFOPMaster) to do an index match from Sheet 1 cell C6 to Column A in Table1 in sheet Master FOP List. Then if it finds a match replace the data in the matched row found with the range data below (yes the code below will need tweaked to do this because that code adds a new row, but it has the correct data references for the table ranges)

Sub AddtoFOPMaster()

Dim ws As Worksheet
Set ws = Sheets("Master FOP List")
Dim tbl As ListObject
Set tbl = ws.ListObjects("FOP")
Dim newrow As ListRow
Set newrow = tbl.ListRows.Add
With newrow
.Range(1) = Worksheets("Sheet1").Range("C6")
.Range(2) = Worksheets("Sheet1").Range("D6")
.Range(3) = Worksheets("Sheet1").Range("C10")
.Range(4) = Worksheets("Sheet1").Range("B20")
End With

End Sub

If there is no match then I just need it to run the above code which adds a row and data to Table 1. I am struggling with the transition to do either or and I am terrible at index matching.

Any help would be much appreciated. I added a sample worksheet if you wanted to see it.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub AddtoFOPMaster()

   Dim tbl As ListObject
   Dim newrow As ListRow
   Dim Fnd As Range
   Set tbl = Sheets("Master FOP List").ListObjects("FOP")
   With Sheets("Sheet1")
      Set Fnd = tbl.ListColumns(1).DataBodyRange.Find(.Range("C6").Value, , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         Set newrow = tbl.ListRows.Add
         With newrow
            .Range(1) = Worksheets("Sheet1").Range("C6")
            .Range(2) = Worksheets("Sheet1").Range("D6")
            .Range(3) = Worksheets("Sheet1").Range("C10")
            .Range(4) = Worksheets("Sheet1").Range("B20")
         End With
      Else
         Fnd.Offset(, 1) = .Range("D6").Value
         Fnd.Offset(, 2).Value = .Range("C10").Value
         Fnd.Offset(, 3).Value = .Range("B20").Value
      End If
   End With
End Sub

 

[abrcrmdl23]

Hi & welcome to MrExcel.
How about

Sub AddtoFOPMaster()

   Dim tbl As ListObject
   Dim newrow As ListRow
   Dim Fnd As Range
   Set tbl = Sheets("Master FOP List").ListObjects("FOP")
   With Sheets("Sheet1")
      Set Fnd = tbl.ListColumns(1).DataBodyRange.Find(.Range("C6").Value, , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         Set newrow = tbl.ListRows.Add
         With newrow
            .Range(1) = Worksheets("Sheet1").Range("C6")
            .Range(2) = Worksheets("Sheet1").Range("D6")
            .Range(3) = Worksheets("Sheet1").Range("C10")
            .Range(4) = Worksheets("Sheet1").Range("B20")
         End With
      Else
         Fnd.Offset(, 1) = .Range("D6").Value
         Fnd.Offset(, 2).Value = .Range("C10").Value
         Fnd.Offset(, 3).Value = .Range("B20").Value
      End If
   End With
End Sub

You are a life saver!!!! That worked perfectly. Thank you so much for the help.

 

[Fluff]

You're welcome & thanks for the feedback.