Enter Formula using AutoFill with a destination range

[Edgarvelez]

Hi All,

I have a code that worked for me in the past on something else and tried to apply it here but it's not working and I need to modify it.
I am using Select case but that is not the problem, it's only there to select one of the two formulas that I need to use.

Basically I am entering a formula starting B7 and I need the range count of this formula based on the range of A7.
If my range count in A7 is 1 then I get the error but If my range count in A7 is 2 or more the all is good.

    Sheets("DashBoard").Select
    Range("A7").Select
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("DashBoard")
    Set sh2 = Sheets("Sheet1")
    sh2.AutoFilterMode = False
    Application.ScreenUpdating = False
    If IsEmpty(ThisWorkbook.Sheets(1).Range("B3")) Then
    MsgBox ("STOP!   Check or Search Type is Blank. Select Sheet Line No. or Batch No. from Drop-Down Menu in Cell B3 to Continue")
    sh1.Range("D7").Select
    Cancel = True
    Exit Sub
    End If
    Application.ScreenUpdating = False
    If IsEmpty(ThisWorkbook.Sheets(1).Range("A7")) Then
    MsgBox ("STOP!   Sheet Line No. or Batch No. to search status Is Blank starting at Cell A7. Enter Sheet Line No.'(s) or Batch No.'(s) startin at Cell A7 to Continue")
    sh1.Range("D7").Select
    Cancel = True
    Exit Sub
    End If
    sh1.Range("B7").Select
    Range("B7:B1048576").Select
    Selection.ClearContents
    sh1.Range("B7").Select
    Select Case sh1.Range("B3").Value
    Case "AWL Lot No."
    ActiveCell.FormulaR1C1 = _
    "=IF(COUNTBLANK(INDEX(Sheet1!R2C10:R1048576C13,MATCH(RC[-1],Sheet1!R2C1:R1048576C1,0),0))=4,""OK To Ship"",""Serial / Batch No. Has Been Used"")"
    '   Excel Sheet Formula   =IF(COUNTBLANK(INDEX(Sheet1!$J$2:$M$1048576,MATCH(A7,Sheet1!$A$2:$A$1048576,0),0))=4,"OK To Ship","Serial / Batch No. Has Been Used")
    Case "Serial / Batch No."
    ActiveCell.FormulaR1C1 = _
        "=IF(COUNTBLANK(INDEX(Sheet1!R2C10:R1048576C13,MATCH(RC[-1],Sheet1!R2C2:R1048576C2,0),0))=4,""OK To Ship"",""Serial / Batch No. Has Been Used"")"
    '   Excel Sheet Formula   =IF(COUNTBLANK(INDEX(Sheet1!$J$2:$M$1048576,MATCH(A7,Sheet1!$B$2:$B$1048576,0),0))=4,"OK To Ship","Serial / Batch No. Has Been Used")
    End Select
    Set ws = ActiveSheet
    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    Alastrow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
    
    
    
    Range("B" & Alastrow).AutoFill Destination:=Range("B" & Alastrow & ":B" & lastRow), Type:=xlFillCopy
    sh1.Range("B7:B1048576").Select
    Selection.Copy
    With Range("B7", Range("B" & Rows.Count).End(xlUp))
    .Value = .Value
    End With
    Application.CutCopyMode = False
    sh1.Range("A7").Select

End Sub

 

[bebo021999]

With XL2BB mini sheet, I afraid that cell with space become blank cell, so I could not test.
To help you to control code, here is how my code works:
In row 42:
loop through each cell in L42:P42, then count lengh of text in side, if total len>0, it means L42:P42 are occupied
In row 43: It appears L43:P43 are blank cells. But, actually, it might be a blank space (1 space created from space bar, or char 160)
So, manual check by in any available cell, i.e, Q43 , to get lenght of L43 by
=len(L43) to see if Q43>0
Drag accross to check next cell M,N,O,P

If you could not find any error, try to share dummy file via, i.e, google drive.

 

[Edgarvelez]

I have it in my google drive what else would you need from me

 

[bebo021999]

Share the google drive link please.

 

[Edgarvelez]

Con1.xlsm

Dashboard Select Check or Search Type below: Serial / Batch No.'(s) Enter AWL Lot No.'(s) or Serial / Batch No.'(s) to check status below:,AWL Lot No.'(s) or Serial / Batch No.'(s) Status check results shown below: 225C006513,OK To Ship,Enter BOL Information below: 225C006514,OK To Ship,Order No...

docs.google.com

 

[Edgarvelez]

Did the link work?

 

[bebo021999]

OK. I forgot to reset length back to 0, after horizontal loop.
try again:

Sub Macro6BatchNo()
'
' Macro6BatchNo Macro
'

'
    Range("A7").Select
    
Dim lr&, lr2&, cell As Range, cellb As Range, cellc As Range, Stype As Range, length&
With Worksheets("DashBoard")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("B7:B" & lr).ClearContents
    If lr < 7 Then
        MsgBox "STOP!   Sheet Line No. or Batch No. to search status Is Blank starting at Cell A7. Enter Sheet Line No.'(s) or Batch No.'(s) startin at Cell A7 to Continue"
        Exit Sub
    End If
    Set Stype = .Range("B3")
    If IsEmpty(Stype) Then
        MsgBox "STOP!   Check or Search Type is Blank. Select Sheet Line No. or Batch No. from Drop-Down Menu in Cell B3 to Continue"
        Exit Sub
    End If
End With
With Worksheets("Sheet1")
    lr2 = .Cells.SpecialCells(xlCellTypeLastCell).Row
    For Each cell In Worksheets("DashBoard").Range("A7:A" & lr)
    
        If (IsNumeric(cell) And Not Stype Like "AWL*") Or (Not IsNumeric(cell) And Stype Like "AWL*") Then
            MsgBox "Invalid Line# or Match No!"
            Exit Sub
        End If
        For Each cellb In .Range("A3:B" & lr2)
            If cell.Value = cellb.Value Then
            'add this line
                length = 0
            '--------------
                For Each cellc In .Range(.Cells(cellb.Row, "L"), .Cells(cellb.Row, "P"))
                    length = length + Len(cellc)
                Next
            End If
        Next
        With cell.Offset(0, 1)
            If length = 0 Then
                .Value = "OK To Ship"
            Else: .Value = "Serial / Batch No. Has Been Used"
            End If
        End With
    Next
End With
End Sub

 

[Edgarvelez]

Ok I give it a try and let you know.

 

[Edgarvelez]

Hi,
It worked great with the Serial / Batch No.'s and the AWL Lot No.'s also.
Thank you very much for your help.