Tom-
Your code works like clockwork....pun intended.
My hat's off to the ExcelMeister!!! Thanks.
"Tom Ogilvy" wrote in message
...
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!