0

I have 2 columns, the first is a list of values I want to check against, the second is the description corresponding to the first column. the first column can have multiple instances of the same value with different descriptions in the second column. I want to print out all the different descriptions in a horizontal row. I have this code, which i can tell is referencing the correct cells by using debug.print, but the cells won't copy to the destination cell. 

Function MyFind(lookup As String, FindRng As Range)

Dim curcell As Range
Dim findcount As Integer

Set curcell = Application.Caller
findcount = 1


For Each celltocheck In FindRng
    If celltocheck = lookup Then
        Debug.Print celltocheck.Address
        Debug.Print celltocheck.Offset(0, 1).Address
        Debug.Print celltocheck.Offset(0, 1).Value
        Debug.Print curcell.Offset(0, findcount).Address
        celltocheck.Offset(0, 1).Copy Destination:=curcell.Offset(0, findcount)
        findcount = findcount + 1
    Else
    End If
Next

End Function
Aline
  • 25
  • 3
  • Is this a UDF that is being called directly from the worksheet? With a function you cannot change the value of other cells without some work around like [this](http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet). It is to change the color but can be changed to fit your needs. – Scott Craner Dec 17 '15 at 21:09
  • What is this supposed to do `Set curcell = Application.Caller`? You laster use `curcell` as a Range. Why isn't `curcell` set to something like `Set curcell = activecell`? – BruceWayne Dec 17 '15 at 21:22
  • 1
    @ pnuts - It wont copy at all, probably because of what @ Scott Craner said, its a UDF called from the worksheet. I wasn't aware you couldn't get it do change other cells. @BruceWayne, its exactly like that. – Aline Dec 17 '15 at 22:41

1 Answers1

1

Assuming the data to check (with the doubles) is in A1:A27 and the description in B1:B27 (the values you want to get) for the output, you could use:

=IFERROR(LARGE(ROW(A1:$A$27)*($A1:$A$27=$A1),COUNTIF($A1:$A$27,$A1)-COLUMN(A:A)+1),"")

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

then copy to the left and down... but you wil get doubles if there are some... Change the ranges to your need (skip the +1 to start with second value)

If you change your function to a sub and Set curcell = Selection, it should also work...

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
  • I needed to end up making it a sub. I was trying to make a UDF for the benefit of other people who will have to use it who are not familiar with vba. – Aline Dec 18 '15 at 13:40