Search for a specific value in a row, then add more data to columns in that same row?

[phatphil3c]

Hello!

Very new to this so I'm sorry in advance! I was able to create a vba form with text fields, dropdown lists, command buttons and radio buttons (for maintenance requests). I am able to successfully transfer this data to another spreadsheet with date and time stamps and also generate jobs numbers (searches for the largest number in column A as the info gets sorted, then adds the next consecutive number). This number is then used to generate the job number in column B (example RMR-0009).

The code below is what is used on my 1st tab. However, there are 5 tabs total (all vba forms: maint request, work done by maint, quality dept verification, etc) that transfer information to 1 spreadsheet. I use similar code on the other tabs to add new line of data to the spreadsheet (all I've been able to figure out). However, no one at the job including me wants to read several rows of info just for 1 job number.

Can anyone advise how to search for the desired job number in column B, use that information to identify the row it came from, and then add more data down the line on that specific row? You can see below for this maint request that columns N through AB will be recorded on the spreadsheet as N/A (which is necessary documentation in my business, can't just leave them blank). I would like to replace those N/A's with the updated information. Each vba form has a dropdown list to choose the job number (called cmbjobnumber). Whatever job number the operator chooses is the value that needs to be identified in column B. Again sorry I am new and hope I provided good enough info (and also not too much). Thanks for any help that can be provided!!

Private Sub cmdSaveClose_Click()

Application.ScreenUpdating = False

Dim irow As Long
Dim Rng As Range
Dim ws As Worksheet
Set ws = Worksheets("Maint Request Data")

irow = Sheets("Maint Request Data").Range("A1048576").End(xlUp).Row + 1


If ValidateForm = True Then

With ThisWorkbook.Sheets("Maint Request Data")

.Range("A" & irow).Value = Application.WorksheetFunction.Large(ws.Range("A1:A1048576"), 1) + 1
.Range("B" & irow).Value = "RMR-" & Format(.Range("A" & irow).Value, "0000")
.Range("C" & irow).Value = txtReportedBy.Value
.Range("D" & irow).Value = Format(Now(), "MM/DD/YYYY")
.Range("E" & irow).Value = Format(Now(), "Long Time")
.Range("F" & irow).Value = cmbEquipmentLocation.Text
If optMoldingEquipment.Value Then .Range("G" & irow).Value = "Molding Equipment"
If optLeakFlowTester.Value Then .Range("G" & irow).Value = "Leak/Flow Tester"
If optAllOtherEquipment.Value Then .Range("G" & irow).Value = "All Other Equipment"
.Range("H" & irow).Value = txtEquipmentID.Value
If txtLoadBarID.Value = "" Then .Range("I" & irow).Value = "N/A" Else .Range("I" & irow).Value = txtLoadBarID.Value
If txtCavityID.Value = "" Then .Range("J" & irow).Value = "N/A" Else .Range("J" & irow).Value = txtCavityID.Value
.Range("K" & irow).Value = IIf(optEquipmentStatusYes.Value = True, "In-Use", "Not In-Use")
If txtPartNumber.Value = "" Then .Range("L" & irow).Value = "N/A" Else .Range("L" & irow).Value = txtPartNumber.Value
If txtLotNumber.Value = "" Then .Range("M" & irow).Value = "N/A" Else .Range("M" & irow).Value = txtLotNumber.Value
.Range("N" & irow).Value = txtProblemDescription.Value
.Range("O" & irow).Value = "N/A"
.Range("P" & irow).Value = "N/A"
.Range("Q" & irow).Value = "N/A"
.Range("R" & irow).Value = "N/A"
.Range("S" & irow).Value = "N/A"
.Range("T" & irow).Value = "N/A"
.Range("U" & irow).Value = "N/A"
.Range("V" & irow).Value = "N/A"
.Range("W" & irow).Value = "N/A"
.Range("X" & irow).Value = "N/A"
.Range("Y" & irow).Value = "N/A"
.Range("Z" & irow).Value = "N/A"
.Range("AA" & irow).Value = "N/A"
.Range("AB" & irow).Value = "N/A"

End With
Call Reset
Else
Application.ScreenUpdating = True
Exit Sub
End If

Application.ScreenUpdating = True

End Sub

 

[dmt32]

Hi welcome to forum

Untested & a bit of a guess but see if update to your code will help you

Private Sub cmdSaveClose_Click()
    Dim JobNumber        As Variant, m As Variant
    Dim msg                    As String
    Dim irow                    As Long
    Dim ws                      As Worksheet
    Dim NewRecord       As Boolean
  
    Set ws = ThisWorkbook.Worksheets("Maint Request Data")
  
    JobNumber = Me.cmbJobNumber.Value
  
    If ValidateForm = True Then
        Application.ScreenUpdating = False
      
        With ws
            irow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
          
            If JobNumber Like "RMR*" Then
                'check column B for existing record
                m = Application.Match(JobNumber, ws.Columns(2), 0)
                'match found - update record
                If Not IsError(m) Then irow = CLng(m) Else NewRecord = True
            End If
          
            If NewRecord Then
                .Range("A" & irow).Value = Application.WorksheetFunction.Large(.Columns(1), 1) + 1
                JobNumber = "RMR-" & Format(.Range("A" & irow).Value, "0000")
                .Range("B" & irow).Value = JobNumber
                .Range("C" & irow).Value = txtReportedBy.Value
                .Range("D" & irow).Value = Format(Now(), "MM/DD/YYYY")
                .Range("E" & irow).Value = Format(Now(), "Long Time")
                .Range("F" & irow).Value = cmbEquipmentLocation.Text
          
                .Range("G" & irow).Value = IIf(optMoldingEquipment.Value, "Molding Equipment", _
                                           IIf(optLeakFlowTester.Value, "Leak/Flow Tester", "All Other Equipment"))
              
                .Range("H" & irow).Value = txtEquipmentID.Value
  
                .Range("I" & irow).Value = IIf(txtLoadBarID.Value = "", "N/A", txtLoadBarID.Value)
                .Range("J" & irow).Value = IIf(txtCavityID.Value = """N/A", txtCavityID.Value)
                .Range("K" & irow).Value = IIf(optEquipmentStatusYes.Value = True, "In-Use", "Not In-Use")
                .Range("L" & irow).Value = IIf(txtPartNumber.Value = "", "N/A", txtPartNumber.Value)
                .Range("M" & irow).Value = IIf(txtLotNumber.Value = "", "N/A", txtLotNumber.Value)
                .Range("N" & irow).Value = txtProblemDescription.Value
            End If
          
            '***************************************************************************************************************************************
            'To Update Record You need to inculde option to replace "N/A" values with control objects values
          
            'example
          
            .Range("O" & irow).Value = IIf(NewRecord, "N/A", Me.TextBox1.Value)
          
              '**************************************************************************************************************************************
          
            .Range("P" & irow).Value = "N/A"
            .Range("Q" & irow).Value = "N/A"
            .Range("R" & irow).Value = "N/A"
            .Range("S" & irow).Value = "N/A"
            .Range("T" & irow).Value = "N/A"
            .Range("U" & irow).Value = "N/A"
            .Range("V" & irow).Value = "N/A"
            .Range("W" & irow).Value = "N/A"
            .Range("X" & irow).Value = "N/A"
            .Range("Y" & irow).Value = "N/A"
            .Range("Z" & irow).Value = "N/A"
            .Range("AA" & irow).Value = "N/A"
            .Range("AB" & irow).Value = "N/A"
          
        End With
      
        Call Reset
      
    End If
  
    NewRecord = False
  
    Application.ScreenUpdating = True
    msg = IIf(NewRecord, "New Record Added", "Record Updated")
    MsgBox JobNumber & Chr(10) & msg, 64, msg
  
End Sub

You will need to adjust for specific project need but hopefully, update goes in the right direction for you

Dave

 

[phatphil3c]

Thank you Dave! I couldn't get back to trying this until today. Even not knowing what I was doing, I was able to incorporate this code successfully and only had to make slight adjustments for the project.

 

[dmt32]

Thank you Dave! I couldn't get back to trying this until today. Even not knowing what I was doing, I was able to incorporate this code successfully and only had to make slight adjustments for the project.

most welcome & appreciate your feedback

Dave