If cell.value A = x, then cell.value N = formula

user_16495411 0
Viewed 139

Trying to fix my spreadsheet to paste a formula in column N, based on the value of column A. Essentially, if cell A6 = "Text 1" then cell N6 = "Formula 1", if cell A7 = "Text 2" then cell N7 = "Formula 2" then have this loop through all rows.

Very amateur at VBA, so excessive googling has given me the below.

Sub Test()
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

    For Each Cell In Range("A6:A" & lr)
        If Cell.Value = "Call Options" Then
            Cell.Offset(0, 13).Value = "=-(B6*100)*E6"
        ElseIf Cell.Value = "Put Options" Then
            Cell.Offset(0, 13).Value = "=(B6*100)*E6"
        End If
    Next Cell  
End Sub

At this point, I hit run and nothing changes. No error message or bug shows up.

Any help would be much appreciated.

if-statement for-loop vba excel loops
2 Answers

If you want it to refer the cell in the same row you need to do this:

Cell.Offset(0, 13).Value = "=-(B" & Cell.Row & "*100)*E" & Cell.Row

If you want to make your If case insensitive use:

LCase(Cell.Value) = LCase("Call Options")
user_32196130

I would rewrite the code to the following:

Sub Test()
lr = Cells(1,Count.Rows).End(xlUp).Row
For i = 6 to lr
    If Cells(i,1).Value = "Call Options" Then
        Cells(i,14).Value = "=-(B" & i & "*100)*E" & i
    ElseIf Cells(i,1).Value = "Put Options" Then
        Cells(i,14).Value = "=(B" & i & "*100)*E" & i
    End If
Next i
End Sub

The reason I would change this is you are just trying to find the last row, so let's not overcomplicate things. Also, when using numeric column values rather than alphabetical values, it makes it easier reference cells without relying on Offset.

user_86657870
Related