CODEWITHSUNDEEP
vbaexcel
wildesbare
wildesbare

Reputation: 7

Loop with multiple Ranges

Im trying to write a code which determines whether certain cells are empty or not and then returns a set string.

To go in detail; I was hoping for the code to look into cell B2, determine if it is empty, then go to C2 and determine if it is non-empty. If both were correct in cell B2 it would then input "Correct" and move on in the range. However, my code doesnt seem to work because it just inputs "Correct" in every cell in the loop range.

I have posted my code below; any help would be much appreciated. 

Sub Fill_Rows()


Dim X As Range
Let Y = Range("C2")

For Each X In Range("B2:B5000")
    If X = "" And Y <> "" Then
        X = "Correct"
    End If
    Y = Y + 1
Next X


End Sub

Upvotes: 0

Views: 508

Answers (2)

T.M.
T.M.

Reputation: 9948

Alternative

Added two solutions:

  • [1] an example code as close as possible to yours and
  • [2] an alternative using a datafield array to demonstrate a faster way for bigger data sets.

[1] Example Code close to yours

There is no need to use a second variable Y, all the more as apparently you left it undeclared, which always can cause issues (type mismatches, no range object etc.). So always use Option Explicit in the declaration head of your code module to force yourself to declare all variable types you are using.

Now you can simply use an offset of 1 column to the existing cell to check the neighbouring cell, too.

Option Explicit   ' declaration head of your code module (obliges to declare variables)

Sub Fill_RowsViaRangeLoop()
Dim X As Range, ws As Worksheet
Set ws = ThisWorkbook.Worksheets("MySheet")  ' << replace with your sheet name
For Each X In ws.Range("B2:B5000")
    If X = "" And X.Offset(0, 1) <> "" Then  ' column offset 1 checks next cell in C
         X = "Correct"
    End If
Next X
End Sub

[2] Example Code using a datafield array

Looping through a bigger range isn't very fast, you can speed up your procedure by assigning your range values to a variant datafield array v, loop through the received array items correcting found items in column 1 and write it back to sheet. 

Option Explicit   ' declaration head of your code module (obliges to declare variables)

Sub Fill_RowsViaArray()
   Dim v As Variant, i As Long, ws As Worksheet
   Set ws = ThisWorkbook.Worksheets("MySheet")  ' << replace with your sheet name
 ' Assign values to a 2-dim array
   v = ws.Range("B2:C5000")             ' or better: v = ws.Range("B2:C5000").Value2
 ' Check criteria looping over all rows (=first array dimension)
   For i = 1 To UBound(v)               ' data field arrays are one-based, i.e. they start with 1
       If v(i, 1) = vbNullString And v(i, 2) <> vbNullString Then v(i, 1) = "Correct"
   Next i
 ' Write edited array back to original range (adapt the range size to the array boundaries in both dimensions)
   ws.Range("B2").Resize(UBound(v, 1), UBound(v, 2)) = v
 End Sub

Further Notes

  • It's good use to fully qualify your sheet or range references (see e.g. object variable ws)
  • Each array item is identified by a row and a column index.
  • As such a datafield array is one based (start indices are 1), the first item in row 1 and column 1 will be referred by v(1,1), in col 2 by v(1,2).
  • In order to count the number of row items you check the upper boundary of its first dimension) via UBound(v,1) or even shorter via Ubound(v)
  • In order to count the number of columns you check the upper boundary of its second dimension) via UBound(v,2) (here the argument 2 is necessary!)
  • A comparation using vbNullString can be preferred in coding to "" as it takes less memory (c.f. @PEH 's answer) .

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57743

If you meant to check by each row like (B2 and C2) then (B3 and C3), then you could do it like this.

Sub Fill_Rows()
    Dim iRow As Long
    For iRow = 2 To 5000
        If Cells(iRow, "B").Value = vbNullString And Cells(iRow, "C").Value <> vbNullString Then
            Cells(iRow, "B").Value = "Correct"
        End If
    Next iRow
End Sub

Upvotes: 1

Related Questions