Need help! Excel & VBA trouble

[sveegaard]

Hi there.
I need VBA to compare several cells in Excel. It's rather difficault to explain the situation, but I'll try it.
Here's is the spreadsheet (| | represent a single cell):
| 0466-1-1-1-1 | 01 | - | 01 | - | 01 | - | 01 |
| 0466-1-1+1 | 01 | - | 01 | + | 01 |
| 0466-1-1 | 01 | - | 01 |

The first one is correct, the other 2 are wrong, while e.g.
| 0466-1-3+1 | 01 | - | 03 | + | 01 |

is correct.

Here's my code:

Sub chk()
Dim iCompare As Long
Dim iCheck As Long
Dim XL As Variant
Dim xlWS As Variant
Dim Checksum As Variant
Dim Compare As Variant
Dim Value_chk As Integer
Dim Kolonne As Variant
 
Set xlWS = Sheet3
 
iCompare = 3
iCheck = 2
Value_chk = 3
Kolonne = 7
 
Columns(Value_chk).Interior.Color = RGB(255, 255, 255)
 
Do While xlWS.Cells(iCheck, Value_chk).Value <> ""
    Do While xlWS.Cells(iCompare, Value_chk).Value <> ""
        Checksum = Cells(iCheck, Kolonne).Value
        Compare = Cells(iCompare, Kolonne).Value
        If Checksum = Compare Then
            GoTo Hvid
            GoTo Ring
            End If
Hvad:
            If Checksum <> Compare Then
            GoTo Her
            Else: GoTo Hvid
            End If
 
Her:
 
            If Kolonne <= 19 Then
            With Cells(iCheck, 3).Interior
            .Color = RGB(255, 0, 0)
            End With
            With Cells(iCompare, 3).Interior
            .Color = RGB(255, 0, 0)
            End With
            
           Else
           GoTo Ring
           End If
           
           
Hvid:
            
           iCompare = iCompare + 1
            
Ring:
         Loop
         
iCompare = iCheck + 2
iCheck = iCheck + 1
 
Loop
With Columns(Value_chk).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Columns(Value_chk).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Columns(Value_chk).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Columns(Value_chk).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Columns(Value_chk).Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
     
End Sub

Hope you understand me



Edit: Fixed [/vbcode] end tag. - Hack

[Hack]

Moved to Office Development

[RobDog888]

A couple of things,
Dont use GoTos they are not very good to use and makes your code hard to read.
What is it that your trying to compare? Are you just looking for blank cells?

[sveegaard]

I can't use anything else.
It's hard to explain. Any numbers that are shorter and contain the same numbers than e.g. 0466-1-1-1-1-1+1 are wrong, while a number such as 0466-1-3 is correct. Every number is seperated into their own cells.

[RobDog888]

So this is one row...

  A  | B | C | D | E | F | G
---------------------------
0466 | 1 | 1 | 1 | 1 | 1 | 1

[sveegaard]

Exactly - well, almost...there is a "-" between all numbers, except for the last two - there's a "+" that seperates them

[RobDog888]

Ok, but can you explain why they are wrong? It will make it easier to try to come up with some code for.

[Ecniv]

uh not sure what you mean .... but try:

=concatenate("0466-",<cellref>,<cellref>,<cellref>)

Or do you mean that your code has to split the first complete one, remove all the first digits to the '-' then shove all other elements (numbers and -/+ signs) into separate cells?

(if so then your first example you have said that the second two lines of is wrong, it isn't)

[sveegaard]

I was right that it was hard to explain This is the situation:

| A | B | C | D | E | F | G | H | I | J | K | L | M
---------------------------
0466 | - | 1 | - | 1 | - | 1 | - | 1 | - | 1 | + | 1
0466 | - | 1 | - | 1 |
0466 | - | 1 | | | | | | | | | + | 1
0466 | - | 1 | - | 3 |

Now, row no 1 is correct.
Row no 2 has the same numbers, but is too short - therefore it's wrong.
Same with row no 3, although it has a '+' instead af a '-'.
Row no 4 is correct, because at least one of it's numbers is different from the other rows

[sveegaard]

Is it confusing, or is the solution complicated? :S