Vba loop problem in listobject(excel table)

Question

1.00/5 (3 votes)

See more:

Hi,

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:

VB

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 + "-" + 
                    ShelfNumberInputField.Value
                End If
            End If
        Next i
    Next
End Sub

Posted 6-Sep-17 23:35pm

Onur ERYILMAZ

Updated 30-May-19 1:41am

Patrice T

v4

Add a Solution

Comments

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Patrice T · Answer 1 · 2017-09-07T12:32:00

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.

Quote:
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.