I use the following code to look up a value based on 3 fields (job number, month, year)
Private Sub searchbilling_Click()
Dim ws As Worksheet
Dim JoBRow As Range
Dim UpdateRow As Long
Set ws = Sheets("PROJECTS")
Set JoBRow = ws.Range("C:C").Find(What:=JobSearch, LookIn:=xlValues, lookat:=xlWhole)
If JoBRow Is Nothing Then
MsgBox "CANNOT FIND JOB NUMBER"
Exit Sub
Else
UpdateRow = JoBRow.Row
End If
jobname = ws.Cells(UpdateRow, 4)
billingnotes = ws.Cells(UpdateRow, 32)
sn = Sheets("BILLING LOG").Cells(1).CurrentRegion.Offset(1).Value
With ListBox1
.List = sn
For i = .ListCount - 1 To 0 Step -1
If .List(i, 6) <> year.Text Then .RemoveItem i
Next
For i = .ListCount - 1 To 0 Step -1
If .List(i, 8) <> cmbMonth Then .RemoveItem i
Next
For i = .ListCount - 1 To 0 Step -1
If .List(i, 0) <> JobSearch Then .RemoveItem i
Next
If .ListCount > 0 Then
billingamount = Format(.List(0, 2), "$#,##0.00")
Else
MsgBox "CANNOT FIND ANY RECORD FOR CURRENT CRITERIA"
End If
End With
End Sub
this will pull up the $value that matches that criteria. I would like to then be able to modify that value and UPDATE the value in the table . So basically it has to perform the same function but then enter the value within the "billingamount" text box.
any help is appreciated. thanks!
Bookmarks