[RESOLVED] VB6 For..Next not working

[Shuter]

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

[MarkT]

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

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

[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 ..

[Code Doc]

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

[si_the_geek]

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

[Shuter]

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.