Başlıklara göre gelir gider

[kelkitli]

Selamlar,
Ekteki Sayın Vedatelci nin hazırladığı dosyayı uyarladım kullanıyorum. Ancak Userformdaki Combobox1 deki tarihi seçince gelir gider durumlarını ilgili textboxlara yazmasını sağlamak istiyorum. Veri sayfası =12:O aralığında gelirler,P12 aralığında giderler var.

 

[kelkitli]

Yada şöyle nasıl yaparız. TextBox26 ya Combobox1 deki yılın Kasım ayına ait verileri C sütunundaki tarihe göre belirleyip O sütunundaki karşılıklarının toplamını almak.

 

[assenucler]

Dosyayı kullanamıyorum

Dostlar merhabalar;

Dosyayı açmak istediğimde aşağıdaki "Hata1, Hata2) iletilerini, VBA penceresindeyken F5'e tıkladığımda da "Hata3" mesajı çıkıyor.

Sorunu nasıl aşabilirim?

Yardımınızı bekliyorum.

 

[kelkitli]

Dtpicker eklemeniz lazım

 

[kelkitli]

sRangeA = Range("c12:c100").Address
sRangeB = Range("c12:c100").Address
sRangeC = Range("o12100").Address
Criter1 = Format(DateSerial(Year(Date), Month(Date), 1), "00000")
Criter2 = Format(DateSerial(Year(Date), Month(Date) + 1, 0), "00000")
TextBox26 = Evaluate("=SumProduct((" & sRangeA & ">=" & Criter1 & ")*(" & sRangeB & "<=" & Criter2 & ")*(" & sRangeC & "))")

Criter1 = Format(DateSerial(Year(Combobox1), Month(Date), 1), "00000")
Burada Combobox1 içerisindeki veri yıl olarak yazılacak. Nasıl yazayım. (Combobox1 de 2009,2010,2011,20012 şeklinde yazılı)

 

[kelkitli]

Şu şekilde oldu ama hata varmı bilmiyorum.

Criter1 = Format(DateSerial(Year("01.01." & ComboBox1), Month(Date), 1), "00000")
Criter2 = Format(DateSerial(Year("01.01." & ComboBox1), Month(Date) + 1, 0), "00000")

 

[kelkitli]

Değerlğ dostlar,
şu şekilde yaptım ama çok kalabalık bir yazım oldu.

Private Sub ComboBox1_Change()
sRangeA = Range("c12:c100").Address
sRangeB = Range("c12:c100").Address
sRangeC = Range("o12100").Address
sRangeD = Range("p12100").Address

Criter1 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.01." & ComboBox1), 1), "00000")
Criter2 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.01." & ComboBox1) + 1, 0), "00000")

Criter3 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.02." & ComboBox1), 1), "00000")
Criter4 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.02." & ComboBox1) + 1, 0), "00000")

Criter5 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.03." & ComboBox1), 1), "00000")
Criter6 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.03." & ComboBox1) + 1, 0), "00000")

Criter7 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.04." & ComboBox1), 1), "00000")
Criter8 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.04." & ComboBox1) + 1, 0), "00000")

Criter9 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.05." & ComboBox1), 1), "00000")
Criter10 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.05." & ComboBox1) + 1, 0), "00000")

Criter11 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.06." & ComboBox1), 1), "00000")
Criter12 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.06." & ComboBox1) + 1, 0), "00000")

Criter13 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.07." & ComboBox1), 1), "00000")
Criter14 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.07." & ComboBox1) + 1, 0), "00000")

Criter15 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.08." & ComboBox1), 1), "00000")
Criter16 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.08." & ComboBox1) + 1, 0), "00000")

Criter17 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.09." & ComboBox1), 1), "00000")
Criter18 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.09." & ComboBox1) + 1, 0), "00000")

Criter19 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.10." & ComboBox1), 1), "00000")
Criter20 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.10." & ComboBox1) + 1, 0), "00000")

Criter21 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.11." & ComboBox1), 1), "00000")
Criter22 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.11." & ComboBox1) + 1, 0), "00000")

Criter23 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.12." & ComboBox1), 1), "00000")
Criter24 = Format(DateSerial(Year("01.01." & ComboBox1), Month("01.12." & ComboBox1) + 1, 0), "00000")

TextBox6 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter1 & ")*(" & sRangeB & "<=" & Criter2 & ")*(" & sRangeC & "))"), "#,##0.00")
TextBox8 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter3 & ")*(" & sRangeB & "<=" & Criter4 & ")*(" & sRangeC & "))"), "#,##0.00")
TextBox10 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter5 & ")*(" & sRangeB & "<=" & Criter6 & ")*(" & sRangeC & "))"), "#,##0.00")
TextBox12 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter7 & ")*(" & sRangeB & "<=" & Criter8 & ")*(" & sRangeC & "))"), "#,##0.00")
TextBox14 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter9 & ")*(" & sRangeB & "<=" & Criter10 & ")*(" & sRangeC & "))"), "#,##0.00")
TextBox16 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter11 & ")*(" & sRangeB & "<=" & Criter12 & ")*(" & sRangeC & "))"), "#,##0.00")
TextBox18 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter13 & ")*(" & sRangeB & "<=" & Criter14 & ")*(" & sRangeC & "))"), "#,##0.00")
TextBox20 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter15 & ")*(" & sRangeB & "<=" & Criter16 & ")*(" & sRangeC & "))"), "#,##0.00")
TextBox22 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter17 & ")*(" & sRangeB & "<=" & Criter18 & ")*(" & sRangeC & "))"), "#,##0.00")
TextBox24 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter19 & ")*(" & sRangeB & "<=" & Criter20 & ")*(" & sRangeC & "))"), "#,##0.00")
TextBox26 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter21 & ")*(" & sRangeB & "<=" & Criter22 & ")*(" & sRangeC & "))"), "#,##0.00")
TextBox28 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter23 & ")*(" & sRangeB & "<=" & Criter24 & ")*(" & sRangeC & "))"), "#,##0.00")

TextBox5 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter1 & ")*(" & sRangeB & "<=" & Criter2 & ")*(" & sRangeD & "))"), "#,##0.00")
TextBox7 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter3 & ")*(" & sRangeB & "<=" & Criter4 & ")*(" & sRangeD & "))"), "#,##0.00")
TextBox9 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter5 & ")*(" & sRangeB & "<=" & Criter6 & ")*(" & sRangeD & "))"), "#,##0.00")
TextBox11 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter7 & ")*(" & sRangeB & "<=" & Criter8 & ")*(" & sRangeD & "))"), "#,##0.00")
TextBox13 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter9 & ")*(" & sRangeB & "<=" & Criter10 & ")*(" & sRangeD & "))"), "#,##0.00")
TextBox15 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter11 & ")*(" & sRangeB & "<=" & Criter12 & ")*(" & sRangeD & "))"), "#,##0.00")
TextBox17 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter13 & ")*(" & sRangeB & "<=" & Criter14 & ")*(" & sRangeD & "))"), "#,##0.00")
TextBox19 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter15 & ")*(" & sRangeB & "<=" & Criter16 & ")*(" & sRangeD & "))"), "#,##0.00")
TextBox21 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter17 & ")*(" & sRangeB & "<=" & Criter18 & ")*(" & sRangeD & "))"), "#,##0.00")
TextBox23 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter19 & ")*(" & sRangeB & "<=" & Criter20 & ")*(" & sRangeD & "))"), "#,##0.00")
TextBox25 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter21 & ")*(" & sRangeB & "<=" & Criter22 & ")*(" & sRangeD & "))"), "#,##0.00")
TextBox27 = Format(Evaluate("=SumProduct((" & sRangeA & ">=" & Criter23 & ")*(" & sRangeB & "<=" & Criter24 & ")*(" & sRangeD & "))"), "#,##0.00")

TextBox29 = Format(CDbl(TextBox27) + CDbl(TextBox25) + CDbl(TextBox23) + CDbl(TextBox21) + CDbl(TextBox19) + CDbl(TextBox17) + CDbl(TextBox15) + CDbl(TextBox13) + CDbl(TextBox11) + CDbl(TextBox9) + CDbl(TextBox7) + CDbl(TextBox5), "#,##0.00")
TextBox30 = Format(CDbl(TextBox28) + CDbl(TextBox26) + CDbl(TextBox24) + CDbl(TextBox22) + CDbl(TextBox20) + CDbl(TextBox18) + CDbl(TextBox16) + CDbl(TextBox14) + CDbl(TextBox12) + CDbl(TextBox10) + CDbl(TextBox8) + CDbl(TextBox6), "#,##0.00")

End Sub

Sadeleştiremedim.