Click here to Skip to main content
14,601,227 members
Rate this:
Please Sign up or sign in to vote.
See more:

I have a problem in my for loop. I want to loop through each excel row in my excel table and find a value in specific column, when I find the value I edit this row's 25th column.

For this purpose I wrote a VBA code. But when I run it, the for loop not fill the cells one by one. I attached the screen shot of my columns when I run the macro.

Thanks in advance.

Link of screen-shot : Link[^]

Edit: I found out that, the table contains 156 rows but in for loop after 86th row the cell value gets empty. Why is that?

What I have tried:

Private Sub FindAndReplaceButton_Click()
    Dim s As String
    s = WorkCommandNumbersInputField.Value
    Dim wcns() As String
    wcns = Split(s, ",")
    Dim wrksht As Worksheet
    Dim objListObj As ListObject
    Dim objListRows As ListRows
    Set wrksht = Application.ActiveSheet
    Set objListObj = wrksht.ListObjects(1)
    Set objListRows = objListObj.ListRows
    Dim rowCount As Integer
    rowCount = objListRows.Count
    For Each wcn In wcns
        For i = 1 To rowCount
            Dim FindedRow As Range
            Set FindedRow = objListObj.ListRows(i).Range
            Dim FindedCell As Range
            Set FindedCell = FindedRow.Cells(i, 2)
            If FindedCell.Value = wcn Then
                Dim ChangedCell As Range
                Set ChangedCell = FindedRow.Cells(i, 25)
                If (ChangedCell.Value = "") Or (ChangedCell.Value = "---") Then
                    ChangedCell.Value = ShelfNumberInputField.Value
                ElseIf ChangedCell.Value <> ShelfNumberInputField.Value Then
                    ChangedCell.Value = ChangedCell.Value + "-" + 
                End If
            End If
        Next i
End Sub
Updated 30-May-19 1:41am
Graeme_Grant 7-Sep-17 5:41am
any reason why you just don't use the built-in Excel If(...) function in the 25th column of each row?
Onur ERYILMAZ 7-Sep-17 5:51am
Because If function always sets the cell 'False' or 'Empty', what I wanted to do if the condition is not match, do nothing. So built in if function does not offer otherwise do nothing option.
Graeme_Grant 7-Sep-17 5:58am
Then you don't understand Excel. For example:
=IF(B7>15, "Big", "Small")

If B7 = 10, then the cell will contain "Small", if B7 = 20 then the cell will contain "Big".

You can take it a step further and use cell reference for the true/false states:
=IF(B7>15, D4, C4)
Onur ERYILMAZ 7-Sep-17 6:11am
Bu the problem is I don't want to write "Small", I want this =IF(B7>15, "Big", otherwise leave the cell unthouched)
Graeme_Grant 7-Sep-17 6:13am
Use a null string then, simple!
Onur ERYILMAZ 7-Sep-17 6:22am
I don't want a null string eighter.
Graeme_Grant 7-Sep-17 6:27am
Good luck then. I gave you a far simpler solution than you are trying to implement yourself. That is what you asked for, that is what you got.
Onur ERYILMAZ 7-Sep-17 7:09am
You gave me nothing ;)
Richard MacCutchan 8-Sep-17 2:33am
Yes he did, but it seems that you do not understand the basics of VBA.
ZurdoDev 7-Sep-17 9:10am
=IF(B7>15, "Big", "")

That will leave the cell untouched.
Graeme_Grant 7-Sep-17 18:41pm
Thanks. He does not realize that an empty cell IS a null string. I doubt that he even tried it.
Pat O'Brien 11-Sep-17 9:28am
but a cell with a formula is NOT a null, even if it returns a null string.

Sub test()
Dim rng As Range
Set rng = [testcell]
Debug.Print "x+CellValue+x", "Has Formula", "IsNull", "Formula"
Debug.Print "x" & [testcell] & "x", rng.HasFormula, IsNull(rng), rng.Formula

End Sub

Run the above and you get:

x+CellValue+x Has Formula IsNull Formula
xx True False =IF(G7>G8,"big", "")

ZurdoDev 7-Sep-17 9:11am
Debug your code. Then you'll see exactly what is happening.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

When your code don't behave as you expect, use the debugger to see what it does, you will know the why by inspecting the variables.
I found out that, the table contains 156 rows but in for loop after 86th row the cell value gets empty. Why is that?

Since your code do not depend on the row number, your code behavior depend on the sheet contain. You are the only one that can do something.
There is a tool that allow you to see what your code is doing, its name is debugger. It is also a great learning tool because it show you reality and you can see which expectation match reality.
When you don't understand what your code is doing or why it does what it does, the answer is debugger.
Use the debugger to see what your code is doing. Just set a breakpoint and see your code performing, the debugger allow you to execute lines 1 by 1 and to inspect variables as it execute.

Debugger - Wikipedia, the free encyclopedia[^]
Debugging in Excel VBA - EASY Excel Macros[^]
MS Excel 2013: VBA Debugging Introduction[^]
How to debug Excel VBA - YouTube[^]

The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't find bugs, it just help you to. When the code don't do what is expected, you are close to a bug.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100