Re: VBA For Each Loop
Try this
Assumes Row 1 is a header row (including a header for column H) and data starts in cell B1
Sub test()
Dim x, y, i As Long, ii As Long
With Sheets("sheet1") '<<< Change sheet name to suit
x = .Cells(1).CurrentRegion.Value
ReDim y(1 To UBound(x, 1) - 1)
For i = 2 To UBound(x, 1)
For ii = 1 To UBound(x, 2) - 1
If x(i, ii) <> "" Then
y(i - 1) = x(i, ii)
Exit For
End If
If IsEmpty(y(i - 1)) Then y(i - 1) = "IN-SCOPE"
Next
Next
.Cells(2, 8).Resize(UBound(y)) = Application.Transpose(y)
End With
End SubRe: VBA For Each Loop
Please attach a sample of your workbook
Re: VBA For Each Loop
Sub LoadInScope()
Dim x, i As Long, ii As Long
With Sheets("MASTER").ListObjects("Table1")
x = .DataBodyRange.Resize(, 8)
For i = 1 To UBound(x, 1)
For ii = 1 To UBound(x, 2)
If x(i, ii) <> "" Then
x(i, 8) = x(i, ii)
Exit For
End If
Next
If IsEmpty(x(i, 8)) Then x(i, 8) = "IN-SCOPE"
Next
.DataBodyRange.Resize(, 8) = x
End With
End SubRe: VBA For Each Loop
You're welcome
Re: VBA For Each Loop
You could also achieve this via the following array formula*:
{=IFERROR(INDEX(A2:G2,MATCH(TRUE,A2:G2<>"",0)),"IN-SCOPE")}
Robert
* Array formulas must be confirmed with CTRL+SHIFT+ENTER after typing or modifying them. When done correctly, Excel will automatically add braces, {}, around the formula. Do not simply type the braces yourself.
Re: VBA For Each Loop
Non Array
=IFERROR(INDEX(A2:G2,0,MATCH("*",A2:G2,0)),"IN-SCOPE")
VBA
Sub test()
With Cells().CurrentRegion
.Columns("h").Offset(1).Resize(.Rows.Count - 1).FormulaR1C1 = _
"=iferror(index(rc1:rc7,0,match(""*"",rc1:rc7,0)),""IN=SCOPE"")"
End With
End SubRe: VBA For Each Loop
Good one (as usual) jindon 
Re: VBA For Each Loop
Not always, but thanks.
Don’t have an account yet? Register yourself now and be a part of our community!