Previous Topic Next Topic

Return Seq Values Until Blank value then return different set of seq values

Solved/Closed
lorieb63 - Sep 28, 2021 at 02:14 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Sep 30, 2021 at 11:33 AM
Hello,
What is a formula to return a specific set of values in sequential cells until the reference sequence returns a blank value then start returning values from a different sequence?

Example:

if A2 is not blank, return value in A2 then sequential as A3, A4 etc. else return value starting in B1, then sequential as B2, B3 etc…
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549
Sep 30, 2021 at 11:33 AM
Hi Lorieb,

When your source sheet looks like this:


and you want your destination sheet to look like this:


Then this is the code to use: 
Sub RunMe()
Dim r, c As Long
r = 2
c = 1
Sheets("Sheet1").Select
Do
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(r, c).Value
r = r + 1
If Cells(r, c).Value = vbNullString Then
    r = 2
    c = c + 1
End If
Loop Until Cells(r, c) = vbNullString
End Sub


Best regards,
Trowa
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Find values based on colum and sum in a table
lotusscript -
pcsces -

1 response
fixing populating values into textboxes based on showing data in listbox
abdelfatah_0230 -
abdelfatah_0230 -

11 responses
Conversion of values already present in cells
mike -
rizvisa1 -

1 response
two values to find on the same cell
jutulu -
jutulu -

2 responses