|
Can someone find this error?
Hey everyone, I keep getting an error within my code and can't seem t solve the problem. If you could all check it out and let me know. Firs the code: Code ------------------- Option Explicit Public Function styrene() As Double Dim ExtCount As Integer, IntCount As Integer ExtCount = 7 While ExtCount <= 15 For IntCount = 17 To 21 Dim additivereactor As Integer additivereactor = ThisWorkbook.Worksheets("Input").Cells(IntCount, 4).Value Dim results As Integer results = ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value If additivereactor = results Then styrene = ThisWorkbook.Worksheets("calculations").Cells((Int Count - 15), 6).Value + styrene Else styrene = styrene + 0 End If ThisWorkbook.Worksheets("Calculations").Cells(20, ExtCount).Value = styrene Next IntCount ExtCount = ExtCount + 1 Wend End Function ------------------- additivereactor and results are always integers. The error is this lin of code: Code ------------------- results = ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value ------------------- Any help is appreciated. ~Jaso -- jclark41 ----------------------------------------------------------------------- jclark419's Profile: http://www.excelforum.com/member.php...fo&userid=2543 View this thread: http://www.excelforum.com/showthread.php?threadid=39057 |
Can someone find this error?
There are a couple of problems here.
1. The line of code that is bombing... are the values in ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value all integers? If not this will bomb out. you are safest to bring in the values as strings and validate them as integers then convert them using CInt. 2. What is the line styrene = styrene + 0 supposed to do? 3. The line ThisWorkbook.Worksheets("Calculations").Cells(20, ExtCount).Value = styrene is not valid in a function. Functions return values. They do not modify sheets. -- HTH... Jim Thomlinson "jclark419" wrote: Hey everyone, I keep getting an error within my code and can't seem to solve the problem. If you could all check it out and let me know. First the code: Code: -------------------- Option Explicit Public Function styrene() As Double Dim ExtCount As Integer, IntCount As Integer ExtCount = 7 While ExtCount <= 15 For IntCount = 17 To 21 Dim additivereactor As Integer additivereactor = ThisWorkbook.Worksheets("Input").Cells(IntCount, 4).Value Dim results As Integer results = ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value If additivereactor = results Then styrene = ThisWorkbook.Worksheets("calculations").Cells((Int Count - 15), 6).Value + styrene Else styrene = styrene + 0 End If ThisWorkbook.Worksheets("Calculations").Cells(20, ExtCount).Value = styrene Next IntCount ExtCount = ExtCount + 1 Wend End Function -------------------- additivereactor and results are always integers. The error is this line of code: Code: -------------------- results = ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value -------------------- Any help is appreciated. ~Jason -- jclark419 ------------------------------------------------------------------------ jclark419's Profile: http://www.excelforum.com/member.php...o&userid=25430 View this thread: http://www.excelforum.com/showthread...hreadid=390578 |
Can someone find this error?
Just some added information:
3. The line ThisWorkbook.Worksheets("Calculations").Cells(20, ExtCount).Value = styrene is not valid in a function. Functions return values. They do not modify sheets. The term "function" used in this statement is pertinent only to a user defined function used in a worksheet or called by such a function. Used as a function in VBA, there is no such restriction. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... There are a couple of problems here. 1. The line of code that is bombing... are the values in ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value all integers? If not this will bomb out. you are safest to bring in the values as strings and validate them as integers then convert them using CInt. 2. What is the line styrene = styrene + 0 supposed to do? 3. The line ThisWorkbook.Worksheets("Calculations").Cells(20, ExtCount).Value = styrene is not valid in a function. Functions return values. They do not modify sheets. -- HTH... Jim Thomlinson "jclark419" wrote: Hey everyone, I keep getting an error within my code and can't seem to solve the problem. If you could all check it out and let me know. First the code: Code: -------------------- Option Explicit Public Function styrene() As Double Dim ExtCount As Integer, IntCount As Integer ExtCount = 7 While ExtCount <= 15 For IntCount = 17 To 21 Dim additivereactor As Integer additivereactor = ThisWorkbook.Worksheets("Input").Cells(IntCount, 4).Value Dim results As Integer results = ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value If additivereactor = results Then styrene = ThisWorkbook.Worksheets("calculations").Cells((Int Count - 15), 6).Value + styrene Else styrene = styrene + 0 End If ThisWorkbook.Worksheets("Calculations").Cells(20, ExtCount).Value = styrene Next IntCount ExtCount = ExtCount + 1 Wend End Function -------------------- additivereactor and results are always integers. The error is this line of code: Code: -------------------- results = ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value -------------------- Any help is appreciated. ~Jason -- jclark419 ------------------------------------------------------------------------ jclark419's Profile: http://www.excelforum.com/member.php...o&userid=25430 View this thread: http://www.excelforum.com/showthread...hreadid=390578 |
Can someone find this error?
In article ,
"Jim Thomlinson" wrote: The line ThisWorkbook.Worksheets("Calculations").Cells(20, ExtCount).Value = styrene is not valid in a function. Functions return values. They do not modify sheets. Only when called from the worksheet. When called from a Sub/Function, it's perfectly legal for a function to change a cell value (as long as the Sub/Function wasn't called from the worksheet). |
Can someone find this error?
Very true. Since it is declared publicly it is available as a UDF so I added
the comment (probably should have been more specific). Thanks Tom & JE. -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: Just some added information: 3. The line ThisWorkbook.Worksheets("Calculations").Cells(20, ExtCount).Value = styrene is not valid in a function. Functions return values. They do not modify sheets. The term "function" used in this statement is pertinent only to a user defined function used in a worksheet or called by such a function. Used as a function in VBA, there is no such restriction. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... There are a couple of problems here. 1. The line of code that is bombing... are the values in ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value all integers? If not this will bomb out. you are safest to bring in the values as strings and validate them as integers then convert them using CInt. 2. What is the line styrene = styrene + 0 supposed to do? 3. The line ThisWorkbook.Worksheets("Calculations").Cells(20, ExtCount).Value = styrene is not valid in a function. Functions return values. They do not modify sheets. -- HTH... Jim Thomlinson "jclark419" wrote: Hey everyone, I keep getting an error within my code and can't seem to solve the problem. If you could all check it out and let me know. First the code: Code: -------------------- Option Explicit Public Function styrene() As Double Dim ExtCount As Integer, IntCount As Integer ExtCount = 7 While ExtCount <= 15 For IntCount = 17 To 21 Dim additivereactor As Integer additivereactor = ThisWorkbook.Worksheets("Input").Cells(IntCount, 4).Value Dim results As Integer results = ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value If additivereactor = results Then styrene = ThisWorkbook.Worksheets("calculations").Cells((Int Count - 15), 6).Value + styrene Else styrene = styrene + 0 End If ThisWorkbook.Worksheets("Calculations").Cells(20, ExtCount).Value = styrene Next IntCount ExtCount = ExtCount + 1 Wend End Function -------------------- additivereactor and results are always integers. The error is this line of code: Code: -------------------- results = ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value -------------------- Any help is appreciated. ~Jason -- jclark419 ------------------------------------------------------------------------ jclark419's Profile: http://www.excelforum.com/member.php...o&userid=25430 View this thread: http://www.excelforum.com/showthread...hreadid=390578 |
| All times are GMT +1. The time now is 09:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com