i think its just this line....
HTHCode:d = ws.Range("a2", ws.[s65536].End(xlUp)).Value 'Change to this d = ws.Range("a2", ws.[g65536].End(xlUp)).Value
Hi, I plagiarised somebody elses spreadsheet in order to create a simple search tool using a userform. Unfortunately I didn't fully understand all the VBA code and consequently can't get it to search properly. My sheet has some simple data organised in rows to record a customer's job. In essence the sheet registers the customer, their unique reference, the job number we allocate it and the date received. I want to use the userform, with 2 comboxes to search by customer, then their reference and once the appropriate record has been shown the 2 text boxes display our job number and the date.
I'd be very grateful if someone could show me the error of my ways.
I've attached a simplified version of my sheet.
Try placing this in your code
your looking at the list index which works for the first instance of the A0613A... it won't work on the others...Code:Private Sub CboOrderNo_Change() Dim xlLongRow As Long Dim xlStringValue As String Dim xlInt As Integer Dim xlWrkSht As Worksheet Set xlWrkSht = Sheets("Register") xlLongRow = xlWrkSht.Cells(Rows.Count, 5).End(xlUp).Row xlStringValue = Me.CboOrderNo.Value For xlInt = 2 To xlLongRow Step 1 If xlWrkSht.Cells(xlInt, 5).Value = xlStringValue Then Me.TxtJobNo.Value = xlWrkSht.Cells(xlInt, 3).Value Me.TxtDate.Value = Format(xlWrkSht.Cells(xlInt, 2).Value, "dd-mmm-yy") Exit For End If Next xlInt Set xlWrkSht = Nothing End Sub
That works a treat. I eventually want to use this on another sheet which has more columns, say up to P and show more data on the search userform. Is there a way to use this sort of search function with the userform to allow the user to find the data in the first place, view it on the userform, edit some elements of it and then have the amended data replaced on the sheet?
[Additional questions removed. Read & adhere to the rules please, or have posting rights revoked]
Grateful for all help received.
Last edited by Dave Hawley; November 19th, 2007 at 10:28.
There are currently 1 users browsing this thread. (0 members and 1 guests)