Could someone please debug this short macro for me? I'm stuck!
 

What I'm trying to do for each in the range a2:a6 in a spreadsheet is to
store the date in it, then to search row 1 for the same date, and to put the
number 1 and colour sky blue into the cell which is in the same row as the
first instance of the date and the same column as the second instance of the
date, i.e. the intersection of first instance's row and the second instance's
column. Don't ask! Thanks for any help figuring how to fix run time error
91!

Option Explicit
Sub macro1()

Dim r As Integer
Dim c As Integer
Dim d As Date

For r = 2 To 6
d = Cells(r, 1).Value
Cells(1, 1).Select
Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
c = ActiveCell.Column
Cells(r, c).Select
ActiveCell.Value = 1
ActiveCell.Interior.ColorIndex = 33

Next r

End Sub

--
Clifford Middleton
Project Manager from UK

Could someone please debug this short macro for me? I'm stuck!
 

Change:
Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
To:
Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
--
Mike Q.


"Clifford Middleton" wrote:

What I'm trying to do for each in the range a2:a6 in a spreadsheet is to
store the date in it, then to search row 1 for the same date, and to put the
number 1 and colour sky blue into the cell which is in the same row as the
first instance of the date and the same column as the second instance of the
date, i.e. the intersection of first instance's row and the second instance's
column. Don't ask! Thanks for any help figuring how to fix run time error
91!

Option Explicit
Sub macro1()

Dim r As Integer
Dim c As Integer
Dim d As Date

For r = 2 To 6
d = Cells(r, 1).Value
Cells(1, 1).Select
Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
c = ActiveCell.Column
Cells(r, c).Select
ActiveCell.Value = 1
ActiveCell.Interior.ColorIndex = 33

Next r

End Sub

--
Clifford Middleton
Project Manager from UK

Could someone please debug this short macro for me? I'm stuck
 

Thank you but this doesn't seem to help. Macro stills gets run time error 91
'Object variable or With block variable not set.' Any ideas anyone? Thank
you.

C.

--
Clifford Middleton
Project Manager from UK


"Mike Q." wrote:

Change:
Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
To:
Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
--
Mike Q.


"Clifford Middleton" wrote:

What I'm trying to do for each in the range a2:a6 in a spreadsheet is to
store the date in it, then to search row 1 for the same date, and to put the
number 1 and colour sky blue into the cell which is in the same row as the
first instance of the date and the same column as the second instance of the
date, i.e. the intersection of first instance's row and the second instance's
column. Don't ask! Thanks for any help figuring how to fix run time error
91!

Option Explicit
Sub macro1()

Dim r As Integer
Dim c As Integer
Dim d As Date

For r = 2 To 6
d = Cells(r, 1).Value
Cells(1, 1).Select
Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
c = ActiveCell.Column
Cells(r, c).Select
ActiveCell.Value = 1
ActiveCell.Interior.ColorIndex = 33

Next r

End Sub

--
Clifford Middleton
Project Manager from UK

Could someone please debug this short macro for me? I'm stuck
 

Here is the the entire sub that works for me if you want to copy & paste. I
am using windows 2000 & Excel 2000

Option Explicit

Sub macro1()

Dim r As Integer
Dim c As Integer
Dim d As Date

For r = 2 To 6
d = Cells(r, 1).Value
Cells(1, 1).Select
Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
c = ActiveCell.Column
Cells(r, c).Select
ActiveCell.Value = 1
ActiveCell.Interior.ColorIndex = 33

Next r

End Sub
--
Mike Q.


"Clifford Middleton" wrote:

Thank you but this doesn't seem to help. Macro stills gets run time error 91
'Object variable or With block variable not set.' Any ideas anyone? Thank
you.

C.

--
Clifford Middleton
Project Manager from UK


"Mike Q." wrote:

Change:
Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
To:
Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
--
Mike Q.


"Clifford Middleton" wrote:

What I'm trying to do for each in the range a2:a6 in a spreadsheet is to
store the date in it, then to search row 1 for the same date, and to put the
number 1 and colour sky blue into the cell which is in the same row as the
first instance of the date and the same column as the second instance of the
date, i.e. the intersection of first instance's row and the second instance's
column. Don't ask! Thanks for any help figuring how to fix run time error
91!

Option Explicit
Sub macro1()

Dim r As Integer
Dim c As Integer
Dim d As Date

For r = 2 To 6
d = Cells(r, 1).Value
Cells(1, 1).Select
Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
c = ActiveCell.Column
Cells(r, c).Select
ActiveCell.Value = 1
ActiveCell.Interior.ColorIndex = 33

Next r

End Sub

--
Clifford Middleton
Project Manager from UK