Cell Range

[Matt]

I'm currently using this code to find my starting row for a range, how can I declare this as my starting row?

thanks

Matt

Cells.Find(What:="CURRENT MONTH TO DATE TOTALS", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate

 

[Dave Hawley]

Hi Matt

Just use:

Dim rStartRange As Range
Set rStartRange = Cells.Find(What:="CURRENT MONTH TO DATE TOTALS", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True)

 

[Matt]

Dave

I've tried to use the rStartRange as part of a macro for producing a chart but it is falling down on the "Set rStartRange" line. Can you spot why it is falling down, error messsage is Method 'Cells' of Object '_Global' failed

Sub SSChart()

Dim Cht As Chart
Dim Lastrow As Long
Dim CurrentSheet As String
Dim rStartRange As Range

Set rStartRange = Cells.Find(What:="CURRENT MONTH TO DATE TOTALS", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True)


CurrentSheet = ActiveSheet.Name


Set Cht = Application.Charts.Add
Lastrow = Sheets(CurrentSheet).Range("A100").End(xlUp).Row


With Cht
.ChartType = xlXYScatter
.SetSourceData Source:=Sheets(CurrentSheet).Range("A" & rStartRange & ":A" & Lastrow & ",D" & rStartRange & ":D" & Lastrow), PlotBy:=xlColumns
.SeriesCollection(1).XValues = Worksheets(CurrentSheet).Range("A" & rStartRange & ":A" & Lastrow)
.SeriesCollection(1).Values = Worksheets(CurrentSheet).Range("D" & rStartRange & ":D" & Lastrow)
.Location Where:=xlLocationAsObject, Name:=CurrentSheet
End With

End Sub

 

[Dave Hawley]

How are you running the macro, if via a CommandButton from the Control toolbox set it's "TakeFocusOnClick" property to false. Or better still use a CommandButton from the Forma toolbar.