Hello all,

I have two little problems that i would greatly apreciate
any help with!!

FIRST:
I am trying to search through my staff register for
members of staff with first/sur name match, these are
found in columns 2 and 3
but the range i am using is from .cells(6,1) to .Cells
(65536, 1).End(xlUp)
only problem is... column A contains staff numbers and my
records do not have staff numbers for all members of
staff... so... it only searches from the first member to
the last member whos staff number is available and none
after that!!
any ideas of how i can force the search to go through all
my records? bearing in mind that i need cell location to
start at column A so i can manipulate the offset!!

SECONDLY:
My search results are displayed in a listbos... when i
double click on the data in the list box...
Data = cell.Text + " " + cell.Offset(0, 2).Text + " " +
cell.Offset(0, 3).Text

therefore when i double click a record i wish to display i
need to be able to display where this critera is met...
i have tried using a loop until cell.Text + " " +
cell.Offset(0, 2).Text + " " + cell.Offset(0, 3).Text =
list index, but it does not work...

any ideas on how i can get this to work??

Thank you for any help

Robert Couchman

For your first problem, why not use offset again, eg assuming that column B
is populated for all records:

..Cells(65536, 2).End(xlUp).Offset(0,-1)

Cheers,
Jeff

"Robert Couchman" wrote in message
...
Hello all,

I have two little problems that i would greatly apreciate
any help with!!

FIRST:
I am trying to search through my staff register for
members of staff with first/sur name match, these are
found in columns 2 and 3
but the range i am using is from .cells(6,1) to .Cells
(65536, 1).End(xlUp)
only problem is... column A contains staff numbers and my
records do not have staff numbers for all members of
staff... so... it only searches from the first member to
the last member whos staff number is available and none
after that!!
any ideas of how i can force the search to go through all
my records? bearing in mind that i need cell location to
start at column A so i can manipulate the offset!!

SECONDLY:
My search results are displayed in a listbos... when i
double click on the data in the list box...
Data = cell.Text + " " + cell.Offset(0, 2).Text + " " +
cell.Offset(0, 3).Text

therefore when i double click a record i wish to display i
need to be able to display where this critera is met...
i have tried using a loop until cell.Text + " " +
cell.Offset(0, 2).Text + " " + cell.Offset(0, 3).Text =
list index, but it does not work...

any ideas on how i can get this to work??

Thank you for any help

Robert Couchman

The following will give you the number of the last row used of an
column:

lrow = Sheet1.UsedRange.Row + Sheet1.UsedRange.Rows.Count

- piku

--
Message posted from http://www.ExcelForum.com