Help Fix my VBA Code

[cmschmitz24]

Hello, I'm trying to look up a value in column H, then return a value in column V, from a formula specific to what is in column H and column S.

Here is my current VBA code that will not produce what I'm looking for.

Sub Premiums()

Dim Lastrow As Long
Dim myRow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For myRow = 2 To Lastrow

Select Case Left(Cells(myRow, "H"), 1)
Case "A": result = ActiveCell.FormulaR1C1 = "=RC[-3]/12"
Case "C": result = ActiveCell.FormulaR1C1 = "=RC[-3]/12"
Case "H": result = ActiveCell.FormulaR1C1 = "=RC[-3]/24"
Case Else: result = ""
End Select

Cells(myRow, "V") = result
Next myRow

End Sub

The returned value says "FALSE", so I'm thinking the result code is not correct.

Thanks,
Christina

 

[GWteB]

Your code depends on the active cell, so the outcome is unpredictable.
Is it possible for you to paste a fragment of your worksheet and your required outcome?

 

[cmschmitz24]

The values in column H can change, however they will always start with either A, C, or H. Based on those, I need the formula to populate the correct value in column V.
If A or C, then take the corresponding value in column S and divide by 12. If H, then take the corresponding value in column S and divide by 24.

 

[GWteB]

You prefer VBA over a formula?

 

[cmschmitz24]

I guess I don't care, to be honest.
But I wasn't able to understand how to write the if/then statement to only look at the first letter of column H.

 

[GWteB]

Try this formula for the cells in column V, starting in row 2 and then copying that cell downwards:
=IF(OR(LEFT(H2,1)="A",LEFT(H2,1)="C"), S2/12, S2/24 )

 

[Fluff]

With your code

Sub Premiums()

   Dim Lastrow As Long
   Dim myRow As Long
   Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
   For myRow = 2 To Lastrow
   
      Select Case Left(Cells(myRow, "H"), 1)
         Case "A", "C": Cells(myRow, "V").FormulaR1C1 = "=RC[-3]/12"
         Case "H": Cells(myRow, "V").FormulaR1C1 = "=RC[-3]/24"
         Case Else: Cells(myRow, "V") = ""
      End Select
   
   Next myRow

End Sub

 

[cmschmitz24]

Thank you both so much!

 

[Fluff]

Glad we could help & thanks for the feedback