Copy and paste value automatically on finding matching value

[MrCaptain]

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

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

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

 

[My Aswer Is This]

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



​

 

[MrCaptain]

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]

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]

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

 

[mumps]

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.

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]

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]

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]

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

 

[mumps]

No problem.