Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

IT Guy: Jan 12, 2010; You people drink like you don't want to live!

Hoping someone can help me out here because I'm no Excel guru.

I have a user implementing a pop-up calendar control on several date cells so that anyone who opens the workbook can click the cell and click the date from the calendar.

All works fine until you share the workbook, then it pops up with an error box "Run-time error '1004': Unable to set the Left property of the OLEObject class"

If I unshare the workbook then everything works again.

# ¿ Jun 23, 2010 12:56

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 02:37

IT Guy: Jan 12, 2010; You people drink like you don't want to live!

nevermind

IT Guy fucked around with this message at 15:53 on Nov 1, 2010

# ¿ Nov 1, 2010 15:32

IT Guy: Jan 12, 2010; You people drink like you don't want to live!

What is the best way to check for matching cells in a range using VBA?

Example: I have a range of 10 cells, I want to check all 10 cells to see if any are the same.

e. Actually, I'm just going to post what I'm trying to do because I don't have a clue on how I'm going to accomplish this.

I have a table like so:

I want a msgbox to display if more than 1 cells in column B are "yes" AND the cells in column A (within the same rows of those cells in B that are "yes") are different dates.

Nothing needs to happen if the dates are the same and nothing needs to happen if the cells in B are "no"

This should be pretty easy but I have no idea how to do this.

e2.
So I think I may have an idea how to do this but I'm not sure on the syntax if someone could help me with this.

code:

Private Sub Worksheet_Change(ByVal Target As Range)

For each cell in column B
   If cell.value = "yes" Then
      array += cell.address
   End If
End For

If count(array) > 1 Then
   For each item in array
      array2 += adjacent cell in column A
   End For
End If

If array2 has duplicates Then
   msgbox "Working?"
End If

End Sub

I'll probably just change the msgbox to a conditional formatted cell because I don't want the msgbox popping up every time a cell changes.

IT Guy fucked around with this message at 17:13 on Nov 4, 2010

# ¿ Nov 4, 2010 15:44

IT Guy: Jan 12, 2010; You people drink like you don't want to live!

gwar3k1 posted:

Here's a sub that'll do what you want (identify duplicate positive dates):
code:
Sub DupCheck()
  bDuplicate = False
  sDates = ""
  For Each xCell In Range("B:B")
    If xCell.Value = "yes" Then
      If InStr(1, sDates, Cells(xCell.Row, 1)) > 0 Then
        bDuplicate = True
      Else
        sDates = sDates & Cells(xCell.Row, 1) & ","
      End If
    End If
  Next

  'bDuplicate is then the variable you want to check
End Sub
If you're using Excel >= 2007, there is a duplicate removal feature, if that's what this is hoping to do?

Actually, I made a mistake, I need to check array2 to see if every value is the same or unique.

Will that still work?

After Googling around, it appears the FREQUENCY() function may do what I need, I just don't know how to use it properly.

# ¿ Nov 4, 2010 18:56

IT Guy: Jan 12, 2010; You people drink like you don't want to live!

gwar3k1 posted:

No, the sub I posted will store a list of unique values and return true if there are any duplicates. You can validate by doing:

Thanks for your help on this. I don't think I'm explaining the situation properly or maybe I'm being dumb and not understanding what is going on here. It is alright if the dates are duplicated but if there are more than 1 distinct values then that is the problem.

I'll try to clear this up a bit. I need to do this:

code:

For Each c As Range("B1:B10") 
    If c.Value = "Yes" And Range(c.Address).Offset(0, -1).Value Is Not Null Then
         'This is where I need to dump the adjacent cell's (column A) value into an array and don't know how
    End If 
Next 
 
 'This is where I need to check if the array has more than 1 distinct unique value and don't know how

Duplicates are fine, but more than 1 distinct is not.

IT Guy fucked around with this message at 13:50 on Nov 5, 2010

# ¿ Nov 5, 2010 13:48

IT Guy: Jan 12, 2010; You people drink like you don't want to live!

Finally got it doing what I want.

Here is how I did it:

code:

Function inColl(value As Variant, coll As Collection)
    For Each v In coll
        If value = v Then
            inColl = True
        ElseIf value <> v Then
            inColl = False
        End If
    Next
End Function

Private Sub Worksheet_Change(ByVal target As Range)
        Dim coll As New Collection
        For Each c In Range("U4:U10")
            If c.value = "Yes" And Range(c.Address).Offset(0, -1).value <> "" And inColl(Range(c.Address).Offset(0, -1).value, coll) = False Then
                coll.Add Range(c.Address).Offset(0, -1).value
            End If
        Next
    
        If coll.Count > 1 And Range("L20").value = "" Then
            Range("L20").value = "Warning: Multiple dates in today's prepayment"
        ElseIf coll.Count <= 1 And Range("L20").value <> "" Then
            Range("L20").value = ""
        End If
End Sub

# ¿ Nov 5, 2010 15:37

IT Guy: Jan 12, 2010; You people drink like you don't want to live!

Zhentar posted:

I don't think inColl does what you think it does.

I noticed that too.

How should I fix that?

So let's say I choose Nov3 in the first cell
inColl will return false because there is nothing in the collection.

I choose Nov4 in the second cell.
inColl returns false because Nov4 <> Nov3

I choose Nov 3 in the third cell. (here is the problem right?)
inColl will be assigned true when it sees Nov3 = Nov3 the first item in the collection, but then when it checks it against the next item in the collection it will return false because Nov3 <> Nov4

However, the sheet seems to be working so I'm confused again.

Is this what you were referring to?

IT Guy fucked around with this message at 18:31 on Nov 5, 2010

# ¿ Nov 5, 2010 18:05

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 02:37

IT Guy: Jan 12, 2010; You people drink like you don't want to live!

Hey thanks, both of you.

# ¿ Nov 5, 2010 19:43

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread