MATCH formula

[peyman]

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

 

[Joel]

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?

 

[Mike H]

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]

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,

 

[peyman]

thanx let me try

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]

Mike,
I used the macro but nothing happened!!
I'm not transfered to sheet2!! where the result can be seen?!

 

[Mike H]

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

 

[Mike H]

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

 

[peyman]

thanx Mike it works great!! but why did you add myValue + 3? I don't get it.

 

[Mike H]

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]

got it. Thanx a lot.

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