CODEWITHSUNDEEP
vbaexcel
user7568042
user7568042

Reputation: 244

Error adding numeric to text

I am able to loop with numbers, but with text the Do While does not loop through.

Dim i As Integer
i = 1

Do While Cells(i, 1).Value <> ""
     Cells(i, 2).Value = Cells(i, 1).Value + 1
     i = i + 1
Loop

How do I implement this?

Upvotes: 2

Views: 79

Answers (2)

CallumDA
CallumDA

Reputation: 12113

@A.S.H has spotted the real error here, but a few other things to note:

  1. When looping through a column/row like this, it's usually better to use a For Each loop.
  2. Using Cells on it's own makes an implicit reference to the ActiveWorksheet. It's best to explicitly reference which cells you are referring to, e.g. ThisWorkbook.Worksheets("Sheet1").Cells

Try this:

Sub Test()
    Dim rng As Range, r As Range

    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A100") 'range to loop through

    For Each r In rng
        If r.Value <> vbNullString and IsNumeric(r.value) Then
            r.Offset(0, 1).Value = r.Value + 1
        End If
    Next r
End Sub

Upvotes: 3

A.S.H
A.S.H

Reputation: 29362

You can loop like that and you don't have any error when it comes to VBA syntax. The only problem is that the expression:

Cells(i, 1).Value + 1

will raise a runtime error when Cells(i, 1).Value is not convertible to a number. That's simple, you have a type mismatch when Cells(i, 1).Value is a string like "abcd", you can't convert that variant to a number in order to add 1 to it.

In this situation it is not the fault of the macro code, but of the data.

To deal with heterogeneous data you can possibly check if a value is numeric before manipulating it as a number, like this:

Do While Cells(i, 1).Value <> ""
    If IsNumeric(Cells(i, 1).value) then Cells(i, 2).Value = Cells(i, 1).Value + 1
  ' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    i = i + 1
Loop

Upvotes: 4

Related Questions