CODEWITHSUNDEEP
vbaexcel
Andreas
Andreas

Reputation: 23958

Cell border not "found"

I have a sheet that looks like this:

enter image description here

I want a VBA code to find customer number and "measure" how large the block is that it's in.

Example:
8887 has two columns.
8736 has two columns.
8602 has one column.

I use range.find to find the customer number and from there i loop a offset in columns to find where the "block" ends.
The block ends when it's not the same interior color, another customer is written in the cell or when a cell border is found. 

Set C = rng.Find(Search, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

If Not C Is Nothing Then
    FirstAddress = C.Address
    FirstColor = C.Interior.Color
    Do
        ' If it's just one cell width (column CJ) 
        If C.Borders(xlEdgeRight).LineStyle <> xlNone Then
            Platser = Platser & Mid(C.Address, 2) & "," 'append the locations string
        Else
            Platser = Platser & Mid(C.Address, 2) & "," 'append the locations string
            i = 1

            ' This is the loop that is causing problem. 
            'As I see it it should stop at i=1 but it keeps looping
            While C.Offset(0, i).Interior.Color = FirstColor And _
                                   C.Offset(0, i).Value = "" And _
                     C.Offset(0, i).Borders(xlEdgeRight).LineStyle <> xlNone
                'append the locations string
                Platser = Platser & Mid(C.Offset(0, i).Address, 2) & "," 
                i = i + 1
            Wend
        End If

        ' here I find the next cell with the same value, 
        '     but this has nothing to do with the problem.
        Set C = rng.Find(Search, _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            after:=C, _
            MatchCase:=False)
    Loop While Not C Is Nothing And FirstAddress <> C.Address

After this code i the cell addresses to get the numbers on row 3.
The output of the code above is:

364,363,362,361,360,359,358,357,356,354,354

So it finds the value 8736, but then it doesn't see the cell border which should make it stop looping.
What could cause this behavior?

Upvotes: 0

Views: 49

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

While C.Offset(0, i).Interior.Color = FirstColor And _
                                   C.Offset(0, i).Value = "" And _
                     C.Offset(0, i).Borders(xlEdgeRight).LineStyle <> xlNone

should be

While C.Offset(0, i).Interior.Color = FirstColor And _
                                   C.Offset(0, i).Value = "" And _
                     C.Offset(0, i).Borders(xlEdgeRight).LineStyle = xlNone

Upvotes: 1

Related Questions