| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH formula
hi,
I've got a formula like: =match(A6, SHEET2A4:A100,0) and I want to have a COMMAND BUTTON and instead of A6 in the formula ,I like to have a variable(I mean a dynamic value) like activecell so that when I press the COMMAND BUTTON, the formula functions for the cell I'm on. Is there a way? Thanks, Peiman |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MATCH formula
What you asked for will produce a circular calculation. You want a fixed
offset from the active cell to place either the formula or the A6 location. With a macro, you can have a pop up come up to select the cell. Do you want a macro? "peyman" wrote: hi, I've got a formula like: =match(A6, SHEET2A4:A100,0) and I want to have a COMMAND BUTTON and instead of A6 in the formula ,I like to have a variable(I mean a dynamic value) like activecell so that when I press the COMMAND BUTTON, the formula functions for the cell I'm on. Is there a way? Thanks, Peiman |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MATCH formula
Hi,
I assume when you say you want the formula functions for the cell you are on then this is the lookup value Private Sub CommandButton1_Click() LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) End Sub Mike "peyman" wrote: hi, I've got a formula like: =match(A6, SHEET2A4:A100,0) and I want to have a COMMAND BUTTON and instead of A6 in the formula ,I like to have a variable(I mean a dynamic value) like activecell so that when I press the COMMAND BUTTON, the formula functions for the cell I'm on. Is there a way? Thanks, Peiman |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MATCH formula
Thanx Joel,
I'M Okay with Macro but let me clarify more what I need to do, the A6 or the active cell I need to find the match is in sheet 1. My purpose is when,for example, I'm on cell F24 in sheet 1 ,by pressing the COMMAND BUTTON I'll be transfered to the corresponding cell(there is a cell with the same content of F24 in sheet 2) in sheet 2. I hope I'm clear on that. Thanx, "Joel" wrote: What you asked for will produce a circular calculation. You want a fixed offset from the active cell to place either the formula or the A6 location. With a macro, you can have a pop up come up to select the cell. Do you want a macro? "peyman" wrote: hi, I've got a formula like: =match(A6, SHEET2A4:A100,0) and I want to have a COMMAND BUTTON and instead of A6 in the formula ,I like to have a variable(I mean a dynamic value) like activecell so that when I press the COMMAND BUTTON, the formula functions for the cell I'm on. Is there a way? Thanks, Peiman |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MATCH formula
thanx let me try
"Mike H" wrote: Hi, I assume when you say you want the formula functions for the cell you are on then this is the lookup value Private Sub CommandButton1_Click() LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) End Sub Mike "peyman" wrote: hi, I've got a formula like: =match(A6, SHEET2A4:A100,0) and I want to have a COMMAND BUTTON and instead of A6 in the formula ,I like to have a variable(I mean a dynamic value) like activecell so that when I press the COMMAND BUTTON, the formula functions for the cell I'm on. Is there a way? Thanks, Peiman |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MATCH formula
Mike,
I used the macro but nothing happened!! I'm not transfered to sheet2!! where the result can be seen?! "Mike H" wrote: Hi, I assume when you say you want the formula functions for the cell you are on then this is the lookup value Private Sub CommandButton1_Click() LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) End Sub Mike "peyman" wrote: hi, I've got a formula like: =match(A6, SHEET2A4:A100,0) and I want to have a COMMAND BUTTON and instead of A6 in the formula ,I like to have a variable(I mean a dynamic value) like activecell so that when I press the COMMAND BUTTON, the formula functions for the cell I'm on. Is there a way? Thanks, Peiman |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MATCH formula
Hi,
I've only just understood what you want. try this. View|Toolbars|Forma and put a button on your sheet. Click 'New' on the popup and paste the code below in and try it LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) Sheets("Sheet2").Select Sheets("Sheet2").Range("A" & myvalue + 3).Select Mike "peyman" wrote: Mike, I used the macro but nothing happened!! I'm not transfered to sheet2!! where the result can be seen?! "Mike H" wrote: Hi, I assume when you say you want the formula functions for the cell you are on then this is the lookup value Private Sub CommandButton1_Click() LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) End Sub Mike "peyman" wrote: hi, I've got a formula like: =match(A6, SHEET2A4:A100,0) and I want to have a COMMAND BUTTON and instead of A6 in the formula ,I like to have a variable(I mean a dynamic value) like activecell so that when I press the COMMAND BUTTON, the formula functions for the cell I'm on. Is there a way? Thanks, Peiman |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MATCH formula
Hi
A bit of error trapping LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) If myvalue < "" Then Sheets("Sheet2").Select Sheets("Sheet2").Range("A" & myvalue + 3).Select Else MsgBox "Lookup value " & ActiveCell.Value & " Not found" End If Mike "Mike H" wrote: Hi, I've only just understood what you want. try this. View|Toolbars|Forma and put a button on your sheet. Click 'New' on the popup and paste the code below in and try it LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) Sheets("Sheet2").Select Sheets("Sheet2").Range("A" & myvalue + 3).Select Mike "peyman" wrote: Mike, I used the macro but nothing happened!! I'm not transfered to sheet2!! where the result can be seen?! "Mike H" wrote: Hi, I assume when you say you want the formula functions for the cell you are on then this is the lookup value Private Sub CommandButton1_Click() LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) End Sub Mike "peyman" wrote: hi, I've got a formula like: =match(A6, SHEET2A4:A100,0) and I want to have a COMMAND BUTTON and instead of A6 in the formula ,I like to have a variable(I mean a dynamic value) like activecell so that when I press the COMMAND BUTTON, the formula functions for the cell I'm on. Is there a way? Thanks, Peiman |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MATCH formula
thanx Mike it works great!! but why did you add myValue + 3? I don't get it.
"Mike H" wrote: Hi, I've only just understood what you want. try this. View|Toolbars|Forma and put a button on your sheet. Click 'New' on the popup and paste the code below in and try it LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) Sheets("Sheet2").Select Sheets("Sheet2").Range("A" & myvalue + 3).Select Mike "peyman" wrote: Mike, I used the macro but nothing happened!! I'm not transfered to sheet2!! where the result can be seen?! "Mike H" wrote: Hi, I assume when you say you want the formula functions for the cell you are on then this is the lookup value Private Sub CommandButton1_Click() LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) End Sub Mike "peyman" wrote: hi, I've got a formula like: =match(A6, SHEET2A4:A100,0) and I want to have a COMMAND BUTTON and instead of A6 in the formula ,I like to have a variable(I mean a dynamic value) like activecell so that when I press the COMMAND BUTTON, the formula functions for the cell I'm on. Is there a way? Thanks, Peiman |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MATCH formula
Hi,
It was because your range started in A4, Index returned the position of the lookup value in the array so if it finds in in A4 it returns 1 so adding 3 gives me the correct row in the worksheet in the worksheet. Mike "peyman" wrote: thanx Mike it works great!! but why did you add myValue + 3? I don't get it. "Mike H" wrote: Hi, I've only just understood what you want. try this. View|Toolbars|Forma and put a button on your sheet. Click 'New' on the popup and paste the code below in and try it LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) Sheets("Sheet2").Select Sheets("Sheet2").Range("A" & myvalue + 3).Select Mike "peyman" wrote: Mike, I used the macro but nothing happened!! I'm not transfered to sheet2!! where the result can be seen?! "Mike H" wrote: Hi, I assume when you say you want the formula functions for the cell you are on then this is the lookup value Private Sub CommandButton1_Click() LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) End Sub Mike "peyman" wrote: hi, I've got a formula like: =match(A6, SHEET2A4:A100,0) and I want to have a COMMAND BUTTON and instead of A6 in the formula ,I like to have a variable(I mean a dynamic value) like activecell so that when I press the COMMAND BUTTON, the formula functions for the cell I'm on. Is there a way? Thanks, Peiman |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MATCH formula
got it. Thanx a lot.
"Mike H" wrote: Hi, It was because your range started in A4, Index returned the position of the lookup value in the array so if it finds in in A4 it returns 1 so adding 3 gives me the correct row in the worksheet in the worksheet. Mike "peyman" wrote: thanx Mike it works great!! but why did you add myValue + 3? I don't get it. "Mike H" wrote: Hi, I've only just understood what you want. try this. View|Toolbars|Forma and put a button on your sheet. Click 'New' on the popup and paste the code below in and try it LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) Sheets("Sheet2").Select Sheets("Sheet2").Range("A" & myvalue + 3).Select Mike "peyman" wrote: Mike, I used the macro but nothing happened!! I'm not transfered to sheet2!! where the result can be seen?! "Mike H" wrote: Hi, I assume when you say you want the formula functions for the cell you are on then this is the lookup value Private Sub CommandButton1_Click() LookupValue = ActiveCell.Value On Error Resume Next myvalue = WorksheetFunction.Match(LookupValue, _ Sheets("Sheet2").Range("A4:A100"), 0) End Sub Mike "peyman" wrote: hi, I've got a formula like: =match(A6, SHEET2A4:A100,0) and I want to have a COMMAND BUTTON and instead of A6 in the formula ,I like to have a variable(I mean a dynamic value) like activecell so that when I press the COMMAND BUTTON, the formula functions for the cell I'm on. Is there a way? Thanks, Peiman |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| which formula? Match? IF? | Excel Worksheet Functions | |||
| match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| MATCH Formula | Excel Worksheet Functions | |||
| Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions | |||
Linear Mode
