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
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