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

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

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

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