Results 1 to 5 of 5

userform update table based on 3 criteria

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    userform update table based on 3 criteria

    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!
    Last edited by adamheon; 12-29-2016 at 04:43 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Userform to Update Table Values based on a Listbox Selection
    By scottkelley80 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-27-2016, 08:49 PM
  2. [SOLVED] Userform to Update Table
    By adamheon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-17-2016, 12:15 PM
  3. update table based on the selection in userform
    By melody10 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-15-2015, 11:25 AM
  4. Trying to update an access table from Excel based on unique ID. rst.Update not working
    By Newbie0924 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2015, 04:15 PM
  5. Update excel based on userform text box update
    By sivakumar123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2013, 01:01 AM
  6. Update table from a userform with controls
    By mohanvp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-26-2011, 03:56 AM
  7. Macro code to update Pivot table based on criteria
    By wish2excel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-30-2010, 05:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1