VBA Formula Help

[chaddres]

I have the below spreadsheet where column M can have different values. I need to insert a formula using VBA into column N based on the value in column M.

If M29 = "JTO/HEN002" then (K2+L2)/'Locations'!D2 but if M29 = "JTO/KIL003" then (K2+L2)/'Locations'!D3. I have the list of 'Locations' below. To complicate this, the number of rows in my spreadsheet can vary from week to week.

Any ideas?

 

[mikenelena]

Hi Chaddres,

Give this a try and see if it works for what you are looking to do:

Sub InsertFormulas()
    Dim lastRow As Long
    Dim i As Long
    
    lastRow = Cells(Rows.Count, "M").End(xlUp).row
    
    For i = 2 To lastRow
        If Cells(i, "M").Value = "JTO/HEN002" Then
            Cells(i, "N").Formula = "=(" & Cells(i, "K").Address & "+" & Cells(i, "L").Address & ")/'Locations'!D2"
        ElseIf Cells(i, "M").Value = "JTO/KIL003" Then
            Cells(i, "N").Formula = "=(" & Cells(i, "K").Address & "+" & Cells(i, "L").Address & ")/'Locations'!D3"
        End If
    Next i
End Sub

 

[chaddres]

Unfortunately this will not work because my list of locations (shown in post) has quite a few options. And, it could even grow in the future. Thank you though.

 

[mikenelena]

Sorry, let me ponder this a bit. In the meantime, someone else may come along and solve it before my brain comes up with it.