VBA Copy & Paste

J

Jim McKillen

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?
 
T

Tom Ogilvy

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
 
J

James McKillen

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!
 
T

Tom Ogilvy

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
 
J

Jim McKillen

Tom-
Your code works like clockwork....pun intended.
My hat's off to the ExcelMeister!!! Thanks.
 
Top