PDA

View Full Version : Solved: Custom Function for Dups



Blue_Bunny
02-27-2009, 12:46 PM
Hello All -

I'm having problems getting my function to work in copied cells. That is, my function works correctly if I click inside the parenthesis then click enter. But, if I double click in the lower right hand corner of the first cell that receives my function to copy it down the sheet, then I get the results from the first cell all the way down my copied selection (not what I want).

Right now my function does not accept arguments - I'm using activecell to manipulate 3 cells and I think this is my problem. I tried to use an argument to solve my issue, but that is not working either. In short, I'm trying to duplicate this worksheet formula in VBA:

=OR(IF(LEFT(D5,13)=LEFT(D4,13),1,0),IF(LEFT(D5,13)=LEFT(D6,13),1,0))

Here is the "working" code:

Function OrgDupTen() As String


Dim TopCell As String
Dim BotCell As String

OrgDupTen = 0
TopCell = ""
BotCell = ""


org = Left(ActiveCell.Offset(0, -1), 10)
TopCell = Left(ActiveCell.Offset(-1, -1), 10)
BotCell = Left(ActiveCell.Offset(1, -1), 10)

If org = TopCell Or org = BotCell Then OrgDupTen = 1

If OrgDupTen <> 1 Then OrgDupTen = 0

End Function


Here is what occurs if I enter the function in the first cell then double click:

A1 Inc 0
A2 Org 0
A2 Org 0
A2 Org 0
A3 Org 0
A4 LLC 0
A5 Inc 0
A5 Inc 0
A6 LLC 0
A7 Inc 0

Here is what I want to occur:

A1 Inc 0
A2 Org 1
A2 Org 1
A2 Org 1
A3 Org 0
A4 LLC 0
A5 Inc 1
A5 Inc 1
A6 LLC 0
A7 Inc 0

Thanks!
BB

mdmackillop
02-27-2009, 01:19 PM
Try = OrgDupTen(addr) where addr is the cell containing the function

Function OrgDupTen(Data As Range) As String
Dim TopCell As String
Dim BotCell As String
OrgDupTen = 0
TopCell = ""
BotCell = ""
org = Left(Data.Offset(0, -1), 10)
TopCell = Left(Data.Offset(-1, -1), 10)
BotCell = Left(Data.Offset(1, -1), 10)
If org = TopCell Or org = BotCell Then OrgDupTen = 1
If OrgDupTen <> 1 Then OrgDupTen = 0
End Function

Blue_Bunny
02-27-2009, 01:49 PM
Hello MD,

This worked great!

Thank you very much!

BB