VLookup and HLookup using VBA to enter data into a worksheet based on fields from a userform

[Russmeister57]

Hi all,

I've been racking my brains for some time now and i cannot figure this one out.
I have a complexed spreadsheet using numerous macros and advanced filters and i have begun to move this into userform.

Basically, as the header states, i have a userform(Review Status) with a textbox and 2 comboboxes (Textbox1, Combobox1 & 2). I want to select data in the textbox and the comboboxes.
Textbox1 = Name
ComboBox1= Week Number
ComboBox2 = Review Status "Complete","Due"
When i click an update button, vba will search a specific sheet in my workbook(Review Status)for the name in Vlookup, and then the week number in HLookup and then enter in the corresponding cell the review status.

I'm sorry i cannot post any data of it into here as its currently a live workbook that i have built that i use for my business.

Can this be done?
Any help would be greatly appreciated.

 

[fadee2]

Assuming your controls are TextBox1 (Name), Combobox1 (Week) and Combobox2 (Status) and your destination worksheet is designed as below

try binding below code to your update button:

Set get_row = Sheets("sheet1").Range("a:a")
Set get_col = Sheets("sheet1").Range("b2:m2")

fnd_row = get_row.Find(TextBox1.Text).Row
fnd_col = get_col.Find(ComboBox1.Value).Column

Cells(fnd_row, fnd_col) = ComboBox2.Value

The code above will look for textbox1 value in column A and keeps a reference of the row number in fnd_row variable. Then it will look for column number corresponding to week number in Combobox1 and will pass the information to fnd_col variable.
Cells(fnd_row,fnd_col) will place the information from ComboBox2 to your desired sheets cell address being provided by two variables.

Hope this Helps.

 

[Larry Haydn]

From a programming point of view, I will approach it differently.
Instead of using vLookup and hLookup, I will use loops to accomplish this.
Assuming your worksheet ("Review Status") setup is like this:

I will create a column reference list in another worksheet ("List"), Like this:

And your form looks like this:

You can link the command button "UPDATE" to a module "modUpdateStatus".
Here is the code:

Option Explicit
Option Compare Text

Sub modUpdateStatus()
'===========================================
'   Updates the Review Status
'   based on the Name, WeekNo, and Status
'===========================================
Dim WB              As Workbook
Dim wsRvwStat       As Worksheet
Dim wsList          As Worksheet

Dim txtUserName     As String
Dim txtWeekNo       As String
Dim txtStat         As String

Dim lngRowNo        As Long
Dim lngLasRow       As Long
Dim txtCol          As String

Dim intListRow      As Integer
Dim txtCol          As String

    '---------------------------------------------------
    '   I will skip the data validation part, such as
    '   * If name is blank, etc
    '---------------------------------------------------
    
    '=============================================================
    '   First, make sure you positively identify the worksheets
    '=============================================================
    Set WB = ActiveWorkbook
    Set wsRvwStat = WB.Sheets("Review Status")
    Set wsList = WB.Sheets("List")
    
    '===========================================================
    '   Now get the input from the textbox and comboboxes:
    '   * txtName
    '   * cboWeekNo
    '   * cboStat
    '
    '   The (Option Compare Text) on top of this module is
    '   very important here. If you do not set this option,
    '   then you might have trouble comparing names, in that
    '   John <> john
    '   By default, the system will use Option Compare Binary
    '   unless you override it with Option Compare Text
    '===========================================================
    txtUserName = Trim(Me.txtName)  'Removes leading and/or trailing spaces
    txtWeekNo = cboWeekNo
    txtStat = cboStat
    
    '====================================================================
    '   Now we are ready to find the row and column to update.
    '   First, find the last data row in thw worksheet "Review Status"
    '   This lngLasRow is used to determine when the loop should stop
    '====================================================================
    lngLasRow = wsRvwStat.Range("A1048576").End(xlUp).row
        '================================
        '   Now we search for the name
        '================================
        For lngRowNo = 2 To lngLasRow
            '============================
            '   Find the name in col A
            '============================
            If wsRvwStat.Range("A" & lngRowNo) = txtUserName Then
                '=======================================================
                '   OK, we found the name, now find the WeekNo column
                '   by searching the "List" worksheet
                '=======================================================
                intListRow = 2  'Start searching for the WeekNo in row 2
                
                Do While intListRow <> ""
                    '==============================
                    '   Find the WeekNo in Col A
                    '==============================
                    If wsList.Range("A" & intListRow) = txtWeekNo Then
                        '=====================================================================
                        '   OK, we found the week no, now get the column reference in Col B
                        '=====================================================================
                        txtCol = wsList.Range("B" & intListRow)
                            '================================================
                            '   Now we know which row and column to update,
                            '   let's update the status
                            '================================================
                            wsRvwStat.Range(lngRowNo, txtCol) = txtStat
                            '===========================
                            '   Mission accomplished!
                            '   New we exit the loop
                            '===========================
                            Exit For
                    End If
                    '==============
                    '   Next row
                    '==============
                    intListRow = intListRow + 1
                Loop
            End If
        Next

End Sub

 

[dmt32]

I'm sorry i cannot post any data of it into here as its currently a live workbook that i have built that i use for my business.

creating a copy of your worksheet with dummy data & posting it using MrExcel Addin XL2BB - Excel Range to BBCode is always helpful to forum as cuts out a lot of guess work

Assuming your names are in Column 1 & the week numbers are in Row 1 of your worksheet then another possible way - maybe

Private Sub CommandButton1_Click()
    Dim wsReviewStatus  As Worksheet
    Dim WeekNo          As Long, i As Long
    Dim m(1 To 2)       As Variant, Item As Variant
    Dim strStatus       As String, strName As String
    
    Set wsReviewStatus = ThisWorkbook.Worksheets("Review Status")
    
    strName = Me.TextBox1.Value
    WeekNo = Val(Me.ComboBox1.Value)
    strStatus = Me.ComboBox2.Value
    
    With wsReviewStatus
        For Each Item In Array(strName, WeekNo)
            i = i + 1
            m(i) = Application.Match(Item, IIf(i = 1, .Columns(1), .Rows(1)), 0)
            If IsError(m(i)) Then MsgBox Choose(i, "Name: ", "Week No: ") & Item & " Not Found", _
                                         48, "Not Found": Exit Sub
        Next
        .Cells(CLng(m(1)), CLng(m(2))).Value = strStatus
    End With
    
End Sub

Dave

 

[Russmeister57]

creating a copy of your worksheet with dummy data & posting it using MrExcel Addin XL2BB - Excel Range to BBCode is always helpful to forum as cuts out a lot of guess work

Assuming your names are in Column 1 & the week numbers are in Row 1 of your worksheet then another possible way - maybe

Private Sub CommandButton1_Click()
    Dim wsReviewStatus  As Worksheet
    Dim WeekNo          As Long, i As Long
    Dim m(1 To 2)       As Variant, Item As Variant
    Dim strStatus       As String, strName As String
   
    Set wsReviewStatus = ThisWorkbook.Worksheets("Review Status")
   
    strName = Me.TextBox1.Value
    WeekNo = Val(Me.ComboBox1.Value)
    strStatus = Me.ComboBox2.Value
   
    With wsReviewStatus
        For Each Item In Array(strName, WeekNo)
            i = i + 1
            m(i) = Application.Match(Item, IIf(i = 1, .Columns(1), .Rows(1)), 0)
            If IsError(m(i)) Then MsgBox Choose(i, "Name: ", "Week No: ") & Item & " Not Found", _
                                         48, "Not Found": Exit Sub
        Next
        .Cells(CLng(m(1)), CLng(m(2))).Value = strStatus
    End With
   
End Sub

Dave

This worked perfectly Dave, thank you.

Thank You all for your replies. Its greatly appreciated.

 

[smozgur]

This worked perfectly Dave, thank you.

The marked solution post has been changed accordingly.

@Russmeister57: In your future questions, that would be great if you could mark the post as solution that answered your question as it will help future readers. No further action is required for this thread.

 

[dmt32]

This worked perfectly Dave, thank you.

Thank You all for your replies. Its greatly appreciated.

welcome glad we were able to help & appreciate your feedback

Dave