JeenLeen
2014-04-21, 02:23 PM
I posted earlier today about an issue I thought I fixed, but then I realized it wasn't working if the two variables I was comparing are equal to 0.
Basically, I'm looking at some set positions on an Excel file and calculating the difference. The results should be at one decimal place, highlighted according to their value, and, if positive, have a + in front of them.
I think I resolved the 'how to handle it being 0' by going a different route. But now I get Run-time Error 91: Object variable or With block variable not set. I've looked at Windows help, and it doesn't help me.
Any idea what's causing it?
Also, I got an error message whenever iCalc was set to something equaling zero. Why would that give an error? (That is, iCalc = Format(Sheets(ws).Cells(rowNum, colNum) - Sheets(ws).Cells(rowNum + 1, colNum), "###.#;(###.#)") gave an error about a type mismatch if the subtraction would yield a 0.) Can doubles not be negative?
Update: the error is coming (I'm pretty sure) from this line: Rng = Sheets(ws).Cells(5, colNum)
Any ideas how I can modify or take that out while still preserving the ability to highlight? I don't how know how translate something like "B5" (an acceptable range) into something I can express with rowNum & colNum. I tried Rng = Range(Cells(5, colNum)), but that doesn't work, either; does change the error code to 1004: Application-defined or object-defined error
My current code is
Private Sub CommandButton1_Click()
'
'Format of cell-selection code: Sheets("SheetName").Cells(row, column)
'Rounding note: Long or Int rounds, so we can't use that to get the precision this output requires
' A Double could be used. However, since we are outputting this as text, I instead went with
' using a String. The 'Fixed' Format gives one decimal place: Format(Sheets(ws).Cells(3, 2) - Sheets(ws).Cells(4, 2), Fixed)
Dim ws As String, wValue As String
Dim rowNum As Integer, colNum As Integer 'sets row and column position
Dim iCalc As Double 'generic calculation to use for multiple programs
Dim Rng As Range
ws = ActiveSheet.Name
'for 2 HSAP Results. This has a range of A2 through D5.
rowNum = 3
colNum = 2
'if the current year is on the top row
If Sheets(ws).Cells(3, 1) > Sheets(ws).Cells(4, 1) Then
Rng = Sheets(ws).Cells(5, colNum)
Do While colNum < 5
If Sheets(ws).Cells(rowNum, colNum) = Sheets(ws).Cells(rowNum + 1, colNum) Then 'if zero
Range(Rng).Select
With Selection
.NumberFormat = "@"
.Interior.ThemeColor = xlThemeColorDark1
End With
Sheets(ws).Cells(5, colNum) = 0
ElseIf Sheets(ws).Cells(rowNum, colNum) > Sheets(ws).Cells(rowNum + 1, colNum) Then 'if positive
iCalc = Format(Sheets(ws).Cells(rowNum, colNum) - Sheets(ws).Cells(rowNum + 1, colNum), "###.#;(###.#)")
Range(Rng).Select
With Selection
.NumberFormat = "@"
.Interior.Color = 5296274
End With
wValue = "+" & iCalc
Sheets(ws).Cells(5, colNum) = wValue
ElseIf Sheets(ws).Cells(rowNum, colNum) < Sheets(ws).Cells(rowNum + 1, colNum) Then 'if negative
iCalc = Format(Sheets(ws).Cells(rowNum, colNum) - Sheets(ws).Cells(rowNum + 1, colNum), "###.#;(###.#)")
Range(Rng).Select
With Selection
.NumberFormat = "@"
.Interior.Color = 255
End With
Sheets(ws).Cells(5, colNum) = iCalc
Else
MsgBox "error: 2 HSAP Results: non-number calculated", "error message 1"
End If
colNum = colNum + 1
Loop
Else 'if the current year is not on the top row
MsgBox "error: 2 HSAP Results: A3 is not the current year", "error message 2"
End If
End Sub
My previous post:
I've run into a bug in an Excel VBA program I'm working on. I'm calculating numbers to one-decimal place and, when the numbers are positive, I want an "+" in front of the number. For example, 2.6 - .3 should give "+2.3".
I figured out I had to set the cells to text format to get the "+" to remain visible, but when I get a "+" to show up, I also get multiple decimal places. I tried making iELA a Double, and it still had the same problem.
My code is a lot longer than ideal (should've used a Loop), so I'm only putting the relevant pieces below:
Dim iELA As String, Dim wValue As String
iELA = Format(Sheets(ws).Cells(3, 2) - Sheets(ws).Cells(4, 2), Fixed)
If CInt(iELA) > 0 Then 'highlight green if positive
Range("B5").Select
Selection.NumberFormat = "@"
Selection.Interior.Color = 5296274
wValue = "+" & iELA
Sheets(ws).Cells(5, 2) = wValue
End If
-----
EDIT
-----
Upon further digging, I solved (or think I solved) my problem soon after posting. Rather than double-post, I'm editing this to add it.
I set the Function more specifically (something I didn't know would work) then concatenated with the "+". I'm using a Double. It might work just as well with a String, but using a Double feels more right for a calculated number.
Dim ws As String, wValue As String
'Dim iELA As String, iMath As String, iBoth As String
Dim iELA As Double, iMath As Double, iBoth As Double
ws = ActiveSheet.Name
'ws = worksheet name; i@@@ = calculated #s; wValue = i@@@@ with a '+'
'NOTE: this could be made more concise with a loop, but I didn't think of that until the coding was done
'if the current year is on row 3
If Sheets(ws).Cells(3, 1) > Sheets(ws).Cells(4, 1) Then
iELA = Format(Sheets(ws).Cells(3, 2) - Sheets(ws).Cells(4, 2), "###.#;(###.#)")
iMath = Format(Sheets(ws).Cells(3, 3) - Sheets(ws).Cells(4, 3), Fixed) 'fix not implemented for this or iBoth yet
iBoth = Format(Sheets(ws).Cells(3, 4) - Sheets(ws).Cells(4, 4), Fixed)
'ELA calculation
If iELA > 0 Then 'highlight green if positive
Range("B5").Select
Selection.NumberFormat = "@"
Selection.Interior.Color = 5296274
wValue = "+" & Format(iELA, Fixed)
Sheets(ws).Cells(5, 2) = wValue
'....various other code follows
Basically, I'm looking at some set positions on an Excel file and calculating the difference. The results should be at one decimal place, highlighted according to their value, and, if positive, have a + in front of them.
I think I resolved the 'how to handle it being 0' by going a different route. But now I get Run-time Error 91: Object variable or With block variable not set. I've looked at Windows help, and it doesn't help me.
Any idea what's causing it?
Also, I got an error message whenever iCalc was set to something equaling zero. Why would that give an error? (That is, iCalc = Format(Sheets(ws).Cells(rowNum, colNum) - Sheets(ws).Cells(rowNum + 1, colNum), "###.#;(###.#)") gave an error about a type mismatch if the subtraction would yield a 0.) Can doubles not be negative?
Update: the error is coming (I'm pretty sure) from this line: Rng = Sheets(ws).Cells(5, colNum)
Any ideas how I can modify or take that out while still preserving the ability to highlight? I don't how know how translate something like "B5" (an acceptable range) into something I can express with rowNum & colNum. I tried Rng = Range(Cells(5, colNum)), but that doesn't work, either; does change the error code to 1004: Application-defined or object-defined error
My current code is
Private Sub CommandButton1_Click()
'
'Format of cell-selection code: Sheets("SheetName").Cells(row, column)
'Rounding note: Long or Int rounds, so we can't use that to get the precision this output requires
' A Double could be used. However, since we are outputting this as text, I instead went with
' using a String. The 'Fixed' Format gives one decimal place: Format(Sheets(ws).Cells(3, 2) - Sheets(ws).Cells(4, 2), Fixed)
Dim ws As String, wValue As String
Dim rowNum As Integer, colNum As Integer 'sets row and column position
Dim iCalc As Double 'generic calculation to use for multiple programs
Dim Rng As Range
ws = ActiveSheet.Name
'for 2 HSAP Results. This has a range of A2 through D5.
rowNum = 3
colNum = 2
'if the current year is on the top row
If Sheets(ws).Cells(3, 1) > Sheets(ws).Cells(4, 1) Then
Rng = Sheets(ws).Cells(5, colNum)
Do While colNum < 5
If Sheets(ws).Cells(rowNum, colNum) = Sheets(ws).Cells(rowNum + 1, colNum) Then 'if zero
Range(Rng).Select
With Selection
.NumberFormat = "@"
.Interior.ThemeColor = xlThemeColorDark1
End With
Sheets(ws).Cells(5, colNum) = 0
ElseIf Sheets(ws).Cells(rowNum, colNum) > Sheets(ws).Cells(rowNum + 1, colNum) Then 'if positive
iCalc = Format(Sheets(ws).Cells(rowNum, colNum) - Sheets(ws).Cells(rowNum + 1, colNum), "###.#;(###.#)")
Range(Rng).Select
With Selection
.NumberFormat = "@"
.Interior.Color = 5296274
End With
wValue = "+" & iCalc
Sheets(ws).Cells(5, colNum) = wValue
ElseIf Sheets(ws).Cells(rowNum, colNum) < Sheets(ws).Cells(rowNum + 1, colNum) Then 'if negative
iCalc = Format(Sheets(ws).Cells(rowNum, colNum) - Sheets(ws).Cells(rowNum + 1, colNum), "###.#;(###.#)")
Range(Rng).Select
With Selection
.NumberFormat = "@"
.Interior.Color = 255
End With
Sheets(ws).Cells(5, colNum) = iCalc
Else
MsgBox "error: 2 HSAP Results: non-number calculated", "error message 1"
End If
colNum = colNum + 1
Loop
Else 'if the current year is not on the top row
MsgBox "error: 2 HSAP Results: A3 is not the current year", "error message 2"
End If
End Sub
My previous post:
I've run into a bug in an Excel VBA program I'm working on. I'm calculating numbers to one-decimal place and, when the numbers are positive, I want an "+" in front of the number. For example, 2.6 - .3 should give "+2.3".
I figured out I had to set the cells to text format to get the "+" to remain visible, but when I get a "+" to show up, I also get multiple decimal places. I tried making iELA a Double, and it still had the same problem.
My code is a lot longer than ideal (should've used a Loop), so I'm only putting the relevant pieces below:
Dim iELA As String, Dim wValue As String
iELA = Format(Sheets(ws).Cells(3, 2) - Sheets(ws).Cells(4, 2), Fixed)
If CInt(iELA) > 0 Then 'highlight green if positive
Range("B5").Select
Selection.NumberFormat = "@"
Selection.Interior.Color = 5296274
wValue = "+" & iELA
Sheets(ws).Cells(5, 2) = wValue
End If
-----
EDIT
-----
Upon further digging, I solved (or think I solved) my problem soon after posting. Rather than double-post, I'm editing this to add it.
I set the Function more specifically (something I didn't know would work) then concatenated with the "+". I'm using a Double. It might work just as well with a String, but using a Double feels more right for a calculated number.
Dim ws As String, wValue As String
'Dim iELA As String, iMath As String, iBoth As String
Dim iELA As Double, iMath As Double, iBoth As Double
ws = ActiveSheet.Name
'ws = worksheet name; i@@@ = calculated #s; wValue = i@@@@ with a '+'
'NOTE: this could be made more concise with a loop, but I didn't think of that until the coding was done
'if the current year is on row 3
If Sheets(ws).Cells(3, 1) > Sheets(ws).Cells(4, 1) Then
iELA = Format(Sheets(ws).Cells(3, 2) - Sheets(ws).Cells(4, 2), "###.#;(###.#)")
iMath = Format(Sheets(ws).Cells(3, 3) - Sheets(ws).Cells(4, 3), Fixed) 'fix not implemented for this or iBoth yet
iBoth = Format(Sheets(ws).Cells(3, 4) - Sheets(ws).Cells(4, 4), Fixed)
'ELA calculation
If iELA > 0 Then 'highlight green if positive
Range("B5").Select
Selection.NumberFormat = "@"
Selection.Interior.Color = 5296274
wValue = "+" & Format(iELA, Fixed)
Sheets(ws).Cells(5, 2) = wValue
'....various other code follows