Sub InsertValue()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 as Range
Dim res As Variant
With Worksheets("Sheet1")
Set rng = .Range("A1")
set rng3 = .Range("B1")
End With
Set rng1 = Range("Date")
res = Application.Match(clng(rng3.value), rng1, 1)
If Not IsError(res) Then
Set rng2 = rng1(res)
rng2.Offset(0, 3).Value = rng.Value
rng.ClearContents
Else
MsgBox "Value is not within the" & vbNewLine _
& "range of values in sheet2"
End If
End Sub

--
Regards,
Tom Ogilvy


"James McKillen" wrote in message
...
Just to clarify what I am trying to do:
On sheet1 in cell A1, I want to put in a currency value.
Sheet2 has dates in column A formatted as Jul-04,Aug-04, Sep-04 and so
on. You were right. There is not a two column range but a named range of
those dates from A1:A160. It covers dates until year 2015.
The named range is "date".
In cell B1 on sheet1, I have =NOW()
When I enter a value in A1 on sheet1 and press a command buttton, I want
the code to cut the value from A1 and paste it 4 columns over from the
date range in the appropriate row based on today's date. I can derive
where the value should be pasted using the formula
=ADDRESS(MATCH(B1,sheet2!Date,1),4)
The things is, of course, that as far as I can see, it must be done with
VBA.





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!