Return a value from specific array of row

[purushothamhk693]

HiI've a requirement, need to a return a value from an array of single row which fulfills the below condition:The formula or macro has to search for an entire selected range of a row and get the value of second cell from the first blank cell in the selected array of rows.Can anyone please help me?Thank you// Hk

 

[WarPigl3t]

If you select the rage to be only 1 column wide then you can use this function.

Function afterBlank(myRange)
    '= afterBlank(A1:A100)
    output = FALSE
    For Each item in Range
        If output = TRUE Then
            afterBlank = item
            Exit Function
        End If
        If item = "" Then
            output = TRUE
        End If
    Next item
End Function

 

[L. Howard]

Hi HK,

Try this. With adjusted sheet names.

Regards,
Howard

Option Explicit

Sub MyOffsetPlus()
Dim c As Range
Dim lr As Long
Dim aRng As Range

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set aRng = Range("A1:A" & lr)

For Each c In aRng

  If Cells(c.Row, 1).Offset(, 1) = "" Then
     Cells(c.Row, 1).Offset(, 3).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2)
   Else
     Cells(c.Row, 1).End(xlToRight).Offset(, 3).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2)
  End If

Next

End Sub

 

[purushothamhk693]

Hey Thank you guys!!!
Below formula could also solve the problem:
=INDEX(A1:J1,MATCH(TRUE,INDEX(A1:J1="",0),0)+2)
Thanks// HK