PDA

View Full Version : how to have a selection of last month



ffarshadd
06-14-2017, 12:47 AM
i have a date column and a price column. I'll attach the file
I wanna select all the prices of last month from 1st day to the last day.
sometimes there's no price for the first day so the selection must be started from second day if there's no second day it must be started from third day and...

Tom Jones
06-14-2017, 02:44 AM
I wanna select all the prices of last month from 1st day to the last day.

Define last month. It could be December? or month before actual month? or actual month?
witch year?
Also you have date doubled (for April 2017 ...)
it's not so clear what you want...

ffarshadd
06-14-2017, 04:51 AM
Define last month. It could be December? or month before actual month? or actual month?
witch year?
Also you have date doubled (for April 2017 ...)
it's not so clear what you want...
by saying last month i meant the previous month. for example we're in 6th month of 2017 i'll need to select the whole 5th month of 2017= 1st of may to 30th of may in 2017
I'll delete the date doubled by this code: Cells.RemoveDuplicates Columns:=Array(1)
imagine they don't exist.
thanx for answering

Paul_Hossler
06-14-2017, 07:12 AM
Try this -- I faked in some May 2017 data since that's the 'Last Month' and there was none in the test data




Option Explicit

Sub Macro1()
Dim r As Range, r1 As Range

Set r = ActiveSheet.Cells(1, 1).CurrentRegion

r.Cells(1, 1).AutoFilter
r.AutoFilter Field:=1, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic
Set r1 = r.SpecialCells(xlCellTypeVisible)

If r1.Areas.Count = 2 Then Set r1 = r1.Areas(2)

r1.Interior.Color = vbGreen

r.Parent.AutoFilterMode = False

r1.Select
End Sub

Tom Jones
06-14-2017, 07:26 AM
In CF put this formula

=AND(A2>=EOMONTH(TODAY(),-2)+1,A2<EOMONTH(TODAY(),-1))

ffarshadd
06-14-2017, 10:14 AM
Try this -- I faked in some May 2017 data since that's the 'Last Month' and there was none in the test data




Option Explicit

Sub Macro1()
Dim r As Range, r1 As Range

Set r = ActiveSheet.Cells(1, 1).CurrentRegion

r.Cells(1, 1).AutoFilter
r.AutoFilter Field:=1, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic
Set r1 = r.SpecialCells(xlCellTypeVisible)

If r1.Areas.Count = 2 Then Set r1 = r1.Areas(2)

r1.Interior.Color = vbGreen

r.Parent.AutoFilterMode = False

r1.Select
End Sub



thanx for answering but it didn't work

Tom Jones
06-14-2017, 10:43 AM
It works for me!
I mean Paul_Hossler code's.
In what way it didn't work? Error? Don't select wright cells?

Paul_Hossler
06-14-2017, 11:28 AM
thanx for answering but it didn't work

Details?

SamT
06-14-2017, 01:52 PM
Here is a solution that uses no worksheet functions
Option Explicit

Sub SelectLastMonthsPrices()
'Date / price columns must be sorted ascending before running sub

Dim FirstCel As Range
Dim LastCel As Range
Dim FirstDay As Date
Dim LastDay As Date
Dim Cel As Range

Dim WholePreviousMonthPrices As Range

'Range("A:B").Sort Header:=xlYes

FirstDay = DateAdd("m", -1, Format(Date, "mm/1/yyyy"))
LastDay = DateAdd("d", -1, Format(Date, "mm/1/yyyy"))

Set FirstCel = ActiveSheet.Cells(2, 1)
Do While FirstCel < FirstDay
Set FirstCel = FirstCel.Offset(1)
Loop

Set LastCel = FirstCel.End(xlDown)
Do While LastCel > LastDay
Set LastCel = LastCel.Offset(-1)
Loop

Set WholePreviousMonthPrices = Range(FirstCel, LastCel).Offset(0, 1)
WholePreviousMonthPrices.Select

End Sub