thatoneguy
03-13-2009, 11:27 AM
:Background about me:
Some coding classes, nothing professional but understands a basic concept of the layout/format (aka knows how to use google >.>)
:About my project:
i have a user input section that is 8 rows x 11 columns. The user does not have to fill in all 8 rows but must fill in the first one at least. All 11 columns do not have to have a value, only the first and last cells in the column. To better explain, it is for a blending column and the rows reference to the number of different distillation types in the process and the columns reference to the temperature in relation to the feed rate percent.
Ex:
........"type1" "type2" "type3"
1%......x1........x2........x3
5%......x1'........x2'.......x3'
10%.....x1''.......x2''......x3''
etc
:What I'm trying to do:
The program already interpolates/extrapolates for empty values as long as they are not in the first and last cells. It will ignore the column if the first cell is missing and error if the last cell is missing (simply done by an increasing order check). I want to have an error (msgbox) show when the first cell in the column is empty
:My problem:
I have tried the isnull and isempty functions with no luck. Some common errors I have been running into is that when the user only has 4/8 rows filled, it will produce and error for the last four boxes because the first cell in the column is empty.
:Sample portion of the code:
For k = 1 To 8
For i = 1 To 11
v(i) = Sheets("Blender").Cells(vOff + i, 1).Value
a(i) = Sheets("Blender").Cells(vOff + i, 2 + k).Value
If a(i) = Empty Then
If i = 1 Then Exit For
For j = i + 1 To 11
v(j) = Sheets("Blender").Cells(vOff + j, 1).Value
a(j) = Sheets("Blender").Cells(vOff + j, 2 + k).Value
If a(j) > 0 Then Exit For
Next j
a(i) = (v(i) - v(i - 1)) / (v(j) - v(i - 1)) * _
(a(j) - a(i - 1)) + a(i - 1)
End If
Sheets("Blender").Cells(vOff + i, 2 + k).Value = a(i)
b(i) = a(i)
Next i
If i > 1 Then
dtype = Sheets("blender").Cells(vOff, 2 + k).Value
'---------------------------------------------------------------------
' Check input for monotonic increase
For j = 1 To 10
If a(j) > a(j + 1) Then
Sheets("blender").Cells(vOff + j + 1, 2 + k).Select
MsgBox "Highlighted input for component " & k & " is not _
monotonically increasing.", vbCritical, "Data Error"
Exit Sub
End If
Next j
:Notes about this portion of code:
-Voff is a set value referenced earlier in the code. I like to give people headaches when they search for cell numbers to edit code =D
-the next k is at the very end of the code (Too much code to copy/paste)
-dtype is used later in case function
:closing:
I hope I am posting this correctly...I tried to inlcude as much info as I could and as much organized detail as possible. If there's anything else you need to know or something you noticed I posted incorrectly, please let me know. Thanks a bunch.
- /frustrated
Some coding classes, nothing professional but understands a basic concept of the layout/format (aka knows how to use google >.>)
:About my project:
i have a user input section that is 8 rows x 11 columns. The user does not have to fill in all 8 rows but must fill in the first one at least. All 11 columns do not have to have a value, only the first and last cells in the column. To better explain, it is for a blending column and the rows reference to the number of different distillation types in the process and the columns reference to the temperature in relation to the feed rate percent.
Ex:
........"type1" "type2" "type3"
1%......x1........x2........x3
5%......x1'........x2'.......x3'
10%.....x1''.......x2''......x3''
etc
:What I'm trying to do:
The program already interpolates/extrapolates for empty values as long as they are not in the first and last cells. It will ignore the column if the first cell is missing and error if the last cell is missing (simply done by an increasing order check). I want to have an error (msgbox) show when the first cell in the column is empty
:My problem:
I have tried the isnull and isempty functions with no luck. Some common errors I have been running into is that when the user only has 4/8 rows filled, it will produce and error for the last four boxes because the first cell in the column is empty.
:Sample portion of the code:
For k = 1 To 8
For i = 1 To 11
v(i) = Sheets("Blender").Cells(vOff + i, 1).Value
a(i) = Sheets("Blender").Cells(vOff + i, 2 + k).Value
If a(i) = Empty Then
If i = 1 Then Exit For
For j = i + 1 To 11
v(j) = Sheets("Blender").Cells(vOff + j, 1).Value
a(j) = Sheets("Blender").Cells(vOff + j, 2 + k).Value
If a(j) > 0 Then Exit For
Next j
a(i) = (v(i) - v(i - 1)) / (v(j) - v(i - 1)) * _
(a(j) - a(i - 1)) + a(i - 1)
End If
Sheets("Blender").Cells(vOff + i, 2 + k).Value = a(i)
b(i) = a(i)
Next i
If i > 1 Then
dtype = Sheets("blender").Cells(vOff, 2 + k).Value
'---------------------------------------------------------------------
' Check input for monotonic increase
For j = 1 To 10
If a(j) > a(j + 1) Then
Sheets("blender").Cells(vOff + j + 1, 2 + k).Select
MsgBox "Highlighted input for component " & k & " is not _
monotonically increasing.", vbCritical, "Data Error"
Exit Sub
End If
Next j
:Notes about this portion of code:
-Voff is a set value referenced earlier in the code. I like to give people headaches when they search for cell numbers to edit code =D
-the next k is at the very end of the code (Too much code to copy/paste)
-dtype is used later in case function
:closing:
I hope I am posting this correctly...I tried to inlcude as much info as I could and as much organized detail as possible. If there's anything else you need to know or something you noticed I posted incorrectly, please let me know. Thanks a bunch.
- /frustrated