Selection based on cell content

[A_Pitts]

I am using a workbook as a sample log database with 9 fields. After a sample ships, I want to retrieve the entry to another sheet called Request, modify it, and then copy it back onto the original. I need to search column A of the sheet Sample Log! using the sample number stored in Request!A9. In another application, I have used the following code to search a specific range for specific text:

Dim ColRange As Long
ColRange = Range("N21:N" & Range("N200").End(xlUp).Row).Find(What:="CLOSED", LookIn:=xlFormulas, LookAt:=xlWhole).Row
Range("A" & ColRange & ":I" & ColRange).Copy

But since this will continue to grow larger over time, I need to search the entire column A of the sheet Sample Log, and I want to search for the sample number which will be a user entry in Request!A9 instead of the fixed text "CLOSED".

Any help would be appreciated.

 

[Yogi Anand]

Hi A_Pitts:

Let me see if I understand it right -- you want to see if the entry in Request!A9 is in column A of the sheet Sample Log -- if this is correct, then one of the ways you can do this is by using the MATCH function.

Try ...

Sub y_APitt1()
    On Error Resume Next
    yMatch = Evaluate("=ADDRESS(MATCH(Request!A9,SampleLog!A:A,0),1)")
    MsgBox yMatch
 End Sub

Does it help?

 

[A_Pitts]

Thanks for the reply.

That looks like a good way to locate the cell, but I don't know how to bring that information into my copy and paste operation. This is what I tried:

On Error Resume Next
ymatch = Evaluate("=ADDRESS(MATCH(Request!A9,Sample Log!A:A,0),1)")
MsgBox ymatch
Dim ColRange As Long
ColRange = Range(ymatch).Row
Range("A" & ColRange & ":I" & ColRange).Copy
Range("A11").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _False, Transpose:=False

What am I missing?

 

[Yogi Anand]

Hi A_Pitts:

Couple of things ...

1. if your file name Sample Log has a space between Sample and Log, in the formula you must use file name as

'Sample Log'

2. If you want to locate only the row number using the MATCH function, then you can use ...

yMatch = Evaluate("=MATCH(Request!A9,'Sample Log'!A:A,0)")

to return the row number of the cell where entry in Request!A9 matches the entry in column A of worksheet 'Sample Log'

I am going to leave it upto you to adopt it in your formulation to suit.

 

[A_Pitts]

Thanks a lot, Yogi.
That works exactly as intended and was simple. I am constantly amazed at all the different methods available to VBA.

Again, thanks for the help.

P.S. If you were asked to recommend only one book to a VBA novice, which would it be?

 

[Yogi Anand]

Hi A_Pitts:

Regarding a beginning book on VBA, Walkenbach's books are very popular. However, I would suggest you do a search on the Board for recommendations made, look at the selections available via MrExcel's on-line bookstore, and other such resources. You want to select a book that meets your own style of learning.