Set Atama Sorunu

mustafaine1 · 6 Kasım 2009

Kod:

Private Sub ComboBox1_Change()
On Error Resume Next
Application.ScreenUpdating = False


fatura_tipi = ComboBox1.Value
referans = ComboBox2.Value
Set cover = Sheets("Cover")
Set numbers = Sheets("Numbers")
Set freight = Sheets("Freight")
Set demurrage = Sheets("Demurrage")
Set tcharter = Sheets("Time Charter")
Set extra = Sheets("Extra")
Set lumpsum = Sheets("Lumpsum")
Set wsfreight = Sheets("WS Freight")

If fatura_tipi = "FREIGHT" Then
freight.[h6].Clear
freight.[h4] = referans
freight.Select
freight.[c4] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 4, 0)
freight.[g5] = "DUE DATE:" & " " & Format(Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 15, 0), "dd.mm.yyyy")
freight.[g5].Font.Bold = True
freight.[g5].Characters(Start:=1, Length:=9).Font.FontStyle = Bold

freight.[c11] = "MESSRS:" & " " & "`" & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 40, 0) & "`"
freight.[c11].Font.Bold = True
freight.[c11].Characters(Start:=1, Length:=8).Font.FontStyle = Bold

freight.[c16] = "M/T" & " " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 32, 0)
freight.[c16].Font.Bold = True
freight.[c16].Characters(Start:=1, Length:=4).Font.FontStyle = Bold

freight.[E16] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 35, 0) & " - " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 36, 0)

freight.[C18] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 37, 0)
freight.[d18] = "MTS IN VAC"

freight.[E18] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 39, 0)

freight.[d20] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 33, 0)
freight.[d21] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 34, 0)

freight.[C26] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 37, 0)

freight.[d28] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 41, 0)

ComboBox1.Value = "** Fatura Tipini Seciniz"
ComboBox2.Value = "**Fatura Numarasi Seciniz"
ComboBox3.Value = "**Tum Faturalar"
ComboBox4.Value = "** Revize Fatura Tipini Seciniz"
End If


If fatura_tipi = "DEMURRAGE" Then
demurrage.[h6].Clear
demurrage.[h4] = referans
demurrage.Select

demurrage.[c4] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 4, 0)
demurrage.[g5] = "DUE DATE:" & " " & Format(Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 15, 0), "dd.mm.yyyy")
demurrage.[g5].Font.Bold = True
demurrage.[g5].Characters(Start:=1, Length:=9).Font.FontStyle = Bold

demurrage.[c11] = "MESSRS:" & " " & "`" & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 40, 0) & "`"
demurrage.[c11].Font.Bold = True
demurrage.[c11].Characters(Start:=1, Length:=8).Font.FontStyle = Bold

demurrage.[c16] = "M/T" & " " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 32, 0)
demurrage.[c16].Font.Bold = True
demurrage.[c16].Characters(Start:=1, Length:=4).Font.FontStyle = Bold

demurrage.[E16] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 35, 0) & " - " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 36, 0)

demurrage.[C18] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 37, 0)
demurrage.[d18] = "MTS IN VAC"

demurrage.[E18] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 39, 0)

demurrage.[d20] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 33, 0)
demurrage.[d21] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 34, 0)

demurrage.[H25] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 23, 0)

ComboBox1.Value = "** Fatura Tipini Seciniz"
ComboBox2.Value = "**Fatura Numarasi Seciniz"
ComboBox3.Value = "**Tum Faturalar"
ComboBox4.Value = "** Fatura Tipini Seciniz"
End If


If fatura_tipi = "TIME CHARTER" Then
tcharter.[h6].Clear
tcharter.[h4] = referans
tcharter.Select

tcharter.[c4] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 4, 0)
tcharter.[g5] = "DUE DATE:" & " " & Format(Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 15, 0), "dd.mm.yyyy")
tcharter.[g5].Font.Bold = True
tcharter.[g5].Characters(Start:=1, Length:=9).Font.FontStyle = Bold

tcharter.[c11] = "MESSRS:" & " " & "`" & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 40, 0) & "`"
tcharter.[c11].Font.Bold = True
tcharter.[c11].Characters(Start:=1, Length:=8).Font.FontStyle = Bold

tcharter.[c16] = "M/T" & " " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 32, 0)
tcharter.[c16].Font.Bold = True
tcharter.[c16].Characters(Start:=1, Length:=4).Font.FontStyle = Bold

tcharter.[F16] = "CHARTER PARTY DATED " & Format(Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 34, 0), "dd.mm.yyyy")
tcharter.[F16].Font.Bold = True
tcharter.[F16].Characters(Start:=1, Length:=20).Font.FontStyle = Bold

tcharter.[C18] = "ADVANCE FOR THE " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 26, 0) & " HIRE FROM " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 27, 0) & " TILL " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 26, 0)

tcharter.[C23] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 24, 0)
tcharter.[E23] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 25, 0)

ComboBox1.Value = "** Fatura Tipini Seciniz"
ComboBox2.Value = "**Fatura Numarasi Seciniz"
ComboBox3.Value = "**Tum Faturalar"
ComboBox4.Value = "** Revize Fatura Tipini Seciniz"

End If


If fatura_tipi = "EXTRA" Then
extra.[h6].Clear
extra.[h4] = referans
extra.Select

extra.[c4] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 4, 0)
extra.[g5] = "DUE DATE:" & " " & Format(Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 15, 0), "dd.mm.yyyy")
extra.[g5].Font.Bold = True
extra.[g5].Characters(Start:=1, Length:=9).Font.FontStyle = Bold

extra.[c11] = "MESSRS:" & " " & "`" & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 40, 0) & "`"
extra.[c11].Font.Bold = True
extra.[c11].Characters(Start:=1, Length:=8).Font.FontStyle = Bold

extra.[c16] = "M/T" & " " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 32, 0)
extra.[c16].Font.Bold = True
extra.[c16].Characters(Start:=1, Length:=4).Font.FontStyle = Bold

extra.[E16] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 35, 0) & " - " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 36, 0)

extra.[C18] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 37, 0)
extra.[C19] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 38, 0)

extra.[E18] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 39, 0)

extra.[d21] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 33, 0)
extra.[d22] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 34, 0)

extra.[C26] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 29, 0)

extra.[H26] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 23, 0)

ComboBox1.Value = "** Fatura Tipini Seciniz"
ComboBox2.Value = "**Fatura Numarasi Seciniz"
ComboBox3.Value = "**Tum Faturalar"
ComboBox4.Value = "** Revize Fatura Tipini Seciniz"

End If



If fatura_tipi = "LUMPSUM" Then
lumpsum.[h6].Clear
lumpsum.[h4] = referans
lumpsum.Select

lumpsum.[c4] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 4, 0)
lumpsum.[g5] = "DUE DATE:" & " " & Format(Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 15, 0), "dd.mm.yyyy")
lumpsum.[g5].Font.Bold = True
lumpsum.[g5].Characters(Start:=1, Length:=9).Font.FontStyle = Bold

lumpsum.[c9] = "MESSRS:" & " " & "`" & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 40, 0) & "`"
lumpsum.[c9].Font.Bold = True
lumpsum.[c9].Characters(Start:=1, Length:=8).Font.FontStyle = Bold

lumpsum.[c14] = "M/T" & " " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 32, 0)
lumpsum.[c14].Font.Bold = True
lumpsum.[c14].Characters(Start:=1, Length:=4).Font.FontStyle = Bold

lumpsum.[E14] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 35, 0) & " - " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 36, 0)

lumpsum.[c16] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 37, 0)
lumpsum.[C17] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 38, 0)

lumpsum.[E16] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 39, 0)

lumpsum.[d19] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 33, 0)
lumpsum.[d20] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 34, 0)

lumpsum.[H24] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 23, 0)

ComboBox1.Value = "** Fatura Tipini Seciniz"
ComboBox2.Value = "**Fatura Numarasi Seciniz"
ComboBox3.Value = "**Tum Faturalar"
ComboBox4.Value = "** Revize Fatura Tipini Seciniz"
End If


If fatura_tipi = "WS FREIGHT" Then
wsfreight.[h6].Clear
wsfreight.[I4] = referans
wsfreight.Select

wsfreight.[c4] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 4, 0)
wsfreight.[H5] = "DUE DATE:" & " " & Format(Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 15, 0), "dd.mm.yyyy")
wsfreight.[H5].Font.Bold = True
wsfreight.[H5].Characters(Start:=1, Length:=9).Font.FontStyle = Bold

wsfreight.[c10] = "MESSRS:" & " " & "`" & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 40, 0) & "`"
wsfreight.[c10].Font.Bold = True
wsfreight.[c10].Characters(Start:=1, Length:=8).Font.FontStyle = Bold

wsfreight.[c15] = "M/T" & " " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 32, 0)
wsfreight.[c15].Font.Bold = True
wsfreight.[c15].Characters(Start:=1, Length:=4).Font.FontStyle = Bold

wsfreight.[E15] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 35, 0) & " - " & Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 36, 0)

wsfreight.[C17] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 37, 0)

wsfreight.[E17] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 39, 0)

wsfreight.[d19] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 33, 0)
wsfreight.[d20] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 32, 0)

wsfreight.[C25] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 16, 0)
wsfreight.[C29] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 17, 0)
wsfreight.[E25] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 18, 0)
wsfreight.[E29] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 18, 0)
wsfreight.[G25] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 19, 0)
wsfreight.[G29] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 20, 0)

wsfreight.[E33] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 14, 0)
wsfreight.[I31] = -Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 21, 0)
wsfreight.[I35] = -Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 22, 0)

If wsfreight.[C29] = "" Then
wsfreight.[E29] = ""
wsfreight.[G29] = ""
wsfreight.Rows("27:30").Select
Selection.EntireRow.Hidden = True
Else
wsfreight.Rows("27:30").Select
Selection.EntireRow.Hidden = False
End If

If wsfreight.[E33] = "" Then
wsfreight.Rows("33:34").Select
Selection.EntireRow.Hidden = True
Else
wsfreight.Rows("33:34").Select
Selection.EntireRow.Hidden = False
End If

If wsfreight.[I31] >= 0 Then
wsfreight.Rows("31:32").Select
Selection.EntireRow.Hidden = True
Else
wsfreight.Rows("31:32").Select
Selection.EntireRow.Hidden = False
End If

If wsfreight.[I35] >= 0 Then
wsfreight.Rows("35:36").Select
Selection.EntireRow.Hidden = True
Else
wsfreight.Rows("35:36").Select
Selection.EntireRow.Hidden = False
End If

ComboBox1.Value = "** Fatura Tipini Seciniz"
ComboBox2.Value = "**Fatura Numarasi Seciniz"
ComboBox3.Value = "**Tum Faturalar"
ComboBox4.Value = "** Revize Fatura Tipini Seciniz"

End If

Bu sekilde bir kodum var arkadaslar fakat suan calismiyor; On Error Resume Next`i kaldirdigimda ilk asagidaki kodda hata veriyor, atamalari gormuyor olarak yorumladim ben nedeni ne olabilir

Kod:

freight.[c4] = Application.WorksheetFunction.VLookup(referans, numbers.Range("A:AM"), 4, 0)

Orion1 · 6 Kasım 2009

Fonksiyon değeri bulamayınca yok hatası üretiyor.O sebepten.

mustafaine1 · 6 Kasım 2009

IHH IIHH

bencede oyle

b:am arasi almaliymisim a:am oldugundanmis

Set Atama Sorunu

mustafaine1

Orion1

Uzman

mustafaine1