aydgur
Altın Üye
- Katılım
- 31 Ekim 2005
- Mesajlar
- 431
- Excel Vers. ve Dili
- Excel 2007 Türkçe
Herkese iyi günler dilerim.Bu Userformda süzmeyi B1 hücresine göre yapıyor.
B1 hücresinde BUGÜN() formulü var ve [xlFilterToday ] bu yüzden rapor aldığım günü süzüyor.
Ben 1 haftalık rapor almak istesem ( bugün+6 gün) olarak alabilmek için kod nasıl revize edilebilir ?
Private Sub UserForm_Initialize()
On Error Resume Next
Dim seguimiento As Long, i As Long
Dim Data() As Variant
Dim cell As Range
Sayfa7.Range("$A$1:$I$" & Sayfa7.Cells(Rows.Count, 2).End(xlUp).Row).AutoFilter Field:=2, Criteria1:= _
xlFilterToday, Operator:=xlFilterDynamic
With Me.ListBox1
.Clear
.ColumnCount = 8
.ColumnWidths = "60;100;100;100;60;60;60;100"
End With
i = 1
With Sayfa7
With .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeVisible) '<--| refer to non blank cells in its column "T" (i.e. with column index 20) from row 1 to last non blank one in column "B"
seguimiento = .Count
ReDim Data(1 To seguimiento + 1, 1 To Me.ListBox1.ColumnCount) '<--| redim data array rows accordingly (while setting columns to 5)
For Each cell In .Cells
i = i + 1
With cell
Data(i, 1) = Format(.Offset(, -1), "dd.mm.yy")
Data(i, 2) = .Offset(, 1) ' etc...
Data(i, 3) = Format(.Offset(, 2), "##,##0.00 TL")
Data(i, 4) = Format(.Offset(, 3), "##,##0.00 USD")
Data(i, 5) = Format(.Offset(, 4), "##,##0.00 EUR")
Data(i, 6) = .Offset(, 5)
Data(i, 7) = Format(.Offset(, 6), "dd.mm.yy")
Data(i, 8) = .Offset(, 7)
End With
Next cell
End With
End With
ListBox1.List = Data
Me.Label10.Caption = Format(CDate(Date), "dd.mm.yyyy")
End Sub
B1 hücresinde BUGÜN() formulü var ve [xlFilterToday ] bu yüzden rapor aldığım günü süzüyor.
Ben 1 haftalık rapor almak istesem ( bugün+6 gün) olarak alabilmek için kod nasıl revize edilebilir ?
Private Sub UserForm_Initialize()
On Error Resume Next
Dim seguimiento As Long, i As Long
Dim Data() As Variant
Dim cell As Range
Sayfa7.Range("$A$1:$I$" & Sayfa7.Cells(Rows.Count, 2).End(xlUp).Row).AutoFilter Field:=2, Criteria1:= _
xlFilterToday, Operator:=xlFilterDynamic
With Me.ListBox1
.Clear
.ColumnCount = 8
.ColumnWidths = "60;100;100;100;60;60;60;100"
End With
i = 1
With Sayfa7
With .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeVisible) '<--| refer to non blank cells in its column "T" (i.e. with column index 20) from row 1 to last non blank one in column "B"
seguimiento = .Count
ReDim Data(1 To seguimiento + 1, 1 To Me.ListBox1.ColumnCount) '<--| redim data array rows accordingly (while setting columns to 5)
For Each cell In .Cells
i = i + 1
With cell
Data(i, 1) = Format(.Offset(, -1), "dd.mm.yy")
Data(i, 2) = .Offset(, 1) ' etc...
Data(i, 3) = Format(.Offset(, 2), "##,##0.00 TL")
Data(i, 4) = Format(.Offset(, 3), "##,##0.00 USD")
Data(i, 5) = Format(.Offset(, 4), "##,##0.00 EUR")
Data(i, 6) = .Offset(, 5)
Data(i, 7) = Format(.Offset(, 6), "dd.mm.yy")
Data(i, 8) = .Offset(, 7)
End With
Next cell
End With
End With
ListBox1.List = Data
Me.Label10.Caption = Format(CDate(Date), "dd.mm.yyyy")
End Sub