Search for and select reference value

[gbaron]

Hi,

I am quite new to VBA and have spent the better portion of the day reading forums so I thought I would just ask because I am stumped. I would like to have a macro that searches for a value in a specific cell in a column on another sheet. I simply want to select the first match in the column so I can paste some values next to it.

Sheets("Input").Range("C13") *this is the value I would like to search for*
Sheets("Measures").Range("A1:A530") *this is the column and sheet I would like to search in. I would like to search from the top for the FIRST match and simply activate that cell so the following syntax will work:

ActiveCell.Offset(0, 2).Select
Range("L24:N24").Select
Selection.Copy
Sheets("Measures").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
ActiveCell.Offset(3, 0).Select
End Sub

Sorry if this is an overly simple question but I have tried the "find" function for the last five hours and I just don't get it.

Thank you

 

[hatstand]

Hi, A simple solution to get you started. Change 'lookat:=xlPart' to 'lookat:=xlWhole' if it's an exact match your looking for. Hope this helps.

Sub TestF()

Dim ValueToFind As String
ValueToFind = Range("MyValue").Value 'Use a named range to store your value in case columns or rows are added and moves the cell

Range("A1:A500").Find(What:=ValueToFind, lookat:=xlPart).Offset(0, 2).Activate

Range("L24:N24").Select
Selection.Copy
Sheets("Measures").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
ActiveCell.Offset(3, 0).Select
End Sub

 

[gbaron]

Hi hatstand,

I apologize, but I cannot seem to get it to work. I have a few questions about your solution that may help solve the problem though.

1. Dim ValueToFind As String
ValueToFind = Range("MyValue").Value Do I change this to a string value of my choice? "IDnumber"?

2. Range("A1:A500").Find(What:=ValueToFind, lookat:=xlPart).Offset(0, 2).Activate what do I put here? "C13"? Sheet("Input").Range("C13")?


3. With regard to the question #2, how does the program know to look at Sheet("measures")?

Here is another way to explain what I am trying to do:

Sheet 1 Sheet 2
A B Cell "C13"
1
2
3
4

I am trying to have the macro search Column A on Sheet 1 for for the value in cell C13 on Sheet 2
So that if the value in C13 is "3", then cell A3 will activate
Thanks for being patient with me. You may have to spell it out more for me if the original solution will still work.

 

[hatstand]

Hi,

Feel free to ask any question you like.
Anything you put into the 'MyValue' cell will be the variable that you are looking for. i.e. If yo put the word 'dog' into cell 'MyValue' the word will be carried as the variable 'ValueToFind'. If it makes it easier change:
ValueToFind = Range("MyValue").Value <TO> ValueToFind = Range("D1").Value

The line 'Range("A1:A500").Find(What:=ValueToFind, lookat:=xlPart).Offset(0, 2).Activate' broken down is doing this:

Range("A1:A500").Find = determins which range to look in.
(What:=ValueToFind = The 'ValueToFind' is carrying your variable .i.e. the word dog. So it will look for the word dog in the range you set. i.e. A1:A500
lookat:=xlPart) = This tell the code to look for a word .i.e. 'dog and cat' will be found as it contains the word 'dog'.
.Offset(0, 2).Activate = When the match is found offset the find location by no rows and 2 coulmns to the right.

I'ma bit confused as to what happens once a match has been found. Do you want to copy soemthing form somewhere to the offset location?

 

[gbaron]

Hi thank you it totally works now. Thank you for your patience to really spell it out for me that helped a ton.

Hi,

Feel free to ask any question you like.
Anything you put into the 'MyValue' cell will be the variable that you are looking for. i.e. If yo put the word 'dog' into cell 'MyValue' the word will be carried as the variable 'ValueToFind'. If it makes it easier change:
ValueToFind = Range("MyValue").Value <to> ValueToFind = Range("D1").Value

The line 'Range("A1:A500").Find(What:=ValueToFind, lookat:=xlPart).Offset(0, 2).Activate' broken down is doing this:

Range("A1:A500").Find = determins which range to look in.
(What:=ValueToFind = The 'ValueToFind' is carrying your variable .i.e. the word dog. So it will look for the word dog in the range you set. i.e. A1:A500
lookat:=xlPart) = This tell the code to look for a word .i.e. 'dog and cat' will be found as it contains the word 'dog'.
.Offset(0, 2).Activate = When the match is found offset the find location by no rows and 2 coulmns to the right.

I'ma bit confused as to what happens once a match has been found. Do yowant to copy soemthing form somewhere to the offset location?

</to>