Variable Find reference to a column?

Simple question for someone,
I want to have a find command run and use a varible defined from a column on the same sheet I am performing the find on.
I can do everything with the exception of defining the column that the variable will work through when it is defining the find name.
Anyone care to help?
TIA
Patrick

it always helps for us to assist you if you post your code so that we can see what you are trying to acheive and be able to give you better advice

Hi,

Mudraker is right ... your question needs clarification ...
Are you looking for code along these lines ...

Please Login or Register  to view this content.

HTH
Carim

What the macro will be doing is going through a list of names, each name will become a variable in a find command, the find command will run on another sheet and when the name on the other sheet has been found it will copy a set of stats and paste them into the original sheet.
The part I have no idea how to do is setting up the list and having the code run through all the names on the list and each time making that name a variable.
Any clearer? I have not done any code for this yet so I cannot post any
TIA
Patrick

Hi,

Given the fact that all your names are in a range, you can loop in this range ...
with i as row and j as column
For i = 1 to whatever
For j = 1 to whatever
Cells(i,j).Select
next j
next i

HTH
Carim

And so if I had a statement like
Dim name As string
name = activecell

Making the variable name equal to the current cell selected in the list?
Correct?
Patrick

Yes...that's right

One last question, how do I set i as a row variable and j as a column variable??
Thanks for the excellent help
Patrick

You can do it with

i = ActiveCell.Row
j = ActiveCell.Column

HTH
Carim

And so using this method I could use
range("Z1").select
For i = 1 to 20
For j = 26
Cells(i,j).Select
next j
next i

Would this work?

Please Login or Register  to view this content.

HTH
Carim

So this is what I have BUT I get a type mismatch??
Any ideas?

Dim name As String
name = ActiveCell
Dim i As Range
Dim j As Range
i = ActiveCell.Row
j = ActiveCell.Column

For i = 1 To 20
Cells(i, 26).Select


Windows("IMPORT BOOK.xls").Activate
Sheets("Current.xls").Select
Cells.Find(What:="name", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
StartCell = ActiveCell.Offset(0, 0).Address
EndCell = ActiveCell.Offset(0, 19).Address
Range(StartCell, EndCell).Copy
Windows("Core per Hour.xls").Activate
Sheets("Current").Select
Range("C1").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues
Next i

small correction

Please Login or Register  to view this content.

But I would recommend another variable word ... e.g Nam1

Please Login or Register  to view this content.

and adjust the find portion too ...

HTH
Carim

Ok well I edited as you suggested but is still gives me a type mismatch for i
?
Should this variable be something different?
TIA

Make sure your name instruction appears once you have selected your cell

Please Login or Register  to view this content.

HTH
Carim

Ok so I have updated and redone some of the code but I still get type mismatch for i variable. Below is the code. Something that also seems to be causing a problem is the line which finds the next blank cell to paste the copied data to.

Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
TIA

Dim Nam1 As String
Dim i As Range
Dim j As Range
i = ActiveCell.Row
j = ActiveCell.Column

For i = 1 To 20
Cells(i, 26).Select
Nam1 = ActiveCell.Value


Windows("Core per Hour.xls").Activate
Sheets("High Value Queue").Select


Cells.Find(What:="Nam1", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
StartCell = ActiveCell.Offset(0, 0).Address
EndCell = ActiveCell.Offset(0, 19).Address
Range(StartCell, EndCell).Copy
Windows("Core per Hour.xls").Activate
Sheets("High Value Queue").Select
Range("C1")..PasteSpecial Paste:=xlPasteValues
Next i

Dim i,j As Integer

HTH
Carim

Ok so I fixed the problem by changing the i variable to integer instead of range, now I have another problem... my list is in one workbook and my sheet I am searching and copying from is in another workbook, when I select the second workbook it also changes the find variable, is there any way around this??
TIA
Patrick

LOL... great minds..
Any ideas on the above problem?

Where would I enter Public?
I have tried just pasting after the macro name but it gives an error?
TIA
Patrick

And now when I put the code in as instructed I get an error variable not defined for the StartCell = , before I enter the public section everything worked.
Any ideas?
Patrick

What I did for a workaround was copied the first find on the first book, pasted it into an empty cell at the top of the second workbook and then it still used it as the variable for the find. This worked for what I needed

Something else I have been wondering how to do with a sheet is finding a particular value and then a second value further down a sheet and then selecting a range between the two. Any idea how this could be done??
TIA
Patrick

Works perfect,,, so simple... I love vba