Help with Array Range and For Each If Then Statement VBA. It works for the first line.

[pnwAnalyst]

I'm still really new to VBA. And this is the last portion of a long list of macros for completing data cleanup and analysis for a report. Maybe this isn't the best way to do this? I'm still new to this, so I'm open to other suggestions. But it needs to be a macro. This is basically what it looks like (the * fields are filled with a vlookup, which is why I have two different arrays because they're not contiguous):

mfg #

mfg # alt

desc

brand

uom

qtyuom

mfgname

mfgcode

mfgtype

contract

contractnum

supcode

effdate

expdate

$date

$exp

status

*

*

*

*

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

<tbody>
</tbody>

The number of rows varies depending on the report. Sometimes its 4000 rows, sometimes its more, sometimes its less. But I've made sure that every column would be the same. We're trying to automate as much of it as we can so that we might be able to get some less-technical people to be able to run through the entire process. The first time I went through the process it took me 6 hours (although I was taking notes too). For the senior person here it takes about 2 hours for each one, depending. We have somewhere around 300 of these to do before the end of the year.

Anyways, this code works, but only for the first row. Which makes sense because that's what I've told it to do in setting the ranges. But when I tried to get it to fill down it filled every single row in the entire worksheet and crashed excel. Any help would be appreciated.

Sub AutomateAllTheThings6()
    Dim arr3() As String
    Dim arr11() As String
    Dim rng3 As Range
    Dim rng11 As Range
    Dim sourcerng As Range


    Set rng3 = ActiveSheet.Range("BH2:BJ2")
    Set rng11 = ActiveSheet.Range("BL2:BV2")
    Set sourcerng = ActiveSheet.Range("BE2:BF2")
    'Set sourcerng = ActiveSheet.Range("BE:BF" & Range("BD" & Rows.count).End(xlUp).Row)
    arr3() = Split("UNKNOWN,UNKNOWN,UNKNOWN", ",")
    arr11() = Split("UNKNOWN, UNKNOWN, UNKNOWN, UNKNOWN, UNKNOWN, UNKNOWN, 00/00/0000, 00/00/0000, 00/00/0000, 00/00/0000, NEEDS REVIEW", ",")
        Call OptimizeCode_Begin
            For Each cell In sourcerng
                If IsEmpty(cell) Then
                    rng3.Value = arr3
                    rng11.Value = arr11
                End If
            Next
        Call OptimizeCode_End
End Sub

 

[pnwAnalyst]

Ahh right, I knew I forgot to clarify something.

When both cells from the BE and BF column are blank, that's when I need the arrays to get inserted.

 

[pnwAnalyst]

Well this code works now, but fills every row with the array's, not just the rows where columns BE and BF are blank. Must be my For Each and If statement.

Sub AutomateAllTheThings6()
    Dim arr3() As String
    Dim arr11() As String
    Dim rng3 As Range
    Dim rng11 As Range
    Dim sourcerng As Range
    Dim lastRow As Long
        Call OptimizeCode_Begin
            lastRow = Range("D1:D" & Range("D1").End(xlDown).Row).Rows.Count
            Set rng3 = ActiveSheet.Range("BH2:BJ2" & ":BH" & lastRow)
            Set rng11 = ActiveSheet.Range("BL2:BV2" & ":BL" & lastRow)
            Set sourcerng = ActiveSheet.Range("BE2:BF2" & ":BE" & lastRow)
            arr3() = Split("UNKNOWN,UNKNOWN,UNKNOWN", ",")
            arr11() = Split("UNKNOWN, UNKNOWN, UNKNOWN, UNKNOWN, UNKNOWN, UNKNOWN, 00/00/0000, 00/00/0000, 00/00/0000, 00/00/0000, NEEDS REVIEW", ",")
                For Each cell In sourcerng
                    If IsEmpty(cell) Then
                        rng3.Value = arr3
                        rng11.Value = arr11
                    End If
                Next
        Call OptimizeCode_End
End Sub

 

[pnwAnalyst]

Any help would be greatly appreciated.