Find Blank, do math based on value above

[tourless]

Hi Folks.

I've got a bunch of data sitting in column M where there are two rows between like values. I want to traverse this data (figure from the top down) and say... when I hit a blank cell look to the value above and place a formula in that blank cell but one column to the left (column L). The formula will take the value in column J for that blank row, and multiply that by a number which is dependent on the value above the blank cell in column M. You'll notice the multiplier changes depending on the value of M and my list is finite. For that I could just say If the value above the blank M cell is 252000300 then the multiplier is 20, if M cell value is 252000315 then the multiplier is 25, etc.

I need to know how to traverse the double blanks and offset my formula.

This...

J	K	L	M
9.25​			252000300
14.25​			252000300
-4.50​			252000300
-11.00​			252000300
8.00​
-30.50​			252000315
-41.50​			252000315
-22.50​			252000315
-15.25​			252000315
-109.75​
1.00​			252000320
-0.50​			252000320
8.50​			252000320
0.75​			252000320
9.75​

Needs to become this...

J	K	L	M
9.25​			252000300
14.25​			252000300
-4.50​			252000300
-11.00​			252000300
8.00​		=J5*20
-30.50​			252000315
-41.50​			252000315
-22.50​			252000315
-15.25​			252000315
-109.75​		=J11*25
1.00​			252000320
-0.50​			252000320
8.50​			252000320
0.75​			252000320
9.75​		=J17*216

 

[kvsrinivasamurthy]

Where are the multipliers 20,25,216 in the given data.

 

[tourless]

They are not in the data. They live outside the scope of the workbook. My solution to that was to cod them as variables...
H300 as long, H315 as Long, etc.

Then assign them values...
Set H300 = "20"
Set H315 = "25"
etc.

Then capture the 'row above blank value which will be one of the vars, and reference it's value in my simply math formula.

It sound very convoluted to me but I can't think of another way to handle that.

 

[tourless]

Update...
I'm making some progress but can't seem to set the variable N300 to the value of a cell one row up and three columns to the right. That value should be NY300. I'm sure you can see where this is going and I'm open to suggestions for a better way to accomplish this. The underlined code throws an 'Object variable or With block variable not set' error. Do I have to declare N300 in my with block? If so will it be persistent to the rest of my sub?

Sub AddProjectedDollars()
Dim cell As Range
Dim lRow As Long, i As Long
Dim NY300 As Long, NY315 As Long, NY320 As Long, NY415 As Long, NY420 As Long, NY425 As Long, NY430 As Long, NY435 As Long, NY440 As Long, NY510 As Long, NY520 As Long
Dim NJ300 As Long, NJ315 As Long, NJ320 As Long, NJ415 As Long, NJ420 As Long, NJ425 As Long, NJ430 As Long, NJ435 As Long, NJ440 As Long, NJ510 As Long, NJ520 As Long
Dim N300 As String

    NY300 = 20
    NY315 = 25
    NY320 = 16
    NY415 = 16.5
    NY420 = 17.5
    NY425 = 16
    NY430 = 16
    NY435 = 15
    NY440 = 15
    NY510 = 37
    NY520 = 22

    lRow = Range("M" & Rows.Count).End(xlUp).Row
    For i = 2 To lRow
        With Range("J" & i)
            If .Font.Bold Then
            [U]N300 = cell.Offset(-1, 3).Value[/U]
            End If
        End With
    Next i

End Sub

 

[tourless]

If M7 is bold and contains NY300 then L7 should contain the formula =J7*20. Instead I get a 1004 Application-defined or object defined error. Not sure why.

If cell.Font.Bold Then
If cell.Value = "NY300" Then
cell.Offset(0, -1).Formula = "=J" & cell.Row & "*20)"
End If
End If

 

[tourless]

Final Update...

Well It's not pretty but I think it could have been worse...

Sub AddProjectedDollars()
Dim lRow As Long, i As Long
    
    lRow = Range("M" & Rows.Count).End(xlUp).Row
    For i = 3 To lRow
        With Range("M" & i)
            If .Font.Bold Then
                If .Value = "NY300" Then
                    Range("L" & i).Value = "=J" & i & "*20"
                End If
                If .Value = "NY315" Then
                    Range("L" & i).Value = "=J" & i & "*25"
                End If
                If .Value = "NY320" Then
                    Range("L" & i).Value = "=J" & i & "*16"
                End If
                If .Value = "NY415" Then
                    Range("L" & i).Value = "=J" & i & "*16.5"
                End If
                If .Value = "NY420" Then
                    Range("L" & i).Value = "=J" & i & "*17.5"
                End If
                If .Value = "NY425" Then
                    Range("L" & i).Value = "=J" & i & "*16"
                End If
                If .Value = "NY430" Then
                    Range("L" & i).Value = "=J" & i & "*16"
                End If
                If .Value = "NY435" Then
                    Range("L" & i).Value = "=J" & i & "*15"
                End If
                If .Value = "NY440" Then
                    Range("L" & i).Value = "=J" & i & "*15"
                End If
                If .Value = "NY510" Then
                    Range("L" & i).Value = "=J" & i & "*37"
                End If
                If .Value = "NY520" Then
                    Range("L" & i).Value = "=J" & i & "*22"
                End If
                If .Value = "NJ300" Then
                    Range("L" & i).Value = "=J" & i & "*18.5"
                End If
                If .Value = "NJ315" Then
                    Range("L" & i).Value = "=J" & i & "*25.5"
                End If
                If .Value = "NJ320" Then
                    Range("L" & i).Value = "=J" & i & "*14"
                End If
                If .Value = "NJ415" Then
                    Range("L" & i).Value = "=J" & i & "*15"
                End If
                If .Value = "NJ420" Then
                    Range("L" & i).Value = "=J" & i & "*15.5"
                End If
                If .Value = "NJ425" Then
                    Range("L" & i).Value = "=J" & i & "*15.5"
                End If
                If .Value = "NJ430" Then
                    Range("L" & i).Value = "=J" & i & "*15.5"
                End If
                If .Value = "NJ435" Then
                    Range("L" & i).Value = "=J" & i & "*14"
                End If
                If .Value = "NJ440" Then
                    Range("L" & i).Value = "=J" & i & "*15"
                End If
                If .Value = "NJ510" Then
                    Range("L" & i).Value = "=J" & i & "*32"
                End If
                If .Value = "NJ520" Then
                    Range("L" & i).Value = "=J" & i & "*14.5"
                End If
            End If
        End With
    Next i
    
    Range("M:M").ClearContents
    Range("L:L").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
    

End Sub