It is unclear exactly what you want to do. Match only works on a single
column and you say you have two. Not sure what ## is supposed to mean in
your formula.

Anyway, this might give you some ideas:

Sub InsertValue()
Dim rng As Range, rng1 As Range
Dim rng2 As Range
Dim res As Variant
With Worksheets("Sheet1")
Set rng = .Range("A1")
End With
With Worksheets("Sheet2")
Set rng1 = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With

res = Application.Match(rng.Value, rng1, 1)
If Not IsError(res) Then
Set rng2 = rng1(res)
Debug.Print rng.Value, rng2.Value
If rng.Value < rng2.Value Then
rng2.Offset(1, 0).EntireRow.Insert
rng2.Offset(1, 0).Value = rng.Value
rng.ClearContents
Else
' the value in A1 equals one of the values
' in column A of sheet2. So what do you want
' to do with this - doesn't make sense to
' put in a duplicate
MsgBox "Duplicate"
End If
Else
MsgBox "Value is not within the" & vbNewLine _
& "range of values in sheet2"
End If
End Sub

--
Regards,
Tom Ogilvy

"Jim McKillen" wrote in message
news:85bjc.36180$GR.4958929@attbi_s01...
This one has me stumped. Here is a vastly simplified version of it:
On (sheet1) I have a value that I am going to enter into cell A1.
On (sheet2) I have a two column range.
What I need is the VBA code that executes in a command button click
procedure that-
a) cuts the value entered into cell A1 on (sheet1), and
b) pastes the value into the range on (sheet2) in a specific place derived
from the
Excel function formula: =ADDRESS(MATCH(#,range,1)##)

Can anyone help?