Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004 write data to a cell
it runs fine for me AFTER I change the col Val to ColVal
-- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Andreas Weber" wrote: Hello Always i get an error 1004 on the function above and i can't find my mistake. I want to read values from the cell an write the steeps in an other cell. On acces to the cell i get always error 1004. Option Explicit Function CalcTest(col Val As Integer, colRes As Integer) As Double On Error GoTo ErrorHandle Dim res As Double Dim sum As Double Dim i As Long Dim errNr As Integer res = 1 sum = 0 For i = 5 To 15 res = Cells(i, colVal).Value 'Read Value sum = sum + res Cells(i, colRes).Value = sum 'Write Result, here comes the error 1004 Next i CalcTest = sum Exit Function ErrorHandle: errNr = Err.Number Resume Next End Function Thanks for all help, i am very confused about this problem. I need this code for an other function. Andreas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004 write data to a cell
On my function colVal is together, it was a faut on copy the code this
window. But on my system the code doesn't work correct. I have excel 2007 sp2 german version. The worksheet isn't protected. I can't explain this problem for me. I have tested it on 2 systems win7 and winxp. Hope you can give me any idee Greetings Andreas "Gary Brown" wrote: it runs fine for me AFTER I change the col Val to ColVal -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Andreas Weber" wrote: Hello Always i get an error 1004 on the function above and i can't find my mistake. I want to read values from the cell an write the steeps in an other cell. On acces to the cell i get always error 1004. Option Explicit Function CalcTest(col Val As Integer, colRes As Integer) As Double On Error GoTo ErrorHandle Dim res As Double Dim sum As Double Dim i As Long Dim errNr As Integer res = 1 sum = 0 For i = 5 To 15 res = Cells(i, colVal).Value 'Read Value sum = sum + res Cells(i, colRes).Value = sum 'Write Result, here comes the error 1004 Next i CalcTest = sum Exit Function ErrorHandle: errNr = Err.Number Resume Next End Function Thanks for all help, i am very confused about this problem. I need this code for an other function. Andreas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004 write data to a cell
Maybe we can give better help if you provide more detail around the
conditions under which you get the error. Does the code ALWAYS error regardless where or how it is run? Are you using the function in a formula in your spreadsheet? Or are you calling the function through other code? When you call it, is the active sheet the same one where you want the value of "sum" to be written? "Andreas Weber" wrote: On my function colVal is together, it was a faut on copy the code this window. But on my system the code doesn't work correct. I have excel 2007 sp2 german version. The worksheet isn't protected. I can't explain this problem for me. I have tested it on 2 systems win7 and winxp. Hope you can give me any idee Greetings Andreas "Gary Brown" wrote: it runs fine for me AFTER I change the col Val to ColVal -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Andreas Weber" wrote: Hello Always i get an error 1004 on the function above and i can't find my mistake. I want to read values from the cell an write the steeps in an other cell. On acces to the cell i get always error 1004. Option Explicit Function CalcTest(col Val As Integer, colRes As Integer) As Double On Error GoTo ErrorHandle Dim res As Double Dim sum As Double Dim i As Long Dim errNr As Integer res = 1 sum = 0 For i = 5 To 15 res = Cells(i, colVal).Value 'Read Value sum = sum + res Cells(i, colRes).Value = sum 'Write Result, here comes the error 1004 Next i CalcTest = sum Exit Function ErrorHandle: errNr = Err.Number Resume Next End Function Thanks for all help, i am very confused about this problem. I need this code for an other function. Andreas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004 write data to a cell
I use the code as an user defined function in the active worksheet. The
function calc the sum and give it return. But the cells on the result column are empty. I set an an breakpoint in the function and then i can see that on these line Cells(i, colRes).Value = sum 'Write Result, here comes the the error 1004 comes. On the next steep I install an english version on an other system. Andreas "B Lynn B" wrote: Maybe we can give better help if you provide more detail around the conditions under which you get the error. Does the code ALWAYS error regardless where or how it is run? Are you using the function in a formula in your spreadsheet? Or are you calling the function through other code? When you call it, is the active sheet the same one where you want the value of "sum" to be written? "Andreas Weber" wrote: On my function colVal is together, it was a faut on copy the code this window. But on my system the code doesn't work correct. I have excel 2007 sp2 german version. The worksheet isn't protected. I can't explain this problem for me. I have tested it on 2 systems win7 and winxp. Hope you can give me any idee Greetings Andreas "Gary Brown" wrote: it runs fine for me AFTER I change the col Val to ColVal -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Andreas Weber" wrote: Hello Always i get an error 1004 on the function above and i can't find my mistake. I want to read values from the cell an write the steeps in an other cell. On acces to the cell i get always error 1004. Option Explicit Function CalcTest(col Val As Integer, colRes As Integer) As Double On Error GoTo ErrorHandle Dim res As Double Dim sum As Double Dim i As Long Dim errNr As Integer res = 1 sum = 0 For i = 5 To 15 res = Cells(i, colVal).Value 'Read Value sum = sum + res Cells(i, colRes).Value = sum 'Write Result, here comes the error 1004 Next i CalcTest = sum Exit Function ErrorHandle: errNr = Err.Number Resume Next End Function Thanks for all help, i am very confused about this problem. I need this code for an other function. Andreas |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004 write data to a cell
If you want to send me a copy I can see if I can determine the problem that
way. Post reply here if you do that - then I'll know to go look for it. bbras612 at comcast dot net "Andreas Weber" wrote: I use the code as an user defined function in the active worksheet. The function calc the sum and give it return. But the cells on the result column are empty. I set an an breakpoint in the function and then i can see that on these line Cells(i, colRes).Value = sum 'Write Result, here comes the the error 1004 comes. On the next steep I install an english version on an other system. Andreas "B Lynn B" wrote: Maybe we can give better help if you provide more detail around the conditions under which you get the error. Does the code ALWAYS error regardless where or how it is run? Are you using the function in a formula in your spreadsheet? Or are you calling the function through other code? When you call it, is the active sheet the same one where you want the value of "sum" to be written? "Andreas Weber" wrote: On my function colVal is together, it was a faut on copy the code this window. But on my system the code doesn't work correct. I have excel 2007 sp2 german version. The worksheet isn't protected. I can't explain this problem for me. I have tested it on 2 systems win7 and winxp. Hope you can give me any idee Greetings Andreas "Gary Brown" wrote: it runs fine for me AFTER I change the col Val to ColVal -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Andreas Weber" wrote: Hello Always i get an error 1004 on the function above and i can't find my mistake. I want to read values from the cell an write the steeps in an other cell. On acces to the cell i get always error 1004. Option Explicit Function CalcTest(col Val As Integer, colRes As Integer) As Double On Error GoTo ErrorHandle Dim res As Double Dim sum As Double Dim i As Long Dim errNr As Integer res = 1 sum = 0 For i = 5 To 15 res = Cells(i, colVal).Value 'Read Value sum = sum + res Cells(i, colRes).Value = sum 'Write Result, here comes the error 1004 Next i CalcTest = sum Exit Function ErrorHandle: errNr = Err.Number Resume Next End Function Thanks for all help, i am very confused about this problem. I need this code for an other function. Andreas |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004 write data to a cell
Yes it is possible to send you a copy of an excel workbook. Where can I send
the file. Thanks for your help. My Mail is a_aweber(at)bluewin.ch Andreas "B Lynn B" wrote: If you want to send me a copy I can see if I can determine the problem that way. Post reply here if you do that - then I'll know to go look for it. bbras612 at comcast dot net "Andreas Weber" wrote: I use the code as an user defined function in the active worksheet. The function calc the sum and give it return. But the cells on the result column are empty. I set an an breakpoint in the function and then i can see that on these line Cells(i, colRes).Value = sum 'Write Result, here comes the the error 1004 comes. On the next steep I install an english version on an other system. Andreas "B Lynn B" wrote: Maybe we can give better help if you provide more detail around the conditions under which you get the error. Does the code ALWAYS error regardless where or how it is run? Are you using the function in a formula in your spreadsheet? Or are you calling the function through other code? When you call it, is the active sheet the same one where you want the value of "sum" to be written? "Andreas Weber" wrote: On my function colVal is together, it was a faut on copy the code this window. But on my system the code doesn't work correct. I have excel 2007 sp2 german version. The worksheet isn't protected. I can't explain this problem for me. I have tested it on 2 systems win7 and winxp. Hope you can give me any idee Greetings Andreas "Gary Brown" wrote: it runs fine for me AFTER I change the col Val to ColVal -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Andreas Weber" wrote: Hello Always i get an error 1004 on the function above and i can't find my mistake. I want to read values from the cell an write the steeps in an other cell. On acces to the cell i get always error 1004. Option Explicit Function CalcTest(col Val As Integer, colRes As Integer) As Double On Error GoTo ErrorHandle Dim res As Double Dim sum As Double Dim i As Long Dim errNr As Integer res = 1 sum = 0 For i = 5 To 15 res = Cells(i, colVal).Value 'Read Value sum = sum + res Cells(i, colRes).Value = sum 'Write Result, here comes the error 1004 Next i CalcTest = sum Exit Function ErrorHandle: errNr = Err.Number Resume Next End Function Thanks for all help, i am very confused about this problem. I need this code for an other function. Andreas |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004 write data to a cell
Thanks for your help. Now it works fine. The faut was that I made an call of
an UDF and not a call with an event. Now I insert a button an call the function from these button. Thank you very mutch. Andreas "B Lynn B" wrote: If you want to send me a copy I can see if I can determine the problem that way. Post reply here if you do that - then I'll know to go look for it. bbras612 at comcast dot net "Andreas Weber" wrote: I use the code as an user defined function in the active worksheet. The function calc the sum and give it return. But the cells on the result column are empty. I set an an breakpoint in the function and then i can see that on these line Cells(i, colRes).Value = sum 'Write Result, here comes the the error 1004 comes. On the next steep I install an english version on an other system. Andreas "B Lynn B" wrote: Maybe we can give better help if you provide more detail around the conditions under which you get the error. Does the code ALWAYS error regardless where or how it is run? Are you using the function in a formula in your spreadsheet? Or are you calling the function through other code? When you call it, is the active sheet the same one where you want the value of "sum" to be written? "Andreas Weber" wrote: On my function colVal is together, it was a faut on copy the code this window. But on my system the code doesn't work correct. I have excel 2007 sp2 german version. The worksheet isn't protected. I can't explain this problem for me. I have tested it on 2 systems win7 and winxp. Hope you can give me any idee Greetings Andreas "Gary Brown" wrote: it runs fine for me AFTER I change the col Val to ColVal -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Andreas Weber" wrote: Hello Always i get an error 1004 on the function above and i can't find my mistake. I want to read values from the cell an write the steeps in an other cell. On acces to the cell i get always error 1004. Option Explicit Function CalcTest(col Val As Integer, colRes As Integer) As Double On Error GoTo ErrorHandle Dim res As Double Dim sum As Double Dim i As Long Dim errNr As Integer res = 1 sum = 0 For i = 5 To 15 res = Cells(i, colVal).Value 'Read Value sum = sum + res Cells(i, colRes).Value = sum 'Write Result, here comes the error 1004 Next i CalcTest = sum Exit Function ErrorHandle: errNr = Err.Number Resume Next End Function Thanks for all help, i am very confused about this problem. I need this code for an other function. Andreas |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004 write data to a cell
Thank you for your help. Now it works fine. It was the problem with the UDF.
For all others here the helpful answer from bras So when I and the other person who responded on the message board ran the code as code, then of course we didn't see the problem. I don't really make UDF's to use in formulas, so wasn't aware of these rules. Essentially this indicates your final solution will need to somehow involve calling the function with code rather than in a cell formula. Maybe it would be worthwhile thinking about trying to use the Worksheet_Change event to make that happen Limitations of UDF's €¢ Cannot "record" an Excel UDF like you can an Excel macro. €¢ More limited than regular VBA macros. UDF's cannot alter the structure or format of a worksheet or cell. €¢ If you call another function or macro from a UDF, the other macro is under the same limitations as the UDF. €¢ Cannot place a value in a cell other than the cell (or range) containing the formula. In other words, UDF's are meant to be used as "formulas", not necessarily "macros". €¢ Excel user defined functions in VBA are usually much slower than functions compiled in C++ or FORTRAN. €¢ Often difficult to track errors. €¢ If you create an add-in containing your UDF's, you may forget that you have used a custom function, making the file less sharable. €¢ Adding user defined functions to your workbook will trigger the "macro" flag (a security issue: Tools Macros Security...). Andreas "B Lynn B" wrote: If you want to send me a copy I can see if I can determine the problem that way. Post reply here if you do that - then I'll know to go look for it. bbras612 at comcast dot net "Andreas Weber" wrote: I use the code as an user defined function in the active worksheet. The function calc the sum and give it return. But the cells on the result column are empty. I set an an breakpoint in the function and then i can see that on these line Cells(i, colRes).Value = sum 'Write Result, here comes the the error 1004 comes. On the next steep I install an english version on an other system. Andreas "B Lynn B" wrote: Maybe we can give better help if you provide more detail around the conditions under which you get the error. Does the code ALWAYS error regardless where or how it is run? Are you using the function in a formula in your spreadsheet? Or are you calling the function through other code? When you call it, is the active sheet the same one where you want the value of "sum" to be written? "Andreas Weber" wrote: On my function colVal is together, it was a faut on copy the code this window. But on my system the code doesn't work correct. I have excel 2007 sp2 german version. The worksheet isn't protected. I can't explain this problem for me. I have tested it on 2 systems win7 and winxp. Hope you can give me any idee Greetings Andreas "Gary Brown" wrote: it runs fine for me AFTER I change the col Val to ColVal -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Andreas Weber" wrote: Hello Always i get an error 1004 on the function above and i can't find my mistake. I want to read values from the cell an write the steeps in an other cell. On acces to the cell i get always error 1004. Option Explicit Function CalcTest(col Val As Integer, colRes As Integer) As Double On Error GoTo ErrorHandle Dim res As Double Dim sum As Double Dim i As Long Dim errNr As Integer res = 1 sum = 0 For i = 5 To 15 res = Cells(i, colVal).Value 'Read Value sum = sum + res Cells(i, colRes).Value = sum 'Write Result, here comes the error 1004 Next i CalcTest = sum Exit Function ErrorHandle: errNr = Err.Number Resume Next End Function Thanks for all help, i am very confused about this problem. I need this code for an other function. Andreas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime error 1004 write data to a cell | Excel Programming | |||
Runtime error '1004' Cannot change part of a merged cell | Excel Programming | |||
Getting error 1004 trying to write formula in cell | Excel Programming | |||
Runtime error 1004 - when I increase the amount of data | Excel Programming | |||
Excel 2002 Data Forms runtime error 1004 | Excel Programming |