nidenikolev
New Member
- Joined
- Jun 6, 2018
- Messages
- 20
I'm trying to locate a row # with data I need to update based on two text boxes within a userform. Code is here:
in my data, store and candidate name are found in row 6, but the function keeps looping through and not matching to row 6. What am I missing here?
VBA Code:
Private Sub cmdSendData_Click()
Set wb = Workbooks.Open("\\TABLE.xlsx")
Dim wsTgt As Worksheet: Set wsTgt = wb.Worksheets("Sheet1")
Dim recRow As Range
'See if there's a match on an existing row
' adjust function to suit...
Set recRow = MatchRow(wsTgt.Range("A1").CurrentRegion, _
txtStore.Text, _
txtCandidateName.Text)
'If there's no existing row to update then add a new row at the bottom
If recRow Is Nothing Then Set recRow = wsTgt.Range("A50000").End(xlUp).Offset(1, 0)
With recRow.EntireRow
.Cells(1).Value = Me.txtTodays_Date.Text
'....
.Cells(33).Value = Me.txtMgrJustification.Text
End With
'wb.Close savechanges:=True
'wb.Saved = True
End Sub
'Return a row from a table based on matches in two columns
' returns nothing if no match
Function MatchRow(tableRange As Range, match1, match2) As Range
Dim rw As Range
For Each rw In tableRange.Rows
'adjust the column numbers/match types as needed
If rw.Cells(4).Value = match1 Then
If rw.Cells(17).Value = match2 Then
Set MatchRow = rw
Exit Function
End If
End If
Next rw
End Function
in my data, store and candidate name are found in row 6, but the function keeps looping through and not matching to row 6. What am I missing here?