dim

  • Thread starter Thread starter Atishoo
  • Start date Start date
A

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
 
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
Click to expand...


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" <[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
 
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)
 
Back
Top