Need Help with Last Bit of Loop

Nogan

New Member
Joined
Nov 28, 2017
Messages
10
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.



Code:
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:

1ay
b
2ay2
v
d
3ty3
a
d
a
s
6by4
b
a
a

<colgroup><col span="3"></colgroup><tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Nogan

New Member
Joined
Nov 28, 2017
Messages
10
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

New Member
Joined
Nov 28, 2017
Messages
10
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

New Member
Joined
Nov 28, 2017
Messages
10
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top