CODEWITHSUNDEEP
excelvba
Sekar Naidu
Sekar Naidu

Reputation: 15

Filtering the months range in VBA by not using the ranges

I am new to VBA code, I have a drop down list with values from Jan to Dec. I have manually added the dates till Dec. In my current code I written like if I select Jan it will copy the range I entered . I need automatically it should pick the jan whole month dates, when I select Jan or any other months without specifying the range value. Is this possible?

For Each cel In Range("D2")

If cel.Value = "Jan" Then
Sheets("Actuals").Range("K1:AO1").Select
'copy required data to new sheet
Sheets("Actuals").Range("K1:AO1").Copy

Upvotes: 0

Views: 212

Answers (2)

Marcos Figueiredo
Marcos Figueiredo

Reputation: 1

I used Autofilter to find the results related to the month that the user selects through a ComboBox. Using two criteria to define the beginning and end of the search, then copying the results and pasting in another plan. It's not the most elegant solution but it might work in this case.

Dim A As Range
Dim i As Date
Dim f As Date
Dim m As Integer
With Worksheets("Banco de dados")

m = ComboBox1.ListIndex + 1

'Define data inical o final para filtrar de acordo com ano
i = "1/" & m & "/" & TextBox1.Value

On Error GoTo ajuste_mes
f = "31/" & m & "/" & TextBox1.Value

ajuste_mes:
    On Error GoTo -1
    On Error GoTo ajuste_mes2:
    f = "30/" & m & "/" & TextBox1.Value

ajuste_mes2:
    On Error GoTo -1
    On Error GoTo ajuste_mes3:
    f = "29/" & m & "/" & TextBox1.Value

ajuste_mes3:
    On Error GoTo -1
    f = "28/" & m & "/" & TextBox1.Value

Set A = ActiveSheet.Range("G:G")
A.AutoFilter Field:=1, Criteria1:=">=" & CDbl(i), _
               Operator:=xlAnd, Criteria2:="<=" & CDbl(f)

If Not A Is Nothing Then
  A.Activate
    Range(Selection, Selection.Offset(0, 2)).Select
    Selection.Copy
    Sheets("Gráficos").Select
    Range("AB11").Select
    ActiveSheet.Paste
    
'Apresenta qual é o mes e ano
Range("O33").Value = ComboBox1.List(ComboBox1.ListIndex)
Range("AB7").Value = TextBox1.Value

Upvotes: 0

CDP1802
CDP1802

Reputation: 16267

Calculate the start of range using datediff function and the end of range using end-of-month eomonth function.

Option Explicit
Function CalcAddress(yr As Integer, mth As String, Jan1 As String) As String

    Dim dt As Date, n As Integer, i As Integer

    ' set date to 1st of month
    dt = Format(yr & "-" & mth & "-01", "yyyy-mmm-dd")

    ' calc days in month
    n = Day(WorksheetFunction.EoMonth(dt, 0))

    ' calculate days from Jan 1
    i = DateDiff("d", DateSerial(yr, 1, 1), dt)
    i = i + Month(dt) - 1 ' add spacer columns

    CalcAddress = Range(Jan1).Offset(0, i).Resize(1, n).Address

End Function

Sub test()

    Dim yr As Integer, mth As String, addr As String

    yr = Year(Date) ' current year
    mth = InputBox("Enter month [Jan,Feb,Mar,..]")
    addr = CalcAddress(yr, mth, "K1")
    MsgBox "Range is " & addr, vbInformation, UCase(mth) & " - " & yr

    'Dim ws1 As Worksheet, ws2 As Worksheet
    'Set ws1 = ThisWorkbook.Sheets("Sheet1")
    'Set ws2 = ThisWorkbook.Sheets("Sheet2")
    'ws1.Range(addr).Copy ws2.Range(addr)

End Sub

Upvotes: 0

Related Questions