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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,829
Office Version
  1. 2013
Platform
  1. Windows
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:

MrCaptain

Board Regular
Joined
Dec 5, 2006
Messages
123
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





 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,829
Office Version
  1. 2013
Platform
  1. Windows
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.
 

MrCaptain

Board Regular
Joined
Dec 5, 2006
Messages
123

ADVERTISEMENT

Ok, thanks for trying 'My Answer Is This', much appreciated.
Regards,
Vern
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,555
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.
 

MrCaptain

Board Regular
Joined
Dec 5, 2006
Messages
123

ADVERTISEMENT

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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,555
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.
 

MrCaptain

Board Regular
Joined
Dec 5, 2006
Messages
123
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,099
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top