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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
txtAreaOfficeU.Value = Cells(myrow+9, 2) 'Area Office
 

VBAnovice58

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,012
Office Version
  1. 365
Platform
  1. Windows
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?
 

Smithy02468

New Member
Joined
Aug 4, 2013
Messages
13

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,012
Office Version
  1. 365
Platform
  1. Windows
Please do not "hijack" other members threads. If you have a question please start your own thread.
 

VBAnovice58

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,012
Office Version
  1. 365
Platform
  1. Windows
Which row is it pulling from & which row should it be returning?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,403
Messages
5,641,936
Members
417,247
Latest member
Chitaah

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
Top