Excel VBA - Fill data in a Range based on value that has been picked from another search

[Zendan21]

Hello,

I am after a VBA code that will fill in data for the 'Location' Column. The value of this data to be filled in will pick from the 'Manufacturing method' offset value.

If the 'Location' value is blank I want to search the 'Manufacturing method' column to find the value 'Assembled-House' once this value is found I want the use the 'Location' value offset of the Assembled-House to fill in the cells that are blank until Assembled-House value is found again.

So if done correctly the macro would fill in the blank values for the first Assembled-house group as '10', the second group have no blanks so these would be ignored and the third group blanks as '3 > G > 8'

I have the below Code which is very basic and will fill data based on the value above it however that isn't always correct to the group it needs to pick from


Thank you for the help!

Option Explicit

Sub Location_Fill()

Application.ScreenUpdating = False
Dim c  As Range

    For Each c In Range("I2:I" & Cells(Rows.Count, "I").End(xlUp).Row)

'adds location flow to any blank cells
        If c.Value < 1 And c.Offset(-1, 0) Like "*JP Bench*" Then c.Value = "JP Bench"
        If c.Value < 1 And c.Offset(-1, 0) Like "*JP*" Then c.Value = "Jig & Press"
        If c.Value < 1 And c.Offset(-1, 0) Like "*10.1*" Then c.Value = "F > 10.1"
        If c.Value < 1 And c.Offset(-1, 0) Like "*10.2*" Then c.Value = "F > 10.2"
        If c.Value < 1 And c.Offset(-1, 0) Like "*10.3*" Then c.Value = "F > 10.3"
        If c.Value < 1 And c.Offset(-1, 0) Like "*10*" Then c.Value = "F > 10"
        If c.Value < 1 And c.Offset(-1, 0) Like "*1*" Then c.Value = "F > 1"
        If c.Value < 1 And c.Offset(-1, 0) Like "*2*" Then c.Value = "F > 2"
        If c.Value < 1 And c.Offset(-1, 0) Like "*3*" Then c.Value = "F > 3"
        If c.Value < 1 And c.Offset(-1, 0) Like "*4*" Then c.Value = "F > 4"
        If c.Value < 1 And c.Offset(-1, 0) Like "*5*" Then c.Value = "F > 5"
        If c.Value < 1 And c.Offset(-1, 0) Like "*6*" Then c.Value = "F > 6"
        If c.Value < 1 And c.Offset(-1, 0) Like "*7*" Then c.Value = "F > 7"
        If c.Value < 1 And c.Offset(-1, 0) Like "*8*" Then c.Value = "F > 8"
        If c.Value < 1 And c.Offset(-1, 0) Like "*9*" Then c.Value = "F > 9"
        
    Next
    
Application.ScreenUpdating = True

End Sub