Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy, paste without file name referenced after paste | Excel Discussion (Misc queries) | |||
Copy; Paste; Paste Special are disabled | Excel Discussion (Misc queries) | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) |