Index Match, find and replace or add row in VBA

abrcrmdl23

New Member
Joined
Apr 16, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,585
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

New Member
Joined
Apr 16, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,585
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,619
Messages
5,659,903
Members
418,536
Latest member
Tezzies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top