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

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

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

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

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

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

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

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

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

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

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