Excel Programming Problem

[alexschiefert]

hi,

I´ve got a problem:
I programmed this sub:


Public Sub FalscherVorgang()

ArrayVorgangDefinieren
ArrayBausteinDefinieren
(these are the arrays i need to use further down)


Workbooks.Open("S:\SEKRETAR\Allgemein\Vorlagen\umwandlung zeiterf. öst-brd.xls")

For x = 5 To 981
Vorgangvorgabe(x) = Workbooks(3).Worksheets("Umrechentabelle").Cells(x, 1)
Next x

For y = 5 To 981
Bausteinvorgabe(y) = Workbooks(3).Worksheets("Umrechentabelle").Cells(y, 2)
Next y


for w = 5 To 981
If (Vorgang(0) = Vorgangvorgabe(w)) Then
Else
If (Vorgang(1) = Vorgangvorgabe(w)) Then
Else

and so on......

Else
MsgBox ("There is a mistake")

Next w

the problem is that it always displays the message box.....
even if i put a valid number in the Vorgang(0)-field and display a message-box if the values match, the mistake-msgbox is displayed.

can anybody help me?

thx, alex

[Ecniv]

Originally posted by alexschiefert

Public Sub FalscherVorgang()
 
ArrayVorgangDefinieren
ArrayBausteinDefinieren
 
 
Workbooks.Open("S:\SEKRETAR\Allgemein\Vorlagen\umwandlung zeiterf. öst-brd.xls")
 
For x = 5 To 981
Vorgangvorgabe(x) = Workbooks(3).Worksheets("Umrechentabelle").Cells(x, 1)
Next x
 
For y = 5 To 981
Bausteinvorgabe(y) = Workbooks(3).Worksheets("Umrechentabelle").Cells(y, 2)
Next y
 
 
[B]for w = 5 To 981[/B] 
[B]If (Vorgang(0) = Vorgangvorgabe(w)) Then
Else
If (Vorgang(1) = Vorgangvorgabe(w)) Then
Else
 
'and so on......
 
Else
MsgBox ("There is a mistake")
 
Next

Ok, so you are opening a sheet, putting two loadss of values in from said sheet, starting at row 5. This means 0-4 are empty.

Also you want to use a boolean (really you do) variable as aflag if its valid or not, then make a loop to check for you setting to false if its not valid... something like the following...

blnValid = true
    For lngX = 0 to 1000
        if not (aryOne(lngx)=aryTwo(lngx) then blnValid = false
    Next
 
    if blnValid then
          msgbox "fine"
    else
         msgbox "mistake"
    end if

Cuts your code down a bit too.

Vorgang array I assume is already set up??
When storing, if you put into the arrays using x-5 as a position you can do the loop I made above.

Possibly the error is a conversion error, in that the two values in the arrays don't match.

Post up if you get it working, or if you use this loop. Or if it still don't work

Vince

[Dave Sell]

for w = 5 To 981 
If (Vorgang(0) = Vorgangvorgabe(w)) Then
Else
If (Vorgang(1) = Vorgangvorgabe(w)) Then
Else
 
and so on......
 
Else
MsgBox ("There is a mistake")
 
Next w

Your Else / If statement could be wrong.

I think this:

If Condition Then
  Statement
Else
If Condition Then
  Statement
Else
  Statement
End If

Is different from this:

If Condition Then Statement
ElseIf Condition Then 
  Statement
ElseIf Condition Then 
  Statement
Else
  Statement
End If

Try the latter.