Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dim
hi am tearing my hair out trying to find out whats wrong with my sub?
it errors out on the line "Worksheets("matchschedule").Cells(c.Column, d.Row) = e.Offset(0, 2)" with application or object defined error? Have I not dimensioned it correctly or something?? sub as follows: Private Sub CommandButton1_Click() Dim c As Range Dim d As Range Dim e As Range 'search for date For Each c In Worksheets("MatchSchedule").Range("C3:AX3") If c Worksheets("matchschedule").Range("A1") And c.Offset(0, -1) < Worksheets("matchschedule").Range("A1") Then 'search for country For Each d In Worksheets("matchschedule").Range("b9:b979") If d = c.Offset(2, 0) Then 'search for score With Worksheets("sheet5").Range("a1:a968") Set e = .Find(d.Offset(0, -1), LookIn:=xlValues, lookat:=xlWhole) If Not e Is Nothing Then Worksheets("matchschedule").Cells(c.Column, d.Row) = e.Offset(0, 2) End If End With End If Next d End If Next c End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dim
On Sat, 29 May 2010 15:29:01 -0700, Atishoo
wrote: hi am tearing my hair out trying to find out whats wrong with my sub? it errors out on the line "Worksheets("matchschedule").Cells(c.Column, d.Row) = e.Offset(0, 2)" with application or object defined error? Have I not dimensioned it correctly or something?? sub as follows: Private Sub CommandButton1_Click() Dim c As Range Dim d As Range Dim e As Range 'search for date For Each c In Worksheets("MatchSchedule").Range("C3:AX3") If c Worksheets("matchschedule").Range("A1") And c.Offset(0, -1) < Worksheets("matchschedule").Range("A1") Then 'search for country For Each d In Worksheets("matchschedule").Range("b9:b979") If d = c.Offset(2, 0) Then 'search for score With Worksheets("sheet5").Range("a1:a968") Set e = .Find(d.Offset(0, -1), LookIn:=xlValues, lookat:=xlWhole) If Not e Is Nothing Then Worksheets("matchschedule").Cells(c.Column, d.Row) = e.Offset(0, 2) End If End With End If Next d End If Next c End Sub Will it help to put an ".Value" at the end of the statement? Lars-Åke |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dim
It appears that the position of c.Column and d.Row should be reversed. -- Jim Cone Portland, Oregon USA ( Compare stuff: http://tinyurl.com/XLCompanion ) "Atishoo" wrote in message ... hi am tearing my hair out trying to find out whats wrong with my sub? it errors out on the line "Worksheets("matchschedule").Cells(c.Column, d.Row) = e.Offset(0, 2)" with application or object defined error? Have I not dimensioned it correctly or something?? sub as follows: Private Sub CommandButton1_Click() Dim c As Range Dim d As Range Dim e As Range 'search for date For Each c In Worksheets("MatchSchedule").Range("C3:AX3") If c Worksheets("matchschedule").Range("A1") And _ c.Offset(0, -1) < Worksheets("matchschedule").Range("A1") Then 'search for country For Each d In Worksheets("matchschedule").Range("b9:b979") If d = c.Offset(2, 0) Then 'search for score With Worksheets("sheet5").Range("a1:a968") Set e = .Find(d.Offset(0, -1), LookIn:=xlValues, lookat:=xlWhole) If Not e Is Nothing Then Worksheets("matchschedule").Cells(c.Column, d.Row) = e.Offset(0, 2) End If End With End If Next d End If Next c End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dim
Correct... the arguments to the Cells property (c.Column and c.Row) are
reversed. However, if we look carefully, we see that entire left side of the construction is unnecessary. Since the 'c' variable is drawn from the "matchschedule" sheet and since Cells(c.Row, c.Column) on the "matchschedule" sheet is nothing more than 'c' itself, the problem line reduces to this... c.Value = e.Offset(0, 2) -- Rick (MVP - Excel) "Jim Cone" wrote in message ... It appears that the position of c.Column and d.Row should be reversed. -- Jim Cone Portland, Oregon USA ( Compare stuff: http://tinyurl.com/XLCompanion ) "Atishoo" wrote in message ... hi am tearing my hair out trying to find out whats wrong with my sub? it errors out on the line "Worksheets("matchschedule").Cells(c.Column, d.Row) = e.Offset(0, 2)" with application or object defined error? Have I not dimensioned it correctly or something?? sub as follows: Private Sub CommandButton1_Click() Dim c As Range Dim d As Range Dim e As Range 'search for date For Each c In Worksheets("MatchSchedule").Range("C3:AX3") If c Worksheets("matchschedule").Range("A1") And _ c.Offset(0, -1) < Worksheets("matchschedule").Range("A1") Then 'search for country For Each d In Worksheets("matchschedule").Range("b9:b979") If d = c.Offset(2, 0) Then 'search for score With Worksheets("sheet5").Range("a1:a968") Set e = .Find(d.Offset(0, -1), LookIn:=xlValues, lookat:=xlWhole) If Not e Is Nothing Then Worksheets("matchschedule").Cells(c.Column, d.Row) = e.Offset(0, 2) End If End With End If Next d End If Next c End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|