PDA

View Full Version : Solved: skip value with error



danovkos
04-20-2011, 02:02 AM
Hi all,
pls. i try to figured out, how to skip value where is an error.
This errod handling doesnt help, or maybe i do something wrong.
on error resume next doesnt solve my problem because i dont want to skip whole cycle but only the one line
and
On Error GoTo label
doesnt works :(

here is part of my macro.
I try to fill variable in one line (some are with error). Where is error i want to skip this value (e.g. there can be added to value 0).


Open cesta & subor For Append As #1
pocetR = Worksheets(list).Range("b:b").Cells.SpecialCells(xlCellTypeConstants).Count + 2

' datum = Worksheets(list).Range("j1").Value 'dátum - mes. preradenia
' Print #1, "Preradenie neúverových pohľadávok ku: " & vbTab & vbTab & vbTab & datum

For ii = 1 To pocetR

If IsError(Worksheets(list).Range("b" & ii).Value) Then GoTo 1
b = Worksheets(list).Range("b" & ii).Value 'cif
1:
If IsError(Worksheets(list).Range("c" & ii).Value) Then GoTo 2
c = Worksheets(list).Range("c" & ii).Value 'meno
2:
On Error GoTo 3
d = Worksheets(list).Range("d" & ii).Value 'mandátka
3:
On Error GoTo 4
e = Worksheets(list).Range("e" & ii).Value 'trans date to mand
4:
On Error GoTo 5
f = Worksheets(list).Range("f" & ii).Value 'krieš
5:
On Error GoTo 6
g = Worksheets(list).Range("g" & ii).Value 'z mand na AK (názov)
6:
On Error GoTo 7
h = Worksheets(list).Range("h" & ii).Value 'z mand na AK (dátum)
7:
On Error GoTo 8
i = Worksheets(list).Range("i" & ii).Value 'mes. ukončenia
8:
On Error GoTo 9
j = Worksheets(list).Range("j" & ii).Value 'mes. ukončenia
9:
If IsError(Format((Worksheets(list).Range("k" & ii).Value), "##,##0.00")) Then GoTo 10
k = Format((Worksheets(list).Range("k" & ii).Value), "##,##0.00") 'stav pohľadávky
10:
On Error GoTo 11
l = Format((Worksheets(list).Range("l" & ii).Value), "##,##0.00") 'náklady na vymáhanie
11:
' n = Format((Worksheets(list).Range("n" & ii).Value), "##,##0.00") 'stav pohľadávky
Print #1, b & vbTab & c & vbTab & d & vbTab & e & vbTab & f & vbTab & g & vbTab & _
h & vbTab & i & vbTab & j & vbTab & k & vbTab & l
Next ii
'Loop
Close #1


any suggestions? :(

thx a lot for any help

Bob Phillips
04-20-2011, 02:36 AM
See if this helps



Open cesta & subor For Append As #1
pocetR = Worksheets(list).Range("b:b").Cells.SpecialCells(xlCellTypeConstants).Count + 2

' datum = Worksheets(list).Range("j1").Value 'dátum - mes. preradenia
' Print #1, "Preradenie neúverových pohladávok ku: " & vbTab & vbTab & vbTab & datum

For ii = 1 To pocetR

If Not IsError(Worksheets(list).Range("b" & ii).Value) Then _
b = Worksheets(list).Range("b" & ii).Value 'cif

If Not IsError(Worksheets(list).Range("c" & ii).Value) Then _
c = Worksheets(list).Range("c" & ii).Value 'meno

If Not IsError(Worksheets(list).Range("D" & ii).Value) Then _
d = Worksheets(list).Range("d" & ii).Value 'mandátka

If Not IsError(Worksheets(list).Range("E" & ii).Value) Then _
e = Worksheets(list).Range("e" & ii).Value 'trans date to mand

If Not IsError(Worksheets(list).Range("F" & ii).Value) Then _
f = Worksheets(list).Range("f" & ii).Value 'krieš

If Not IsError(Worksheets(list).Range("G" & ii).Value) Then _
g = Worksheets(list).Range("g" & ii).Value 'z mand na AK (názov)

If Not IsError(Worksheets(list).Range("H" & ii).Value) Then _
h = Worksheets(list).Range("h" & ii).Value 'z mand na AK (dátum)

If Not IsError(Worksheets(list).Range("I" & ii).Value) Then _
i = Worksheets(list).Range("i" & ii).Value 'mes. ukoncenia

If Not IsError(Worksheets(list).Range("J" & ii).Value) Then _
j = Worksheets(list).Range("j" & ii).Value 'mes. ukoncenia

If IsError(Format((Worksheets(list).Range("k" & ii).Value), "##,##0.00")) Then Goto 10
k = Format((Worksheets(list).Range("k" & ii).Value), "##,##0.00") 'stav pohladávky

If Not IsError(Worksheets(list).Range("L" & ii).Value) Then _
l = Format((Worksheets(list).Range("l" & ii).Value), "##,##0.00") 'náklady na vymáhanie

Print #1, b & vbTab & c & vbTab & d & vbTab & e & vbTab & f & vbTab & g & vbTab & _
h & vbTab & i & vbTab & j & vbTab & k & vbTab & l
Next ii
'Loop
Close #1

danovkos
04-20-2011, 03:28 AM
i tried your code, but it stops with error
"label not defined"
so i fix this line

If IsError(Format((Worksheets(list).Range("k" & ii).Value), "##,##0.00")) Then Goto 10
k = Format((Worksheets(list).Range("k" & ii).Value), "##,##0.00") 'stav pohladávky


like yours above


If Not IsError(Format((Worksheets(list).Range("k" & ii).Value), "##,##0.00")) Then _
k = Format((Worksheets(list).Range("k" & ii).Value), "##,##0.00") 'stav pohladávky


but it still return error
"type mismatch"

:dunno

Bob Phillips
04-20-2011, 03:58 AM
Where do you get that error?

danovkos
04-20-2011, 04:03 AM
exactly on this line


If Not IsError(Format((Worksheets(list).Range("k" & ii).Value), "##,##0.00")) Then _
k = Format((Worksheets(list).Range("k" & ii).Value), "##,##0.00") 'stav pohladávky

danovkos
04-20-2011, 06:31 AM
I fixed it base your code.

this are the right lines


If Not IsError(Worksheets(list).Range("k" & ii).Value) Then _
k = Format((Worksheets(list).Range("k" & ii).Value), "##,##0.00") 'stav pohladávky
If Not IsError(Worksheets(list).Range("L" & ii).Value) Then _
l = Format((Worksheets(list).Range("l" & ii).Value), "##,##0.00") 'náklady na vymáhanie


thx for help