Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompt user for input and utilize that input | Excel Worksheet Functions | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
Find method using User input | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
Get User Input | Excel Programming |