Dear Experts, in my quest to explore macro's I am currently lost. I
learn mostly by creative copy pasting and a lot of trail and
error... ;-) However, i am... stuck. Is there anyone who can review my
code and tell me why I get an error telling me the 'Object does not
support the property or method'. The lines generating this error have
been put between
(I copied this from a sheet where i use this also and it does work!)

Sub Write_Results()

Dim sDateExtract As String
Dim FoundCell As Range
Dim lFiledate As Long
Dim rLastSplitsPerAI As Range
Dim LSPAI As Long
Dim sFileNameResults As String

0 If lFiledate = 0 Then
lFiledate = InputBox("Enter date of reportation. (yyyymmdd)")
MsgBox "You will create the SE Report for " & lFiledate
Else
End If

sFileNameResults = "SE statistiek " & lFiledate & ".xls"
LSPAI = 126
Set rLastSplitsPerAI = Range(Cells(3, 1), Cells(3, LSPAI))
sDateExtract = Mid(lFiledate, 5, 2) & "/" & Right(lFiledate, 2) & "/"
& Left(lFiledate, 4)
MsgBox sDateExtract
Windows("overview_f.xls").Activate
Sheets("SPAI").Select

Set FoundCell = Range("A1:A800").Find _
(What:=DateValue(sDateExtract), LookIn:=xlFormulas)
If FoundCell < "" Then


Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value


Else
MsgBox "Oops... something went wrong, check things manually plz."
End If

End Sub

firstly, unless i'm blind i didn't find any 's. :) so i don't
know where it's erroring.

a first thought is shouldn't Dim lFiledate As Long be Dim IFiledate as
Date?

0 If lFiledate = 0 Then why is the first zero there? that would make it error.

i have some possible ideas about sDateExtract but i'm not sure so i'll
keep my mouth shut for now about those..... :)
hope this helps
susan
(ps - & i learn most of my vba thru trial & error, too!)


On Mar 23, 8:34 am, "artisdepartis" wrote:
Dear Experts, in my quest to explore macro's I am currently lost. I
learn mostly by creative copy pasting and a lot of trail and
error... ;-) However, i am... stuck. Is there anyone who can review my
code and tell me why I get an error telling me the 'Object does not
support the property or method'. The lines generating this error have
been put between
(I copied this from a sheet where i use this also and it does work!)

Sub Write_Results()

Dim sDateExtract As String
Dim FoundCell As Range
Dim lFiledate As Long
Dim rLastSplitsPerAI As Range
Dim LSPAI As Long
Dim sFileNameResults As String

0 If lFiledate = 0 Then
lFiledate = InputBox("Enter date of reportation. (yyyymmdd)")
MsgBox "You will create the SE Report for " & lFiledate
Else
End If

sFileNameResults = "SE statistiek " & lFiledate & ".xls"
LSPAI = 126
Set rLastSplitsPerAI = Range(Cells(3, 1), Cells(3, LSPAI))
sDateExtract = Mid(lFiledate, 5, 2) & "/" & Right(lFiledate, 2) & "/"
& Left(lFiledate, 4)
MsgBox sDateExtract
Windows("overview_f.xls").Activate
Sheets("SPAI").Select

Set FoundCell = Range("A1:A800").Find _
(What:=DateValue(sDateExtract), LookIn:=xlFormulas)
If FoundCell < "" Then



Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value



Else
MsgBox "Oops... something went wrong, check things manually plz."
End If

End Sub

Susan, thx for pointing out my omission of the :|

The reasons I don't Dim lFileDate as Date is the trouble I hate with
working with dates in Excel. (Some of my collegues use the European,
others the American system. Besides... I use the string in my SaveAs
macro.
I do not know how that darn 0 got there either... -_-

Let me repost my code, with the I can select the ranges mentioned
in between just fine, but i cant copy paste or Range.Value =
Range .Value


Sub Write_Results()

Dim sDateExtract As String
Dim FoundCell As Range
Dim lFiledate As Long
Dim rLastSplitsPerAI As Range
Dim LSPAI As Long
Dim sFileNameResults As String

If lFiledate = 0 Then
lFiledate = InputBox("Enter date of reportation. (yyyymmdd)")
MsgBox "You will create the SE Report for " & lFiledate
Else
End If

sFileNameResults = "SE statistiek " & lFiledate & ".xls"
LSPAI = 126
Set rLastSplitsPerAI = Range(Cells(3, 1), Cells(3, LSPAI))
sDateExtract = Mid(lFiledate, 5, 2) & "/" & Right(lFiledate, 2) & "/"
& Left(lFiledate, 4)
MsgBox sDateExtract
Windows("overview_f.xls").Activate
Sheets("SPAI").Select

Set FoundCell = Range("A1:A800").Find _
(What:=DateValue(sDateExtract), LookIn:=xlFormulas)
If FoundCell < "" Then


Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value



Else
MsgBox "Oops... something went wrong, check things manually plz."
End If

End Sub




On Mar 23, 1:59 pm, "Susan" wrote:
firstly, unless i'm blind i didn't find any 's. :) so i don't|
know where it's erroring.

a first thought is shouldn't Dim lFiledate As Long be Dim IFiledate as
Date?

0 If lFiledate = 0 Then why is the first zero there? that would make it error.

i have some possible ideas about sDateExtract but i'm not sure so i'll
keep my mouth shut for now about those..... :)
hope this helps
susan
(ps - & i learn most of my vba thru trial & error, too!)

On Mar 23, 8:34 am, "artisdepartis" wrote:



Dear Experts, in my quest to explore macro's I am currently lost. I
learn mostly by creative copy pasting and a lot of trail and
error... ;-) However, i am... stuck. Is there anyone who can review my
code and tell me why I get an error telling me the 'Object does not
support the property or method'. The lines generating this error have
been put between
(I copied this from a sheet where i use this also and it does work!)

Sub Write_Results()

Dim sDateExtract As String
Dim FoundCell As Range
Dim lFiledate As Long
Dim rLastSplitsPerAI As Range
Dim LSPAI As Long
Dim sFileNameResults As String

0 If lFiledate = 0 Then
lFiledate = InputBox("Enter date of reportation. (yyyymmdd)")
MsgBox "You will create the SE Report for " & lFiledate
Else
End If

sFileNameResults = "SE statistiek " & lFiledate & ".xls"
LSPAI = 126
Set rLastSplitsPerAI = Range(Cells(3, 1), Cells(3, LSPAI))
sDateExtract = Mid(lFiledate, 5, 2) & "/" & Right(lFiledate, 2) & "/"
& Left(lFiledate, 4)
MsgBox sDateExtract
Windows("overview_f.xls").Activate
Sheets("SPAI").Select

Set FoundCell = Range("A1:A800").Find _
(What:=DateValue(sDateExtract), LookIn:=xlFormulas)
If FoundCell < "" Then

Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value

Else
MsgBox "Oops... something went wrong, check things manually plz."
End If

End Sub- Hide quoted text -

- Show quoted text -

LOL you still forgot the 's!

but, since you mentioned the selects & the pasting, it gives me a
better idea where to look......... (in this area, i believe):

Sheets("SPAI").Select

Set FoundCell = Range("A1:A800").Find _
(What:=DateValue(sDateExtract), LookIn:=xlFormulas)
If FoundCell < "" Then
Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value


Find (What:=DateValue(sDateExtract)

you don't have DateValue dimmed as a variable......
although i'm thinking perhaps that's a VBA object or something?
if not, then you need to declare that.
also, you're looking for sDateExtract in the formulas...
try values?

also, i don't see where you're copying or pasting anything.....
you find this datevalue, & then you tell a range in worksheet
"overview" that it's value should come from a range in another
worksheet.

see, trial & error!
susan



On Mar 23, 9:31 am, "artisdepartis" wrote:
Susan, thx for pointing out my omission of the :|

The reasons I don't Dim lFileDate as Date is the trouble I hate with
working with dates in Excel. (Some of my collegues use the European,
others the American system. Besides... I use the string in my SaveAs
macro.
I do not know how that darn 0 got there either... -_-

Let me repost my code, with the I can select the ranges mentioned
in between just fine, but i cant copy paste or Range.Value =
Range .Value

Sub Write_Results()

Dim sDateExtract As String
Dim FoundCell As Range
Dim lFiledate As Long
Dim rLastSplitsPerAI As Range
Dim LSPAI As Long
Dim sFileNameResults As String

If lFiledate = 0 Then
lFiledate = InputBox("Enter date of reportation. (yyyymmdd)")
MsgBox "You will create the SE Report for " & lFiledate
Else
End If

sFileNameResults = "SE statistiek " & lFiledate & ".xls"
LSPAI = 126
Set rLastSplitsPerAI = Range(Cells(3, 1), Cells(3, LSPAI))
sDateExtract = Mid(lFiledate, 5, 2) & "/" & Right(lFiledate, 2) & "/"
& Left(lFiledate, 4)
MsgBox sDateExtract
Windows("overview_f.xls").Activate
Sheets("SPAI").Select

Set FoundCell = Range("A1:A800").Find _
(What:=DateValue(sDateExtract), LookIn:=xlFormulas)
If FoundCell < "" Then



Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value



Else
MsgBox "Oops... something went wrong, check things manually plz."
End If

End Sub

On Mar 23, 1:59 pm, "Susan" wrote:



firstly, unless i'm blind i didn't find any 's. :) so i don't|
know where it's erroring.

a first thought is shouldn't Dim lFiledate As Long be Dim IFiledate as
Date?

0 If lFiledate = 0 Then why is the first zero there? that would make it error.

i have some possible ideas about sDateExtract but i'm not sure so i'll
keep my mouth shut for now about those..... :)
hope this helps
susan
(ps - & i learn most of my vba thru trial & error, too!)

On Mar 23, 8:34 am, "artisdepartis" wrote:

Dear Experts, in my quest to explore macro's I am currently lost. I
learn mostly by creative copy pasting and a lot of trail and
error... ;-) However, i am... stuck. Is there anyone who can review my
code and tell me why I get an error telling me the 'Object does not
support the property or method'. The lines generating this error have
been put between
(I copied this from a sheet where i use this also and it does work!)

Sub Write_Results()

Dim sDateExtract As String
Dim FoundCell As Range
Dim lFiledate As Long
Dim rLastSplitsPerAI As Range
Dim LSPAI As Long
Dim sFileNameResults As String

0 If lFiledate = 0 Then
lFiledate = InputBox("Enter date of reportation. (yyyymmdd)")
MsgBox "You will create the SE Report for " & lFiledate
Else
End If

sFileNameResults = "SE statistiek " & lFiledate & ".xls"
LSPAI = 126
Set rLastSplitsPerAI = Range(Cells(3, 1), Cells(3, LSPAI))
sDateExtract = Mid(lFiledate, 5, 2) & "/" & Right(lFiledate, 2) & "/"
& Left(lFiledate, 4)
MsgBox sDateExtract
Windows("overview_f.xls").Activate
Sheets("SPAI").Select

Set FoundCell = Range("A1:A800").Find _
(What:=DateValue(sDateExtract), LookIn:=xlFormulas)
If FoundCell < "" Then

Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value

Else
MsgBox "Oops... something went wrong, check things manually plz."
End If

End Sub- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Susan, im giving up on the s (This time they were in my code for
sure when i copied it to GoogleGroups... ;-)

You are right, the error occurs in this bit:
Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value

I checked and the setting of FoundCell works fine, even though i dont
completely get why... I had the same thoughts as you on that, but this
works... so that is okay enough, even though i dont fully understand
it... (Timepressure :S)
This FoundCell basically is the Row on which I want to insert my
results in my overview file. The reason i want to do it like this, is
I want to avoid the Clipboard. (Our system is VERY slow).

To give you an idea:
copying it via
Workbooks(sFileNameResults).Sheets("SPLITS PER AI").Range(Cells(3, 1),
Cells(3, LSPAI)).Select
Selection.Copy

and pasting it via
FoundCell.Offset(0, 1).Select
ActiveCell.PasteSpecial xlPasteValues

Does work, but since i need to do these with more data this becomes
very code and memory inefficient...

Thx for thinking along ^^

On Mar 23, 2:49 pm, "Susan" wrote:
LOL you still forgot the 's!

but, since you mentioned the selects & the pasting, it gives me a
better idea where to look......... (in this area, i believe):

Sheets("SPAI").Select

Set FoundCell = Range("A1:A800").Find _
(What:=DateValue(sDateExtract), LookIn:=xlFormulas)
If FoundCell < "" Then
Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value

Find (What:=DateValue(sDateExtract)

you don't have DateValue dimmed as a variable......
although i'm thinking perhaps that's a VBA object or something?
if not, then you need to declare that.
also, you're looking for sDateExtract in the formulas...
try values?

also, i don't see where you're copying or pasting anything.....
you find this datevalue, & then you tell a range in worksheet
"overview" that it's value should come from a range in another
worksheet.

see, trial & error!
susan

On Mar 23, 9:31 am, "artisdepartis" wrote:



Susan, thx for pointing out my omission of the :|

The reasons I don't Dim lFileDate as Date is the trouble I hate with
working with dates in Excel. (Some of my collegues use the European,
others the American system. Besides... I use the string in my SaveAs
macro.
I do not know how that darn 0 got there either... -_-

Let me repost my code, with the I can select the ranges mentioned
in between just fine, but i cant copy paste or Range.Value =
Range .Value

Sub Write_Results()

Dim sDateExtract As String
Dim FoundCell As Range
Dim lFiledate As Long
Dim rLastSplitsPerAI As Range
Dim LSPAI As Long
Dim sFileNameResults As String

If lFiledate = 0 Then
lFiledate = InputBox("Enter date of reportation. (yyyymmdd)")
MsgBox "You will create the SE Report for " & lFiledate
Else
End If

sFileNameResults = "SE statistiek " & lFiledate & ".xls"
LSPAI = 126
Set rLastSplitsPerAI = Range(Cells(3, 1), Cells(3, LSPAI))
sDateExtract = Mid(lFiledate, 5, 2) & "/" & Right(lFiledate, 2) & "/"
& Left(lFiledate, 4)
MsgBox sDateExtract
Windows("overview_f.xls").Activate
Sheets("SPAI").Select

Set FoundCell = Range("A1:A800").Find _
(What:=DateValue(sDateExtract), LookIn:=xlFormulas)
If FoundCell < "" Then

Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value

Else
MsgBox "Oops... something went wrong, check things manually plz."
End If

End Sub

On Mar 23, 1:59 pm, "Susan" wrote:

firstly, unless i'm blind i didn't find any 's. :) so i don't|
know where it's erroring.

a first thought is shouldn't Dim lFiledate As Long be Dim IFiledate as
Date?

0 If lFiledate = 0 Then why is the first zero there? that would make it error.

i have some possible ideas about sDateExtract but i'm not sure so i'll
keep my mouth shut for now about those..... :)
hope this helps
susan
(ps - & i learn most of my vba thru trial & error, too!)

On Mar 23, 8:34 am, "artisdepartis" wrote:

Dear Experts, in my quest to explore macro's I am currently lost. I
learn mostly by creative copy pasting and a lot of trail and
error... ;-) However, i am... stuck. Is there anyone who can review my
code and tell me why I get an error telling me the 'Object does not
support the property or method'. The lines generating this error have
been put between
(I copied this from a sheet where i use this also and it does work!)

Sub Write_Results()

Dim sDateExtract As String
Dim FoundCell As Range
Dim lFiledate As Long
Dim rLastSplitsPerAI As Range
Dim LSPAI As Long
Dim sFileNameResults As String

0 If lFiledate = 0 Then
lFiledate = InputBox("Enter date of reportation. (yyyymmdd)")
MsgBox "You will create the SE Report for " & lFiledate
Else
End If

sFileNameResults = "SE statistiek " & lFiledate & ".xls"
LSPAI = 126
Set rLastSplitsPerAI = Range(Cells(3, 1), Cells(3, LSPAI))
sDateExtract = Mid(lFiledate, 5, 2) & "/" & Right(lFiledate, 2) & "/"
& Left(lFiledate, 4)
MsgBox sDateExtract
Windows("overview_f.xls").Activate
Sheets("SPAI").Select

Set FoundCell = Range("A1:A800").Find _
(What:=DateValue(sDateExtract), LookIn:=xlFormulas)
If FoundCell < "" Then

Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value

Else
MsgBox "Oops... something went wrong, check things manually plz."
End If

End Sub- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

:D
ok. what happens when you step thru the code?
you said you're getting "does not support the property or method", so
i'm assuming that vba can't find one of these workbooks.

Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value

OR it can't find the sheet, or it can't find the range.
step thru & hover over each of these & see which one it can't find.

to make life easier, i would set variables for these 2 ranges:

dim DestCell as range
dim StartCell as range

set startcell =
Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127))

set destcell =
Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI

then you could add
msgbox destcell.address
and
msgbox startcell.address

to make sure vba knows where they are.
that might help you find the error.
if it knows where both of them are, try
destcell.value = startcell.value

thinks-too-much-susan
:)


On Mar 23, 10:06 am, "artisdepartis" wrote:
Susan, im giving up on the s (This time they were in my code for
sure when i copied it to GoogleGroups... ;-)

You are right, the error occurs in this bit:
Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row,
2), Cells(FoundCell.Row, 127)).Value _
= Workbooks(sFileNameResults).Sheets("SPAI").rLastSp litsPerAI.Value

I checked and the setting of FoundCell works fine, even though i dont
completely get why... I had the same thoughts as you on that, but this
works... so that is okay enough, even though i dont fully understand
it... (Timepressure :S)
This FoundCell basically is the Row on which I want to insert my
results in my overview file. The reason i want to do it like this, is
I want to avoid the Clipboard. (Our system is VERY slow).

To give you an idea:
copying it via
Workbooks(sFileNameResults).Sheets("SPLITS PER AI").Range(Cells(3, 1),
Cells(3, LSPAI)).Select
Selection.Copy

and pasting it via
FoundCell.Offset(0, 1).Select
ActiveCell.PasteSpecial xlPasteValues

Does work, but since i need to do these with more data this becomes
very code and memory inefficient...

Thx for thinking along ^^