UserForm VLOOKUP to get Data and Change one Column

[gino-aviatio]

Good afternoon Everyone,
I am currently working on a database interface based on excel ( i know it's ridiculous since a real database will never come out, but we don't get access available for that)
maybe to let you know I didn't study IT or anything close, I am kind of learning by doing since a couple month
So coming to the problem...

I got one Table (Database) which contains row by row all Data and a couple of Userforms dealing with the Data by showing it and doing some analysis based on new excel tables linked with active ranges and variables to filters. the problem is I done the interface for the change ( it should just change one Colum in a specific row (linked by file number) so I figured I should use VLOOKUP to find the Data Row and Display the data (Which is not getting changed) while having one filed for changing the Status (Open, in Progress, Closed) by a ComboBox.

BUT I can't manage to get the VLookup formula right, I don't quite know where the mistake is since I use it the first time and don't want to do it via a new excel tab and I wouldn't be able to Change the Data by just filtering it out.

I would be glad for any ideas or suggestion.

[Bob Phillips]

You can't just say you can't get the VLOOKUP right without showing us the data or the formula, we can't read across the ether.

Post the workbook and tell us how to see and reproduce the problem.

[gino-aviatio]

the point is I don't really get a code for that I tried a few VLookup functions I found but nothing worked, anyways here is the file the function should be concerning "Edit_Satus" user form based on Sheet("Database")

Private Sub UserForm_Initialize()
Worksheets("Database").Activate


'list box filling to choose for new Status
COB_Status.AddItem "In Progress"
COB_Status.AddItem "Closed"
COB_Status.AddItem "Open"


'TB Filling with conbtant about file, based on file Number
TB_FileNumber.Text = CStr(ThisWorkbook.Sheets("Filtered File").Range("A5").Value)
Dim FileNumber As String
    FileNumber = TB_FileNumber.Value
Set myrange = Range("A:K")
Dim Agent As String
Agent = Application.WorksheetFunction.VLookup(FileNumber, myrange, 2, False)
MsgBox Agent


''On Error GoTo Error1


''Tb_Agent.Value = Application.VLookup(TB_FileNumber.Text, Sheet1.Range("A4:K10000"), 1, False)


''Error1:
    ''MsgBox "Error - i guess something went wrong!"
''TB_Agent.Text =
''TB_Station.Text =
''TB_Date.Text =
''TB_Date_Due.Text =
''TB_CA_Responsible.Text =
''TB_Observation_Description.Text =
''TB_Corrective_Action_Description.Text =
''TB_Expectation_CA.Text =
''TB_Remarks.Text =
    
End Sub

The Lookup Function Fould be in for all TB_* Files Expecting TB_FileNumber where the Lookup is based on.
I tried to upload the file but it doesn't work might be due to the size

[mdmackillop]

Please post a sample file with layout and some data.

[gino-aviatio]

Here we go thank you guys in advance!!

[Bob Phillips]

There is no worksheet called Database in that workbook, no worksheet called Filtered File. It can't work without the pieces.

[gino-aviatio]

sorry for the miss understanding - I thought the database file would be enough.
anyways I had to make it a zip file to get it online

[gino-aviatio]

so i found a code and happily was able to alter it!

still Thank you for trying to solve it.

Private Sub COB_Change_Click()

'declare the variables
Dim findvalue As Range
Dim DataSH As Worksheet
'error handling
On Error GoTo errHandler:
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
Set DataSH = Sheet1
'check for values
If TB_FileNumber.Value = "" Or COB_Status.Value = "" Then
MsgBox "There is not data to edit"
Exit Sub
End If
'clear the listbox
'.RowSource = ""
'find the row to edit
Set findvalue = DataSH.Range("A5:A10000").Find(What:=Me.TB_FileNumber.Text, LookIn:=xlValues, LookAt:=xlWhole)
'update the values
findvalue = TB_FileNumber.Value
findvalue.Offset(0, 1) = TB_Agent.Value
findvalue.Offset(0, 2) = TB_Station.Value
findvalue.Offset(0, 3) = TB_Date.Value
findvalue.Offset(0, 4) = TB_Date_Due.Value
findvalue.Offset(0, 5) = COB_Status.Value
'unprotect the worksheets for the advanced filter
'Unprotect_All
'filter the data
'DataSH.Range("A5:CR10000").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                !$L$8:$L$9"), CopyToRange:=Range("Data!$N$8:$T$8"), _
Unique:=False
'if no data exists then clear the rowsource
'If DataSH.Range("N9").Value = "" Then
'lstEmployee.RowSource = ""
'Else
'add the filtered data to the rowsource
'lstEmployee.RowSource = DataSH.Range("outdata").Address(external:=True)
'End If
'return to sheet
'Sheet2.Select
'Protect all sheets
'Protect_All
'error block
On Error GoTo 0
Exit Sub
errHandler:
'Protect all sheets
'Protect_All
'show error information in a messagebox
MsgBox "An Error has Occurred " & vbCrLf & _
"The error number is: " & Err.Number & vbCrLf & _
Err.Description & vbCrLf & "Please notify the administrator"


ActiveWorkbook.Sheets("Database").Activate


Unload Me


End Sub