Solved: skip value with error

[danovkos]

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.
[vba]on error resume next[/vba] doesnt solve my problem because i dont want to skip whole cycle but only the one line
and
[vba]On Error GoTo label[/vba]
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).

[vba]
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
[/vba]

any suggestions?

thx a lot for any help

[Bob Phillips]

See if this helps

[vba]

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 [/vba]

[danovkos]

i tried your code, but it stops with error
"label not defined"
so i fix this line
[vba]
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
[/vba]

like yours above

[vba]
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
[/vba]

but it still return error
"type mismatch"

[Bob Phillips]

Where do you get that error?

[danovkos]

exactly on this line

[vba]
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
[/vba]

[danovkos]

I fixed it base your code.

this are the right lines

[VBA]
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
[/VBA]

thx for help