Solved: Help with case statement

[yasarayhanka]

I have written this lenghty case statement but it is no good,
what I am trying to do is, in the spreadsheet you will see the numbers wiill be added every month for the current month and following months
I am trying to enter the formula in the cells below that will give the result of last months entry - previous months entry.

Thanks in advance for the help.

Yasar

here is some part of the vba code that I am using

[vba]Select Case range("n14").Value
Case ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-12]C-R[-13]C"
Case Is <> ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-11]C-R[-12]C"
End Select

Select Case range("n15")
Case Is = ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-11]C-R[-12]C"
Case Is <> ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-10]C-R[-11]C"
End Select

Select Case range("n16")
Case Is = ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-10]C-R[-11]C"
Case Is <> ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-9]C-R[-10]C"
End Select

Select Case range("n17")
Case Is = ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-9]C-R[-10]C"
Case Is <> ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-8]C-R[-9]C"
End Select

Select Case range("n18")
Case Is = ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-8]C-R[-9]C"
Case Is <> ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-7]C-R[-8]C"
End Select

Select Case range("n19")
Case Is = ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-7]C-R[-8]C"
Case Is <> ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-6]C-R[-7]C"
End Select

Select Case range("n20")
Case Is = ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-6]C-R[-7]C"
Case Is <> ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-5]C-R[-6]C"
End Select

Select Case range("n21")
Case Is = ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-5]C-R[-6]C"
Case Is <> ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-4]C-R[-5]C"
End Select

Select Case range("n22")
Case Is = ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-4]C-R[-5]C"
Case Is <> ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-3]C-R[-4]C"
End Select
[/vba]

[malik641]

Hi Yasar,

I believe your code isn't working because you keep on selecting "N25" and changing it's formula, when maybe you want to select another cell.

Also, you can construct a loop to help reduce your code. Given your example, here's a way you can shorten it:

(Untested, but it should work)

[VBA]Public Sub Sample()
Dim iCounter As Long
Dim iRow As Long
Dim iCol As Long
Dim iSomeRelativeRow As Long

iSomeRelativeRow = 25
iRow = -12
iCol = -13

For iCounter = 14 To 22
Select Case ActiveSheet.Cells(iCounter, "N").Value
Case vbNullString ' Value is blank
ActiveSheet.Cells(iSomeRelativeRow, "N").FormulaR1C1 = _
"=R[" & iRow & "]C-R[" & iCol & "]C"
Case Else ' Value is NOT blank
ActiveSheet.Cells(iSomeRelativeRow, "N").FormulaR1C1 = _
"=R[" & (iRow + 1) & "]C-R[" & (iCol + 1) & "]C"
End Select

' Increment variables (except for iCounter, since
' it is done by the For loop
iRow = iRow + 1
iCol = iCol + 1
iSomeRelativeRow = iSomeRelativeRow + 1

Next iCounter ' 14 to 22
End Sub[/VBA]

Good luck!

[mdmackillop]

I would go with Joseph's solution, but for future reference, I would not use Select in an either/or situation.
Instead of
[vba]Select Case range("n14").Value
Case ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-12]C-R[-13]C"
Case Is <> ""
range("n25").Select
ActiveCell.FormulaR1C1 = "=R[-11]C-R[-12]C"
End Select
[/vba]
I would use
[vba]If Range("n14") = "" Then
Range("n25").FormulaR1C1 = "=R[-12]C-R[-13]C"
Else
Range("n25").FormulaR1C1 = "=R[-11]C-R[-12]C"
End Sub
[/vba]

[yasarayhanka]

Hi Yasar,

I believe your code isn't working because you keep on selecting "N25" and changing it's formula, when maybe you want to select another cell.

Also, you can construct a loop to help reduce your code. Given your example, here's a way you can shorten it:

(Untested, but it should work)

[vba]Public Sub Sample()
Dim iCounter As Long
Dim iRow As Long
Dim iCol As Long
Dim iSomeRelativeRow As Long

iSomeRelativeRow = 25
iRow = -12
iCol = -13

For iCounter = 14 To 22
Select Case ActiveSheet.Cells(iCounter, "N").Value
Case vbNullString ' Value is blank
ActiveSheet.Cells(iSomeRelativeRow, "N").FormulaR1C1 = _
"=R[" & iRow & "]C-R[" & iCol & "]C"
Case Else ' Value is NOT blank
ActiveSheet.Cells(iSomeRelativeRow, "N").FormulaR1C1 = _
"=R[" & (iRow + 1) & "]C-R[" & (iCol + 1) & "]C"
End Select

' Increment variables (except for iCounter, since
' it is done by the For loop
iRow = iRow + 1
iCol = iCol + 1
iSomeRelativeRow = iSomeRelativeRow + 1

Next iCounter ' 14 to 22
End Sub[/vba]

Good luck!

malik641,
My purpose is changing the formula in the same cell for each column (representing the months of the year), also each row represent when the data was entered. so in august I will enter the data for august and for the rest of the year in the same row.
what I am trying to do is in each column I want to see the difference of the entries made in this month and last month for each month of the year.


=IF(J14="",J13-J12,IF(J15="",J14-J13,IF(J16="",J15-J14,IF(J17="",J16-J15,IF(J18="",J17-J16,IF(J19="",J18-J17,J19-J18))))))

for july column I need to implement this if statement.

this if statement works, but I only can enter 8 if statements at once in excel, so for the last three months I have to enter the formula manually.
thank you for trying to help me on this,

yasar

[mdmackillop]

To get around IF limitations
http://www.cpearson.com/excel/nested.htm

[yasarayhanka]

To get around IF limitations
http://www.cpearson.com/excel/nested.htm

that solves the problem.
thank you very much

yasar