Create a Do While loop to scan a column until no value is found in VBA

Question

I worked on the top portion of this last week and that doesn't need anything aside from some formatting on my end. The bottom portion with the block style comments are where I am stuck. (below wks.Activate is new)

I am trying to set a do while loop that sequentially reads through a given column until no value is present, then copy the row above and paste the formatting into the blank row.

There are a couple other problems, but I'm just concerned with base functionality at the moment.

The code I have put together so far is as follows:

Public Sub AddNewPage()
    Sheets(Sheets.Count).Select 'references last sheet in workbook
    Dim wks As Worksheet 'establish static variable wks to reference worksheets
    Dim nullVal As Boolean 'set a boolean variable for value check
    Dim i As Integer 'set variable as integer for coming loop
    Set wks = ActiveSheet 'set wks to be the given, activated sheet (dynamic variable)

    ActiveSheet.Copy After:=Worksheets(Sheets.Count) 'sets the last sheet in the workbook as the active sheet and copies it
    Range("H9").Value = Range("H9").Value + 1 'sets value of cell H9 in new worksheets to sequentially increase by 1

    If wks.Range("H9").Value <> "" Then 'If cell "H9" in activated worksheet has a value then...
        On Error Resume Next 'Proceed even if I beak stuff
        wks = Sheets(Sheets.Count).Select 'sets wks to reference last sheet in given workbook
        ActiveSheet.Name = wks.Range("H9").Value + 1 'sets page title to sequentially increase by 1 with each iteration
    End If

    wks.Activate

    Application.Worksheets("Log").Activate 'redirect to primary sheet used for tracking workbook data

    Do While nullVal = False 'establishing a do while loop to scan cells until no value is found

        For i = 1 To 1000                    '*************************
            If Cells(i, 1).Value <> "" Then  '*This is my problem
                nullVal = False              '*area, I think.
                i = i + 1                    '*
                Else: nullVal = True         '*The goal is to create
            End If                           '*a do while loop that
        Next i                               '*scans cells in the given
                                             '*column until no value
        If nullVal = True Then Exit Do       '*is found and copy the
        If nullVal = True Then Exit Sub      '*above row's formatting
                                             '*into the blank row.
    Loop                                     '*(see below)
                                             '*************************
    If nullVal = True Then
        wks.Cells(i, 1).End(xlUp).Offset(1, 0).Select '**********************************
        Rows(Selection.Row - 1).Copy                  '*Another minor bug in here as it
        Rows(Selection.Row).Insert Shift:=xlDown      '*won't necessarily select the last 
        Rows(Selection.Row).ClearContents             '*row if the user is in the sheet
        Application.CutCopyMode = False               
    End If
End Sub

It's somewhat functional at this point, with bugs.

Any other sets of eyes and any depth of knowledge would be appreciated as I just started learning this... thing (VBA) and I've been working on and off with this for days now.

I am curious as to whether an array or table might serve me well here.

You do not need a while loop. Yout question is a duplicate question. See https://stackoverflow.com/questions/68945401/select-first-empty-cell-in-column-and-works-for-empty-column/68946916 or https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba — GoWiser, Aug 28 '21 at 06:04
When you use **On Error Resume Next** you should always check if **Err.Number <> 0** and if it is, handle the error and clear the error state with **Err.Clear**, otherwise you might get unexpected behavior. — GoWiser, Aug 28 '21 at 06:15
If you want to test or modify the data in a range, it is faster to use an array than accessing the range repeatedly, see https://stackoverflow.com/questions/68912994/vba-speed-up-for-loop-with-array-dictionary/68947199#68947199 — GoWiser, Aug 28 '21 at 06:18

score 0 · Accepted Answer · answered Aug 28 '21 at 21:50

0

I'm not sure by saying copy the format to the empty [row] means the entire row or just the empty cell?

For you loop struct

Dim vItem As Variant

For Each vItem In Selection

    If vItem.Value = "" Then
    
        vItem.Activate
        ActiveCell.Offset(-1, 0).Copy
        ActiveCell.PasteSpecial xlPasteFormats
        Exit Sub
        
    End If
    
Next vItem

This will format the empty cell by the format of the cell immediately above it.

Is this what you want?

answered Aug 28 '21 at 21:50

doco

24
2

I'm still to low to up vote apparently. It's close, but pasting directly presented it's own challenges and it was easier to simply add the formulas directly. I got it solved, I appreciate the insight. – Ipixler Sep 01 '21 at 00:08
Looking at this again, it's definitely right along the same lines and I'll dig deeper into it. Most of my background is entry level c++ so VBA is a different animal with similar structures but different syntax. I'll definitely check it out and play around with it. – Ipixler Sep 01 '21 at 17:19

Ipixler · Answer 2 · 2021-09-01T00:28:50.363

Public Sub AddNewPage()

Sheets(Sheets.Count).Select 'references last sheet in workbook
Dim wks As Worksheet 'establish static variable wks to reference worksheets
Dim nullVal As Boolean 'set a boolean variable for value check
Dim i As Integer 'set variable as integer for coming loop
Set wks = ActiveSheet 'set wks to be the given, activated sheet (dynamic variable)

Dim iRange As Range
Dim iCells As Range
    
ActiveSheet.Copy After:=Worksheets(Sheets.Count) 'sets the last sheet in the workbook as the active sheet and copies it
Range("H9").Value = Range("H9").Value + 1 'sets value of cell H9 in new worksheets to sequentially increase by 1

If wks.Range("H9").Value <> "" Then 'If cell "H9" in activated worksheet has a value then...
    On Error Resume Next 'Proceed even if I beak stuff
    wks = Sheets(Sheets.Count).Select 'sets wks to reference last sheet in given workbook
    ActiveSheet.Name = wks.Range("H9").Value + 1 'sets page title to sequentially increase by 1 with each iteration
End If

wks.Activate

Application.Worksheets("Log").Activate 'redirect to primary sheet used for tracking workbook data

Do While nullVal = False 'establishing a do while loop to scan cells until no value is found
    For i = 1 To Sheets(Sheet.Count).Value 'Makes the book functional, but because I have an index page and a master template I have compensate with "- 2" in the subsequent code
        If Cells(i, 1).Value > "" Then 'If cells in defined column have positive value, adhere to the following
            nullVal = False
            Else: nullVal = True
        End If
    Next i 'Psuedo ++ style operator
    
    If nullVal = True Then
        Cells(i - 1, 1) = i - 2 'Floating variable which sets numeric value of a given cell to coincide with page count
    End If
    
Loop

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'The following makes use of the floating variable "i - 2" to
'define the page number for the given formulas
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Cells(i - 1, 1).Select 'Creates a hyperlink to sheets as they are being created
ActiveCell.Formula = "=('" & i - 2 & "'!H9)"
Sheets("Log").Cells(i - 1, 1).Hyperlinks.Add Selection, Address:="", SubAddress:="'" & Sheets(Sheets.Count).Name & "'!H9", TextToDisplay:=""

Cells(1, 1).Select 'Solves the issue of replacing descriptor in cell (1, 1)
Sheets("Log").Cells(1, 1).Hyperlinks.Add Selection, Address:="", SubAddress:="", TextToDisplay:="EWA #"

Cells(i - 1, 2).Select
ActiveCell.Formula = "=('" & i - 2 & "'!A12)"

Cells(i - 1, 3).Select
ActiveCell.Formula = "=('" & i - 2 & "'!H24)"

Cells(i - 1, 4).Select
ActiveCell.Formula = "=('" & i - 2 & "'!H25)"

Cells(i - 1, 5).Select
ActiveCell.Formula = "=('" & i - 2 & "'!D29)"

Cells(i - 1, 6).Select
ActiveCell.Formula = "=('" & i - 2 & "'!E29)"

Cells(i - 1, 7).Select
ActiveCell.Formula = "=('" & i - 2 & "'!H42)"

Cells(i - 1, 1).EntireRow.RowHeight = 50 'Row width formatting for new data

Cells(i - 1, 1).Select 'Moving back to the top of the page

End Sub

That is what I was trying to get to, took a bit and I'm still not completely familiarized with VBA. If anyone has any insightts on how to clean it up a bit I'm all ears.

I would like to find a way to create a page from the base master template, called "master," have the created page populate with the name "1" and populate the index page with a 1 as well while no values are present.

Create a Do While loop to scan a column until no value is found in VBA

2 Answers2