Question Loop Until Question

[drew23]

Hi guys

I am trying to write something that take value from page 1 and looks for that value in a given range in page 2. I then want to make a loop so that it looks for that value in page two until the search returns no results.

This is my code:

a = Cells(counter, 3).Value

Do

Sheets("Sheet2").Select
Columns("C:C").Select
Selection.Find(What:=a, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Copy
Sheets("Sheet1").Select
Cells(counter, 4).PasteSpecial

Sheets("Sheet2").Select

***here is my problem. I want a loop that will do this command
Selection.FindNext(After:=ActiveCell).Activate
until there is no match.***


Next counter

Thanks for your help

 

[Alisa]

Did you mean to post this in the excel section?

 

[NigelShaw]

Hi

I too guess this should have been posted in excel not access but hey ho, it happens.

Why can't you use a straight forward dlookup or hlookup function? Is there something special involved? Is the cell content your trying to search in a specific column or random across a sheet?

Are you checking for duplicates?

Do you want to create a filtered list of duplicated items?

I used to program excel extensively so could help you if you outline your need more specifically.

Nigel

 

[drew23]

Nigel

Thanks for you help. I can't use the v/d lookup formula because i dont know the exact name of what I am looking for. ie I am taking the first 6 laters from the names in sheet 1 and trying to find all the words that contain these first six letters in sheet 2.

All the words in sheet 2 are in one specific column (C:C).

The idea is to get the first 6 letters from sheet 1 and then by searching in sheet 2 get a list of all posible matches.

thanks

Hi

I too guess this should have been posted in excel not access but hey ho, it happens.

Why can't you use a straight forward dlookup or hlookup function? Is there something special involved? Is the cell content your trying to search in a specific column or random across a sheet?

Are you checking for duplicates?

Do you want to create a filtered list of duplicated items?

I used to program excel extensively so could help you if you outline your need more specifically.

Nigel

 

[NigelShaw]

Hi

that doesn't seem too difficult. So to get this straight, the data in sheet one is random and you are searching column c in sheet 2?

Where do you want the list to be generated?

I would do this in vba and carry out the following-

1. Get 6 letters from first cell
2. Search sheet 2 column c for a match
3. Get the next available space in the new list
4. Add the match to the list
5. Loop

to prevent visual sheet switching and very fast cell selection, I would turn off screenupdating.


I'll look at this when I get into the office but in the mean time, do you want to either post your xls or pm me and I'll give you my email to sent it. It's much easier when you have the data you are working with.



Regs


Nigel