Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error: what am i doing wrong??
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error: what am i doing wrong??
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error: what am i doing wrong??
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error: what am i doing wrong??
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error: what am i doing wrong??
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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error: what am i doing wrong??
: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 ^^ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error: what am i doing wrong??
Susan, getting very frustrated here... (not because of you ofc!)
I think there's a problem with the way I address my ranges. I tried ur suggestion and there seems to be a problem with setting set startcell = Workbooks("overview_f.xls").Sheets("SPAI").Range(C ells(FoundCell.Row, 2), Cells(FoundCell.Row, 127)) Got the thing working now, because i need it implemented by Monday, but I am still desperately and hardheadedly looking for my mistake. On Mar 23, 3:23 pm, "Susan" wrote: :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 ^^- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error: what am i doing wrong??
Just now, by chance i notice that the problem probably is in the way i
address my Workbooks. No idea why, but when i run the macro it gives an error, if I select the workbook and then continue my macro it works... weird... still no solution though On Mar 23, 3:23 pm, "Susan" wrote: :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 ^^- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error: what am i doing wrong??
artis -
i hope tom's solution worked for you & you made your deadline......... sorry i couldn't help. :/ susan On Mar 23, 12:18 pm, "artisdepartis" wrote: Just now, by chance i notice that the problem probably is in the way i address my Workbooks. No idea why, but when i run the macro it gives an error, if I select the workbook and then continue my macro it works... weird... still no solution though On Mar 23, 3:23 pm, "Susan" wrote: :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 ^^- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error: what am i doing wrong??
Tom, thx for this clarification... It is through help like yours that
i learn from my mistakes :-) @ Susan: your help clarified the problem for me. I did manage to get it working in time, by rerecording and using that code. (From my observation that the sheet needed to be selected...) I hope that someday i can post replies in stead of questions here. Keep up your generousity in helping! Now I will improve my macro... I thought i fixed the dateproblem, but alas... that one is still a bugger :S TBC On Mar 23, 8:24 pm, Tom Ogilvy wrote: I haven't read the full thread, but for the specific part you are highlighting: You qualify only the range and not the cells within the range. This means that the range will point to a specific sheet and the cells will point to the activesheet. When these are the same (as you have observed), the code works. When not, you get an error. Fix it like this With Workbooks("overview_f.xls").Sheets("SPAI") set startcell = .Range(.Cells(FoundCell.Row,2), _ .Cells(FoundCell.Row, 127)) End With Range and Both Cells should be preceded with a period. -- Regards, Tom Ogilvy "artisdepartis" wrote: Just now, by chance i notice that the problem probably is in the way i address my Workbooks. No idea why, but when i run the macro it gives an error, if I select the workbook and then continue my macro it works... weird... still no solution though On Mar 23, 3:23 pm, "Susan" wrote: :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 ^^- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error: what am i doing wrong??
Susan, thx for your help anyways! See my post above in reply to Tom!!
On Mar 26, 1:34 pm, "Susan" wrote: artis - i hope tom's solution worked for you & you made your deadline......... sorry i couldn't help. :/ susan On Mar 23, 12:18 pm, "artisdepartis" wrote: Just now, by chance i notice that the problem probably is in the way i address my Workbooks. No idea why, but when i run the macro it gives an error, if I select the workbook and then continue my macro it works... weird... still no solution though On Mar 23, 3:23 pm, "Susan" wrote: :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 ^^- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run Time 1004 Error: Application or Object Difine Error | Excel Programming | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Error 1004, Application-definded or object-defined error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |