VBAnovice58
New Member
- Joined
- Mar 18, 2021
- Messages
- 3
- Office Version
- 2019
- Platform
- Windows
Good day,
I am working on creating a database in excel and looking to set up some data retrieval code to identify the row number in a column which will then extract the record details and populate a user form form the individual cells of the spreadsheet row.
I have some code which although it identifies the value placed in the textbox of the user form, it stubbornly refuses to indicate the correct row where the value is in the column.
Can anyone throow any light where i may be going wrong?
Thanks in advance
Colin
I am working on creating a database in excel and looking to set up some data retrieval code to identify the row number in a column which will then extract the record details and populate a user form form the individual cells of the spreadsheet row.
I have some code which although it identifies the value placed in the textbox of the user form, it stubbornly refuses to indicate the correct row where the value is in the column.
VBA Code:
Private Sub CbRetrieve_Click()
Dim myrow As Integer
Set MyRange = Sheets("Budget_Sheet").Range("A10:A1000") ' Change to suit
If txtRecordNoU.Text <> "" And IsNumeric(txtRecordNoU) Then
myval = txtRecordNoU.Value ' input value of record on database' doing nothing
myval = CLng(myval) 'ensure input value is a number doing nothing
If IsError(Application.Match(CLng(txtRecordNoU.Text), MyRange, 0)) Then
MsgBox "Lookup for " & txtRecordNoU.Text & " not found"
Unload UserForm2
UserForm2.Show
End If
Else
MsgBox "Invalid input"
Unload UserForm2
UserForm2.Show
End If
' Identifying row where data is held
myrow = Application.Match(myval, Sheets("Budget_Sheet").Range("A10:A100"), 0)
'Rows(myrow).Select
' retrieving values from spreadsheet into text boxes U means update
txtAreaOfficeU.Value = Cells(myrow, 2) 'Area Office
txtRoadNoU.Value = Cells(myrow, 3) 'Road No
txtSiteNameU.Value = Cells(myrow, 4) 'Site Name
etc etc
Can anyone throow any light where i may be going wrong?
Thanks in advance
Colin