[RESOLVED] VB6 For..Next not working

Hi everybody,

I was trying to use a macro in Excel 03 to convert a huge database of data. Those data contain a combinated text and numerical string. First the text string needs to be exported and pasted in to cells to the right. So I created a following loop to find out if the text-numerical string contains the searched data:

Sub Midovanie4()
Dim InitialRow As Integer
Dim InitialColumn As Integer
Dim Data As Variant
Set Cells1 = Range("B4:H9")

InitialRow = ActiveCell.Row
InitialColumn = ActiveCell.Column + 9

For Each Data In Cells1
If InStr(Data, "Ar") > 0 Then _
Data.Activate
Cells(InitialRow, InitialColumn).Value = "0"
Next Data
End Sub

If the string contains the searched data (Ar) then the cell is activated and 9 columns to the right from this position "0" is written.

But it doesnt work. Each time i run it it fills only the last cell of the range cells1 containing the searched data. All the other cells that contain the data too are kept untoched. Has someone a idea how to fix it?

Thank you
Shuter

I'm not sure if this is it but I would try moving where you are setting the InitialRow and InitialColumn variables.

Code:

---

Sub Midovanie4()
Dim InitialRow As Integer
Dim InitialColumn As Integer
Dim Data As Variant

    Set Cells1 = Range("B4:H9")

    For Each Data In Cells1
        If InStr(Data, "Ar") > 0 Then
            Data.Activate
            InitialRow = ActiveCell.Row
            InitialColumn = ActiveCell.Column + 9
            Cells(InitialRow, InitialColumn).Value = "0"
        End If
    Next Data
End Sub

---

Wow, this really worked!
I was thinking about this before, but was not sure.
Thank you very much, MarkT!!! I was stuck with this the whole day ..

Quote:

---

Originally Posted by Shuter

Wow, this really worked!
I was thinking about this before, but was not sure.
Thank you very much, MarkT!!! I was stuck with this the whole day ..

---

Please mark your thread Resolved. Thanks!

MarkT :thumb:

Thread moved to the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt

Guys, thank you for the advices. I am new to this forum and kind of new to VB.
Thread is marked as Resolved. And next time I write to Office Development/VBA section.