CODEWITHSUNDEEP
vbawhile-loopis-empty
Doug
Doug

Reputation: 1

Testing if a cell is empty in VBA

I'm trying to write a module in VBA to take a worksheet containing the structure of a website and output into a worksheet two columns which have (column 1) the name of a page, and (column 2) the section of the site that page is in. (So I can make use of it in Visio)

My existing worksheet contains a structure like this:

Root / Section / Sub-section / Page
Root / Section / Page
Root / Section / Sub-section / Sub-section / Page

i.e. each row shows the page's ancestors in the columns to the left, but there are different levels to the structure complicating matters.

I'm trying to loop through this, starting in column 7 (the deepest level of navigation), and looping left initially to find a non-empty cell, and then copying that cell and its parent into my new sheet. Once it hits column 2 I want it to move down, as this contains the root; this carries on until the last row at 562.

My code is below, but it's giving me an error on my 'While Isempty' line. What have I done wrong?

Sub directDescendent()

Dim readCol As Integer
Dim readRow As Integer
Dim writeRow As Integer
readCol = 7
readRow = 2
writeRow = 2

While readRow < 562
    While IsEmpty(ActiveWorkbook.Sheets(2).Cells(readRow, readCol).Value)
        readCol = readCol - 1
    Wend
    If readCol < 3 Then
        readCol = 7
        readRow = readRow + 1
    End If
    ActiveWorkbook.Sheets(3).Cells(writeRow, 1).Value =     ActiveWorkbook.Sheets(2).Cells(readRow, readCol).Value
    ActiveWorkbook.Sheets(3).Cells(writeRow, 2).Value = ActiveWorkbook.Sheets(2).Cells(readRow, readCol - 1).Value
    writeRow = writeRow + 1

Wend
End Sub

Upvotes: 0

Views: 11369

Answers (2)

gembird
gembird

Reputation: 14053

You can use for-next with step = -1. While-wend is not necessary because the number of itereations is known (7).

For readCol = 7 To 1 Step -1
    If Not IsEmpty(ActiveWorkbook.Sheets(2).Cells(readRow, readCol).Value) Then Exit For
Next readCol

'
' Note:
' Here readCol would be equeal to zero if all cells from column 7 to 1 are empty.
'

Upvotes: 1

user2299169
user2299169

Reputation:

Your readCol variable's value is 0 (zero) when the code enters While IsEmpty(ActiveWo... the 7th time (min value there should be 1) so an exception is thrown as index is out of bounds (asking for the 0th element of something that has elements 1 to n)

Upvotes: 3

Related Questions