Need Help with Last Bit of Loop

[Nogan]

Hi All,

I am adapting a code I found on the web into my macro, it works exactly as I would like except for one aspect. I modified the loop to spit out sSentence into column C, however, I am unable to figure out how to make sSentence be placed in the correct row. As of now the data is being placed in the row adjacent to the beginning of the next loop iteration. Is there anyway to store the beginning of each loop iteration's cell and offset it to place the end result?

I have the code below and denoted where I need assistance.

Sub NT()
 
    Range("A1:B4").Select
    Selection.UnMerge
    Range("B1:B4").Select
    Selection.Value = "A"
  
' Renames each sheet
 For x = 1 To Sheets.Count
 
    If Worksheets(x).Range("C2").Value = "" Then
        On Error Resume Next
        Range("C2").FormulaR1C1 = _
            "=+MID(R[0]C[-2],LEN(LEFT(R[0]C[-2],17)),LEN(R[0]C[-2])-LEN(RIGHT(R[0]C[-2],18))-LEN(LEFT(R[0]C[-2],16)))"
    End If
 
    If Worksheets(x).Range("C2").Value <> "" Then
        Sheets(x).Name = Worksheets(x).Range("C2").Value
    End If
 
 Next




' Deleting rows in column B that are blank


On Error Resume Next
    Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete


On Error GoTo NoBlanks
    Cells.Replace " ", "#N/A", xlWhole
    Cells.SpecialCells(xlConstants, xlErrors).Delete
NoBlanks:


    Range("B1:B4").ClearContents
    
' Main Section


Dim sWord As String
Dim sSentence As String
Dim c As Long


c = 6


Do


sWord = Cells(c, 2).Value


    If Not Cells(c, 1).Value = "" Or Cells(c, 2) = "" Then
    
Cells(c, 3) = sSentence
        sSentence = sWord
    Else
    Debug.Print sSentence
    Debug.Print sWord


'This section builds the sentences and this is where I need the data from sSentence stored in the beginning row of each iteration
        
        If sSentence = vbNullString Then
            sSentence = sWord      
        Else
            sSentence = sSentence & Space(1) & sWord
          
        End If
    
    End If
    
    c = c + 1


Loop While Not sWord = ""




End Sub

To give you an idea of what the data looks like:

[TABLE="width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]a[/TD]
[TD]y2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]t[/TD]
[TD]y3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]s[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]b[/TD]
[TD]y4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In reference to the above, y2 is where sSentence is currently being placed, but it needs to be in y... conversely, y3 needs to be at y2, etc.

Any help is much appreciated.

Thanks

 

[Nogan]

Bump

 

[Nogan]

Bump.

Would it need to store the position at the beginning of each iteration since the sizing of each could be different? Any help is appreciated!

 

[Nogan]

Bump.

Maybe if there was an additional loop that runs after the above, that moves the non-empty cell in column C to where the iteration starts? Seems inefficient.

 

[Nogan]

Spit balling ideas here:

How about if the sSentences created in the current procedure are stored in an array then a second loop runs once complete and spits them out in the location of nonblanks in A but in column C? Would this be possible/a good approach?