Getting an Overflow and Type Mismatch Error for No Reason

[Legacy 389185]

I have a listbox like this ;

ID	yukleme_limani	yukleme_ulkesi	bosaltma_limani	bosaltma_ulkesi	arac_tipi	lokal_masraf	navlun	navlun2	doviz	nakliye_firmasi
1	GEBZE	TURKIYE	ATINA	YUNANISTAN	A	50	100	50	EUR	THYSEN
2	ATINA	YUNANISTAN	GEBZE	TURKIYE	C	20	200	50	EUR	THYSEN
3	GEBZE	TURKIYE	ROMA	ITALYA	D	50	300	20	EUR	CEEVA
4	AMBARLI	TURKIYE	ROMA	ITALYA	B	40	200	60	USD	CEEVA
5	GEBZE	TURKIYE	ROMA	ITALYA	F	30	400	100	USD	EKOL
6	ATINA	YUNANISTAN	ROMA	ITALYA	A	60	300	50	USD	EKOL
7	LEFKOSA	KIBRIS	ATINA	YUNANISTAN	A	40		200	EUR	CEEVA
8	CESME	TURKIYE	LEFKOSA	KIBRIS	B	20	100	50	USD	THYSEN
9	GEBZE	TURKIYE	LEFKOSA	KIBRIS	D	10	500	60	USD	EKOL
10	ATINA	YUNANISTAN	LEFKOSA	KIBRIS	C	40	300	100	EUR	EKOL
11	ROMA	ITALYA	GEBZE	TURKIYE	E	50	200	200	EUR	CEEVA
12	ROMA	ITALYA	AMBARLI	TURKIYE	F	60	500	500	USD	CEEVA
13	GEBZE	TURKIYE	ATINA	YUNANISTAN	E	70	100	20	USD	THYSEN
14	AMBARLI	TURKIYE	ATINA	YUNANISTAN	A	40	200	30	EUR	EKOL
15	ATINA	YUNANISTAN	ROMA	ITALYA	D	80	500	50	USD	CEEVA
16	LEFKOSA	YUNANISTAN	ROMA	ITALYA	A	20	200	60	USD	EKOL

<caption> list_navlun_kayit </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

I have a code which helps me to get average of "navlun" results according to different "yukleme limanı" data. For example when I type "Gebze" in the filtering "yukleme limanı" text box, the list turns into a 5 row list and it gets the average of that 5 results in a textbox called "navlun2015ort" and this is my code;

----------------------------------------------------------------------
Private Sub yukleme_limani_ara_Change()


Dim aranan As String


aranan = Forms!navlun_kayit!yukleme_limani_ara.Text
Forms!navlun_kayit!yukleme_limani_ara_gecici = aranan
list_navlun_kayit.Requery


Dim TpNav As Double, aa As Long,


For aa = 1 To (Me.list_navlun_kayit.ListCount - 1)

TpNav = TpNav + Nz(CDbl(Me.list_navlun_kayit.Column(7, aa)), 0)


Next aa
Me.navlun2015ort = Int(TpNav / (aa - 1))




End Sub
----------------------------------------------------------------------

So there are 2 problems;
1)When I type something else thats not on the list under "yukleme limanı" column, program gives me a overflow error and highlighting this "Me.navlun2015ort = Int(TpNav / (aa - 1))" in the code.How can I correct that?

2)For example in the list, you can see that the 7th row of "navlun" column is empty. I used the code;
" Nz(CDbl(Me.list_navlun_kayit.Column(7, aa)), 0) " to get a zero value instead of empty cells but I guess it doesnt work because it gives me a "type mismatch" error. So how can I avoid it?

Thanks for you help guys.

 

[Norie]

Couldn't you use DAvg to get the result?

 

[Legacy 389185]

Couldn't you use DAvg to get the result?

It doesnt work because the data comes from the list not a stabilized table.

 

[Norie]

How doesn't it work?

 

[Legacy 389185]

How doesn't it work?

With Davg function I have to select a domain like a table but in this example I have to get average from the listbox with the visible rows which are selected by the filter.

 

[Norie]

How are you populating the listbox?

 

[Legacy 389185]

I figured it out. I can teach you if you want amkçocu

 

[Norie]

Sure, that would be good.