Copy and paste value automatically on finding matching value

MrCaptain

Board Regular
Joined
Dec 5, 2006
Messages
123
Hi all,

Here's some code which is frustrating me!

The last part of the macro RowRead below does not paste the value to the selected cell. When I click in the selected cell manually it works. I need the macro to achieve this automatically, any suggestions or solutions. All answers gratefully acknowledged. The Range "K2:AC2" represents columns 11 to 29 in the code.

Regards,Vern

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("K2:AC2")) Is Nothing ThenCall RowRead
End If
End Sub


Code:
Sub RowRead()
Dim i As Integer
For i = 11 To 29 
       If Cells(3, i) = Cells(1, "B") Then 
           Cells(2, i) = Cells(4, i) 
       End If 
Next i 
       Cells(4, i).Copy 
       Cells(2, i).Select 
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _        :=False, Transpose:=False 
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I think it would be better if you were to tell us in words what your trying to do,

Looks like your saying if you select any cell in Range("
K2:AC2") you want something to happen.

You said:

I need the macro to achieve this automatically

Very little can happen in excel automatically

You must do something to activate the script.

Otherwise the script would run constantly.
The minute you open the workbook



 
Last edited:
Upvote 0
I think it would be better if you were to tell us in words what your trying to do,

Looks like your saying if you select any cell in Range("
K2:AC2") you want something to happen.

You said:

I need the macro to achieve this automatically

Very little can happen in excel automatically

You must do something to activate the script.

Otherwise the script would run constantly.
The minute you open the workbook


Ok then,
If the value in cell B1 changes compare it with the values in cells J2 to AC2. If a match is found then copy a value from the row under the matched cell (row 3) and paste it above the matched value to row 1. So if B1 was equal to say L2 then copy the value in L3 and paste it to L1 and retain it there. When B1 changes again, say to a value equal to the value in P2 then copy P3 and paste into P1 cell as value. Retain the previous value in L1 and so on.
B1 value changes by formula linked to another sheet so its not a value its the resiult of a formula. The comparison works, just cant get the value in row 3 to copyinto row 1.
Thats the best explanation I can offer, hope it makes sense.
Regards,
Vern





 
Upvote 0
What your asking for is a sheet calculation change event script. And I'm not familiar with how that works.
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 
Upvote 0
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.
Code:
Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Dim rng As Range
    Set rng = Range("J2:AC2").Find(Range("B1").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not rng Is Nothing Then
        rng.Offset(-1, 0).Value = rng.Offset(1, 0).Value
    End If
    Application.ScreenUpdating = True
End Sub
The macro will run automatically each time the value in B1 changes.
 
Upvote 0
Thanks mumps,
It sort of worked but then gave the error:
'Run time error 1004 Application-defined or object-defined error first time, then after closing and reopening the workboook it gave this error:
'Run time error 2147417848 (80010108) Method 'Find' of object 'Range' failed
I am running Windows 10 and Excel 2016.
Any suggestions appreciated.
Regards,
Vern
 
Upvote 0
I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
Thanks for your offer mumps, I really dont want to go down that road so I think I will regretably abandon this for now.
Regards,
Vern
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top