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

On Sat, 29 May 2010 15:29:01 -0700, Atishoo
<(E-Mail Removed)> 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

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" <(E-Mail Removed)>
wrote in message news:68D5B1CC-F357-4236-AC5A-(E-Mail Removed)...
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

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" <(E-Mail Removed)> wrote in message
news:%23OT3Zd4$(E-Mail Removed)...
>
> 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" <(E-Mail Removed)>
> wrote in message
> news:68D5B1CC-F357-4236-AC5A-(E-Mail Removed)...
> 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
>