Run-Time error 1004

[Guest]

Frustration is setting in....

I am writing a macro to parse through a bunch of data, find a range of
consecutive cells that match a date, and eventually create a chart based on
that range. For now, however, I just want to highlight the range or change
it's color or something to make sure I am grabbing the correct info.

Here is what I have:

FoundFirstDate = False
FoundLastDate = False
Prompt1 = "Please enter the date for which to create charts"
Title1 = "Daily Chart Creation"
Default1 = Date

DateToChart = InputBox(Prompt1, Title1, Default1)
Worksheets("Chart Data").Activate
RowNum = 2
ColNum = 4
Cells(RowNum, ColNum).Select
'find first date
Do While Not FoundFirstDate And ActiveCell.Value <> ""
If ActiveCell.Value = DateToChart Then
FoundFirstDate = True
DateStart = RowNum
End If
RowNum = RowNum + 1
Cells(RowNum, ColNum).Select
Loop
'find last date
Do While Not FoundLastDate And ActiveCell.Value <> ""
If ActiveCell.Value <> DateToChart Then
FoundLastDate = True
DateFinish = RowNum - 1
End If
RowNum = RowNum + 1
Cells(RowNum, ColNum).Select
Loop
If Not FoundFirstDate Or Not FoundLastDate Then Exit Sub

'set the range
RangeName = "D" & DateStart & "" & DateFinish

Set DailyChartRange = ActiveSheet.Range(RangeName)
Worksheets("Chart Data").Range(DailyChartRange).Select
Selection.Font.ColorIndex = 3

I realize that I am probably doing this the hard way; however, I am not sure
of a better way to do this. Anyway, when the code executes, I receive a
run-time error 1004 and the code stops at the range selection line.

Any ideas?????????

Thanks in advance

 

[Jean-Yves]

Hi Don

Set DailyChartRange = ActiveSheet.Range(RangeName) 'DaylyChartRange is
an object, this is why you using Set
Worksheets("Chart Data").Range(DailyChartRange).Select ''
gives an error because you are passing 'DaylyChartRange as an argument
which should be a string, not an object
just use DailyChartRange.select , or Worksheets("Chart
Data").Range(DailyChartRange.name).Select
PS,
why do you select a page ? Just do what you want with it.
Regards

Jean-Yves