If then inside select case

Question

Inside each Select Case block where the case is not “Name”, I want to add an if then statement that compares the values in the 2 rows above.  If the values are equal then add 15 to the row above and paste the sum into the cell.  If the values are not equal, then paste the value from the row above into the cell.  Can you help?

Sub CsSlct()

Dim ws1 As Worksheet, ws2 As Worksheet

Dim lastRow As Long, i As Long

Set ws1 = ThisWorkbook.Sheets("Sheet1")

Set ws2 = ThisWorkbook.Sheets("Sheet2")

lastRow = ws1. Cells(ws1. Rows.Count, "B"). End(xlUp). Row 'get the last row in Sheet1 Column B

For i = 2 To lastRow 'start at row 2 assuming there is a header in row 1

Select Case ws1. Range("B" & i). Value

Case "Name"

ws2. Range("B" & i). Value = "ID"

ws2. Range("A" & i). Value = ws1. Range("A" & i). Value

ws2. Range("C" & i). Value = Val(ws2.Range("C" & i - 1).Value) + 5

Case "Title"

ws2. Range("B" & i). Value = "ID"

ws2. Range("A" & i). Value = ws1. Range("A" & i). Value

‘if then statement

Case "Satisfied"

ws2. Range("B" & i). Value = "ID"

ws2. Range("A" & i). Value = ws1. Range("A" & i). Value

ws2. Range("C" & i). Value = Val(ws2.Range("C" & i - 1).Value) + 5

 **‘if then statement**

Case "Percent"

ws2. Range("B" & i). Value = "ID"

ws2. Range("A" & i). Value = ws1. Range("A" & i). Value

‘if then statement

End Select

Next i

End Sub

Answer 1

Hi there

Please, try replacing ws with ws2 in the given code

If i > 2 Then

        If **ws2**.Range("C" & i - 2).Value = **ws2**.Range("C" & i - 1).Value Then 

            **ws2**.Range("C" & i).Value = **ws2**.Range("C" & i - 1).Value + 15 

        Else 

            **ws2**.Range("C" & i).Value = **ws2**.Range("C" & i - 1).Value 

        End If 

End If

Regards

Jeovany

Answer 2

Your loop with i = 2. For that value, we cannot look at the cell 2 rows up!

Perhaps code like this:

        If i > 2 Then
            If ws.Range("C" & i - 2).Value = ws2.Range("C" & i - 1).Value Then
                ws.Range("C" & i).Value = ws.Range("C" & i - 1).Value + 15
            Else
                ws.Range("C" & i).Value = ws.Range("C" & i - 1).Value
            End If
        End If

Answer 3

Thank you for your help. I added the code but I am getting this error