Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Debug Error | Excel Discussion (Misc queries) | |||
Locking debug mode in a macro | Excel Worksheet Functions | |||
How do I resolve debug error macro Excel 2000? At least, any lead | Excel Discussion (Misc queries) | |||
Macro getting stuck | Excel Programming | |||
Macro Debug | Excel Programming |