VBA Copy & Paste
 

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?

VBA Copy & Paste
 

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?

VBA Copy & Paste
 

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!

VBA Copy & Paste
 

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!

VBA Copy & Paste
 

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!