0

I would like to automatically change the contents of cells when data is pasted into them. The data will be pasted into Column Q of a sheet on Excel. I need it to match it with data in Column A of another sheet, titled 'Users', and return the corresponding value from Column B of the same sheet. I presume it will require use of VBA, although I have very little understanding of VBA coding!

Any help will be very much appreciated!

6
  • 1
    Please edit your question to better explain what you are trying to do, including what you've already tried, then ask how to accomplish it. By including the method of solution in your question limits answers to only VBA solutions. There might be a non VBA solution.
    – CharlieRB
    Oct 13, 2014 at 15:35
  • Look up worksheet change event and let us know where you get stuck Oct 13, 2014 at 16:02
  • Do you mean that when you input certain value in column Q in sheet1, then Excel to "look up" that value in column A of sheet2 and return the value of column B of sheet 2 to sheet1. But in which column in sheet1?
    – Prasanna
    Oct 13, 2014 at 18:29
  • Please do not tell me that it should change the value in column Q in sheet1. Then this becomes a cyclic connection. Excel will throw a warning - in this case
    – Prasanna
    Oct 13, 2014 at 18:30
  • Hi guys, thanks for the comments. I've found a code for something which seems pretty much the same as I'm looking for in an answer to another question on this site: superuser.com/questions/541674/… I'm struggling to work out how to adapt it to the cells I'm working with. I've changed my layout a little bit. So the cells I'm pasting data into are in Column Q. The cells I want to lookup are in Column Z of the same sheet, and I would like it to return the corresponding value from column AA.
    – Alan
    Oct 15, 2014 at 8:44

1 Answer 1

1

I don't believe you can do what you want. There is no way (that I know of) to detect only pastes.

The closest is to use

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

But, this means it will fire every time any change on the page is made.

So, as a work around, what you can do is paste into Col Q and then manually run a macro (which you can assign to a button if easier).

Option Explicit

Sub MatchThePairs()

'You can edit this top bit

'The name of column you are pasting into
Dim pastedCol As String
pastedCol = "Q" 'UPDATE ME IF NEEDED

'The name of the look up column
Dim lookupCol As String
lookupCol = "Z" 'UPDATE ME IF NEEDED

'The name of the look to show results
Dim resultCol As String
resultCol = "AA" 'UPDATE ME IF NEEDED

'Do you want to clear the results first ?
Dim clearResults As Boolean
clearResults = True 'CHANGE ME TO True OR False

'What is the row of the header (if you have one)
Dim rowHeader As Integer
rowHeader = 1 ' set to 0 if no header

'What is the name of the results column
Dim resultsColHeader As String
resultsColHeader = "ResultsCol" ' Change me to what ever

'what is the first row (do not include the headings)
Dim row As Integer
row = 2 'AS PER THE SCEEN SHOT, I STARTED ON ROW 2



' **** hopefully you won't need to edit anything below this

If clearResults Then
    Range(resultCol & ":" & resultCol).Cells.Clear

    If rowHeader > 0 Then
       Range(resultCol & rowHeader).Value = resultsColHeader
    End If


End If

Do While (Range(pastedCol & row).Value <> "")

    If Range(pastedCol & row).Value = Range(lookupCol & row).Value Then
        'yipee, a match
        Range(resultCol & row).Value = Range(lookupCol & row).Value
    End If
    row = row + 1
Loop

End Sub

Before

enter image description here

After

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .