I have a table in Excel, Tab_Visitas , which has visits per day of the month.
WhatIwantedtodowasalinechart,butonlywiththedateselectedandthedatebeforetheselecteddate,typethis:
Obtaining the dates I already have:
DataSelecionada:= DATA($Q$2;CORRESP($P$2;Tab_Meses;0);1)
DataAnterior:= DATAM(DataSelecionada;-1)
How do I put the chart to only display the data of the indicated month, as well as the previous one? (preferably with formulas only)
In your question it is not clear how you organized your data, but I understood that the chart is in a spreadsheet (tab) separate from that of the data and so you get the reference date from cell Q2 (now, after your most recent change, P2).
Finally, a possible possible solution is as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 17 Then
dCurDate = Target.Value
dPrevDate = DateAdd("m", -1, Target.Value)
Set oChart = ChartObjects(1)
With Sheets("Visitas")
Set oSearchRange = .Range("B4", .Range("B65536").End(xlUp))
Set oCurResp = oSearchRange.Find(What:=dCurDate, LookIn:=xlFormulas, LookAt:=xlWhole)
Set oPrevResp = oSearchRange.Find(What:=dPrevDate, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not oCurResp Is Nothing And Not oPrevResp Is Nothing Then
oChart.Chart.SetSourceData Source:=.Range(.Cells(oPrevResp.Row, 2), .Cells(oCurResp.Row, 34)), PlotBy:=xlRows
Else
MsgBox "Intervalo de datas não encontrado!"
End If
End With
End If
End Sub
It works as follows:
The function captures the change event of the worksheet ( Worksheet_Change ) and checks to see if the change occurred in row 2 cell 17 column (you have now moved to cell P2 just before it was in Q2 - was what I used in the example).
If this cell has changed, the code searches the worksheet data (tab) "Visits" (I called it here, I changed it according to your Excel file). The search range is column B, where the dates are. It does two searches: the current date and the previous date (it takes 1 month from the current date, similar to what you already did in your code).
If it finds both dates, it changes the data source on the chart (I considered it to be the first in the current spreadsheet - where you should add that code!) indicating that the data should be plotted horizontally are in the direction of the lines). Note that the range is defined by the lines found in the two previous searches and in the columns of days (2 through 34). Note also that nothing else is changed in the graph, only the data source. The axis data as well as the formatting is retained as before.