Loop Through Datas in VBA

Question

Data for my problem are provided below:

#define apple
#define mango
#define banana
animals{
dog,
cat,
bird
#define guava
#define avocado

Strings with #define are set to color index = 50, the rest are set to color index = 23

What I want to do is that I want to find the word "animals", and I would like it to look at the next column up until the next column has a #define. If the next column has no #define, it numbers it with '0', the next column '1' and so on and so forth thus the result would be like this:

#define apple
#define mango
#define banana
animals{
dog, 0
cat, 1
bird 2
#define guava
#define avocado

My code is as follows but it does not move to the next column as planned.

For Each cell In DataRange.Cells
    If InStr(cell.Value, "animals") <> 0 Then
        Do Until ActiveCell.Font.ColorIndex = 50
            cell.Value = cell.Value & o
            o = o + 1
            cell.Offset(0, 1).Select
        Loop
    End If
Next

Thanks

Vityata · Answer 1 · 2017-12-19T11:36:20.653

In general, your error in the code is that you do not select the cell, on which you are looping through. This is probably a good example to understand how loops work.

I don't want to be the one writing this code, but try:

For Each cell In DataRange.Cells
    cell.Select
    If InStr(cell.Value, "animals") <> 0 Then
        Do Until ActiveCell.Font.ColorIndex = 50
            cell.Value = cell.Value & o
            o = o + 1
            cell.Offset(0, 1).Select
        Loop
    End If
Next

If it works, try to refactor it, after reading this: How to avoid using Select in Excel VBA

Try to avoid the Selection by setting the ActiveCell to a Range and not using Select neither ActiveCell. You may consider a second Range variable for the horizontal loop. Give it a try!

Something like this will be probably ok:

Public Sub TestMe()

    Dim myCellH         As Range
    Dim myCellV         As Range
    Dim DataRange       As Range
    Dim o               As Long

    Set DataRange = Range("A1:A10")

    For Each myCellH In DataRange.Cells
        If InStr(myCellH, "animals") <> 0 Then
            Set myCellV = myCellH
            Do Until myCellV.Font.ColorIndex = 50
                myCellV.Value = myCellV.Value & o
                o = o + 1
                Set myCellV = myCellV.Offset(0, 1)
            Loop
        End If
    Next
End Sub

Loop Through Datas in VBA

1 Answers1