Finding row number of a value in column

VBAnovice58

New Member
Joined
Mar 18, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. 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.

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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
VBA Code:
txtAreaOfficeU.Value = Cells(myrow+9, 2) 'Area Office
 
Upvote 0
i don't think i explained the problem very well, i have a column (column A ) whihc has the record numbers in. the data retrieval form has a text box for which the record number required in column A is entered, i have a "lookup" button which when clicked and it will search the column A for this number and identify the row number and extract the data from the cells in that row of the spreadsheet to populate the userform to allow the record to be updated.

The answer " txtAreaOfficeU.Value = Cells(myrow+9, 2) 'Area Office" above does not have any relevance to the quesiton i was asking.

Hope i have clarified my query.

Regards
Colin
 
Upvote 0
Hope i have clarified my query.
Not really, you have not told us what is failing & in what way.
Did you actually try what mart37 suggested, or did you just dismiss it out of hand?
 
Upvote 0
The code does not identify the correct row number in the database where the value entered in the user form text box is located in column a.
 
Upvote 0
Please do not "hijack" other members threads. If you have a question please start your own thread.
 
Upvote 0
Apologies, it seems i have two accounts as i logged in on another pc with another account.

The response above "The code does not identify the correct row number in the database where the value entered in the user form text box is located in column a.", is correct, the code doesn't identify the correct row no the issue is somewhere in the code below

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

Regards
Colin
 
Upvote 0
Which row is it pulling from & which row should it be returning?
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top