dim

[Atishoo]

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

 

[Lars-Åke Aspelin]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[Rick Rothstein]

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)