Hi Robert,
The following should be more efficient:
Code:
Sub Test()
Application.ScreenUpdating = False
Dim LastRow As Long, Rng As Range, oCel As Range, i As Long, bState As Boolean
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
Set Rng = Range("a2:a" & LastRow)
For Each oCel In Rng
With oCel
bState = True
If .Value Like "*FUJ*" Then
For i = 10 To 18
If Not IsEmpty(Cells(.Row, i)) Then
bState = False
Exit For
End If
Next
End If
With Cells(.Row, "u")
If bState = True Then
.Value = "Out of Stock"
Else
.Value = "In Stock"
End If
End With
End With
Next
Application.ScreenUpdating = True
End Sub
If your range J:Q holds only numeric data, you could possibly make the code even more efficient by simply summing the values in that range and testing for a non-zero result. For example:
Code:
Sub Test1()
Application.ScreenUpdating = False
Dim LastRow As Long, RngID As Range, RngSum As Range, oCel As Range, i As Long, bState As Boolean
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
Set RngID = Range("a2:a" & LastRow)
For Each oCel In RngID
With oCel
bState = True
If .Value Like "*FUJ*" Then
Set RngSum = Worksheets("Sheet1").Range("J" & .Row & ":" & "Q" & .Row)
If Application.WorksheetFunction.Sum(RngSum) = 0 Then bState = False
End If
With Cells(.Row, "u")
If bState = True Then
.Value = "Out of Stock"
Else
.Value = "In Stock"
End If
End With
End With
Next
Application.ScreenUpdating = True
End Sub