Sub UpdateFiles()
' Looking to match criteria of four culumns, which defines a unique record, and then update that record
' Tape file variables
Dim updFN As Variant ' If this is the worksheet name, is there a way to get it from the file?
Dim numRows As Long ' Is there a way to grab this from the file?
Dim Bmatch As Variant
Dim Cmatch As Variant
Dim Dmatch As Variant
Dim Ematch As Variant
Dim curRow As Long
Dim tapFile As Variant
' Catalog file variables
Dim fndRow As Long ' I am unable to find a way to retrieve this value; the row number of a found match
Dim matchRow As Long ' I am unable to find a way to retrieve this value; the row number that matches all criteria
Dim Bfind As Variant
Dim Cfind As Variant
Dim Dfind As Variant
Dim Efind As Variant
Dim catFile As Variant
' Open catalog file and switch back to tape file
catFile = Application.GetOpenFilename
Workbooks.Open Filename:=catFile
ActiveWindow.ActivatePrevious
' Get updFN
updFN = Application.InputBox(prompt:="What is the filename to use to update?", Title:="Update Filename")
' MsgBox ("The name of the file to use to update is ") & updFN
' Get numRows
numRows = Application.InputBox(prompt:="How many rows are in " & updFN & "?", Title:="Update File Rows")
' MsgBox ("There are " & numRows & " rows in " & updFN)
Application.ScreenUpdating = False
' Get Bmatch . . . Ematch
For curRow = 1 To numRows
Bmatch = Cells(curRow, 2).Value
Cmatch = Cells(curRow, 3).Value
Dmatch = Cells(curRow, 4).Value
Ematch = Cells(curRow, 5).Value
' MsgBox (Bmatch)
' Switch back to catalog
ActiveWindow.ActivatePrevious
' Go to top of B and Search for Bmatch
Cells(2, 1).Activate
Columns("B:B").Select
Selection.Find(What:=Bmatch, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
' This is where I would use fndRow; Cfind = Cells(fndRow, 3).Value
ActiveCell.Offset(0, 1).Range("A1").Select
Cfind = ActiveCell.Value
' MsgBox (Cmatch)
ActiveCell.Offset(0, 1).Range("A1").Select
Dfind = ActiveCell.Value
' MsgBox (Dmatch)
ActiveCell.Offset(0, 1).Range("A1").Select
Efind = ActiveCell.Value
' MsgBox (Ematch)
' Test for matches
If Cmatch <> Cfind Then Cells.FindNext(After:=ActiveCell).Activate ' It seems to hang up unless this command is repeated
Cells.FindNext(After:=ActiveCell).Activate
If Dmatch <> Dfind Then Cells.FindNext(After:=ActiveCell).Activate ' It seems to hang up unless this command is repeated
Cells.FindNext(After:=ActiveCell).Activate
If Ematch <> Efind Then Cells.FindNext(After:=ActiveCell).Activate
If Cmatch = Cfind And Dmatch = Dfind And Ematch = Efind Then ActiveCell.Offset(0, 10).Range("A1").Select ' This is where I would use matchRow;
' Cells(matchRow, 12).Value = Cells(matchRow, 12).Value & " " & updFN
ActiveCell.Offset(0, 10).Range("A1").Select ' For first loop, this must be repeated, but it ruins later loops
' Add update value to L cell
ActiveCell.Value = ActiveCell.Value & " " & updFN
' Switch back to tape file
ActiveWindow.ActivatePrevious
Next curRow
Application.ScreenUpdating = True
End Sub