find user input
 

I need a piece of code to do the following (within a larger procedure):

1) take the "A" cell of the row that a user is on
2) find that value in another sheet ("sheet 2", let's call it)
3) position the cursor on the find

from there, I will have it take certain pieces of information and perform a
function.

Sounds like a simple request, but I've always been bad with taking values
that are not hard coded and using them in things like find. So if anyone can
help me with the few lines that it takes to do the above, as well as any
definitions (and where definitions are supposed to go, as I know about them,
but have never actually used them), I'd be most grateful for some help. Thx.
--
Boris

find user input
 

looks in first 100 row's in column A

Sub test()
x = ActiveCell.Value
Sheets("Sheet2").Activate
For r = 1 To 100
If Cells(r, 1) = x Then r1 = r
Next
Cells(r1, 1).Select
End Sub


"BorisS" skrev:

I need a piece of code to do the following (within a larger procedure):

1) take the "A" cell of the row that a user is on
2) find that value in another sheet ("sheet 2", let's call it)
3) position the cursor on the find

from there, I will have it take certain pieces of information and perform a
function.

Sounds like a simple request, but I've always been bad with taking values
that are not hard coded and using them in things like find. So if anyone can
help me with the few lines that it takes to do the above, as well as any
definitions (and where definitions are supposed to go, as I know about them,
but have never actually used them), I'd be most grateful for some help. Thx.
--
Boris

find user input
 

thanks so much. Does the find function also work? I did a macro record, and
got this...

Cells.Find(What:="6", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

The only thing I don't know, with this one, or yours, is how to define the
variable that ends up taking the value of the input cell. In other words, my
user will be told that they need to select the cell or line that has the
value that needs to be found. Either way, I'll set the activecell to be the
correct column of that line, and then I need to run this code, which takes
that value, and goes and finds the cell with that value on the other sheet.

So in your example, how would you define 'x'? The DIM thing is involved,
but this is where I've never actually used that in Excel (which would
surprise you if you knew how much I know Excel). :)

Thx for the final bit of help.
--
Boris


"excelent" wrote:

looks in first 100 row's in column A

Sub test()
x = ActiveCell.Value
Sheets("Sheet2").Activate
For r = 1 To 100
If Cells(r, 1) = x Then r1 = r
Next
Cells(r1, 1).Select
End Sub


"BorisS" skrev:

I need a piece of code to do the following (within a larger procedure):

1) take the "A" cell of the row that a user is on
2) find that value in another sheet ("sheet 2", let's call it)
3) position the cursor on the find

from there, I will have it take certain pieces of information and perform a
function.

Sounds like a simple request, but I've always been bad with taking values
that are not hard coded and using them in things like find. So if anyone can
help me with the few lines that it takes to do the above, as well as any
definitions (and where definitions are supposed to go, as I know about them,
but have never actually used them), I'd be most grateful for some help. Thx.
--
Boris

find user input
 

depends of the value
Dim x
Dim x as string
Dim x as single
Dim x as double
Dim x as Range


"BorisS" skrev:

thanks so much. Does the find function also work? I did a macro record, and
got this...

Cells.Find(What:="6", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

The only thing I don't know, with this one, or yours, is how to define the
variable that ends up taking the value of the input cell. In other words, my
user will be told that they need to select the cell or line that has the
value that needs to be found. Either way, I'll set the activecell to be the
correct column of that line, and then I need to run this code, which takes
that value, and goes and finds the cell with that value on the other sheet.

So in your example, how would you define 'x'? The DIM thing is involved,
but this is where I've never actually used that in Excel (which would
surprise you if you knew how much I know Excel). :)

Thx for the final bit of help.
--
Boris


"excelent" wrote:

looks in first 100 row's in column A

Sub test()
x = ActiveCell.Value
Sheets("Sheet2").Activate
For r = 1 To 100
If Cells(r, 1) = x Then r1 = r
Next
Cells(r1, 1).Select
End Sub


"BorisS" skrev:

I need a piece of code to do the following (within a larger procedure):

1) take the "A" cell of the row that a user is on
2) find that value in another sheet ("sheet 2", let's call it)
3) position the cursor on the find

from there, I will have it take certain pieces of information and perform a
function.

Sounds like a simple request, but I've always been bad with taking values
that are not hard coded and using them in things like find. So if anyone can
help me with the few lines that it takes to do the above, as well as any
definitions (and where definitions are supposed to go, as I know about them,
but have never actually used them), I'd be most grateful for some help. Thx.
--
Boris

find user input
 

Boris,
Something like this:

Private Sub CommandButton1_Click()
Dim FoundCell As Range
With Worksheets("Sheet4")
Set FoundCell = .Range("A1:A9").Find(ActiveCell.Value, , xlValues)
If Not FoundCell Is Nothing Then
.Select
FoundCell.Select
Else
MsgBox "No match"
ActiveCell.Select
End If
End With
End Sub

NickHK

"BorisS" wrote in message
...
I need a piece of code to do the following (within a larger procedure):

1) take the "A" cell of the row that a user is on
2) find that value in another sheet ("sheet 2", let's call it)
3) position the cursor on the find

from there, I will have it take certain pieces of information and perform
a
function.

Sounds like a simple request, but I've always been bad with taking values
that are not hard coded and using them in things like find. So if anyone
can
help me with the few lines that it takes to do the above, as well as any
definitions (and where definitions are supposed to go, as I know about
them,
but have never actually used them), I'd be most grateful for some help.
Thx.
--
Boris