I'm not sure what's wrong/missing with my code

[ctouchberry]

I'm not sure what's wrong with my code. The loop in question "loops", but the TagRowCounter variable stops increasing at 318. This is right where part of the logic starts "working", or NOT working in this case.

What I'm trying to do is list Tag Numbers with their respective Rooms. Every Tag Number has a Room, and a Tag Number can only be associated with 1 Room. That "listing" part works perfectly.

What I'm having issues with is about the "Room, Room" values shown on the Component 2 attachment. Those Rooms need to be NOT listed, and also have the spot that would be occupied by them..by the next "Room" value. In this case, there's about 150-200 "Room, Room" values in a row that need to be skipped entirely. Unfortunately, I can't tell the program to skip certain lines, as the source content is fluid and will change. It has to be smart enough to figure it out without specific row numbers noted.

There's about 4500 rows to be listed after the "Room, Room" values stop, and this glitch is preventing those from being listed, as well as pausing the rest of the program.

I apologize for not having the screenshots shown better, I'm having issues getting the HTML Maker tool properly installed.

The single "Room" values that worked so far are shown in Component 1. The final results are listed on JCX. I have not attached a picture of the "not-listing" result on the JCX sheet, as it's just a blank cell.

JCX
https://imgur.com/a/3DZFm
Component 1
https://imgur.com/a/J3lvs
Component 2
https://imgur.com/a/SujzZ

'Tag Number and Room
Sheets("JCX").Range("A:CZ").<wbr>NumberFormat = "@"
i = 0
Do
    'Tag Number
        If Not InStr(Sheets("Component").<wbr>Cells(TagRowCounter + i, 5).Value, ",") > 0 Then
        Sheets("JCX").Cells(<wbr>TagRowCounter, 10).Value = Sheets("Component").Cells(<wbr>TagRowCounter + i, 1).Value
    'Room
        Sheets("JCX").Cells(<wbr>TagRowCounter, 9).Value = Sheets("Component").Cells(<wbr>TagRowCounter + i, 5).Value
        TagRowCounter = TagRowCounter + 1
    Else
        i = i + 1
    End If
Loop Until Sheets("Component").Cells(<wbr>TagRowCounter + i, 1) = False

 

[wideboydixon]

I tried your code and it seemed to work OK with a cut down data set. Having said that, I found the logic a little odd so perhaps you could try something like this:

Dim lastRow As Long
Dim thisRow As Long
Dim nextRow As Long

Sheets("JCX").Range("A:CZ").NumberFormat = "@"
lastRow = Sheets("Component").Cells(Sheets("Component").Rows.Count, 5).End(xlUp).Row
nextRow = 2
For thisRow = 2 To lastRow
    If InStr(Sheets("Component").Cells(thisRow, 5).Value, ",") = 0 Then
        Sheets("JCX").Cells(nextRow, 10).Value = Sheets("Component").Cells(thisRow, 1).Value
        Sheets("JCX").Cells(nextRow, 9).Value = Sheets("Component").Cells(thisRow, 5).Value
        nextRow = nextRow + 1
    End If
Next thisRow

WBD

 

[ctouchberry]

I see you took the loop functionality out. Any reason why you did that?

 

[wideboydixon]

I replaced your Do/Loop Until with a For/Next loop based on the previously found last cell on the Component sheet.

WBD

 

[ctouchberry]

That worked perfectly!

And now there's another issue, haha.

For some reason, some of the Floors aren't being listed, I'm not sure why. Let me know if you need explanation on what I'm trying to do here.

JCX
https://imgur.com/a/NKH7c
Space
https://imgur.com/a/GfJ7C

'Floor
TagRowCounter = 2
Do
    Sheets("JCX").Cells(<wbr>TagRowCounter, 8).Value = Application.VLookup(Sheets("<wbr>Component").Cells(<wbr>TagRowCounter, 5), Sheets("Space").Range("A:E"), 5, 0)
    TagRowCounter = TagRowCounter + 1
Loop Until Sheets("Component").Cells(<wbr>TagRowCounter, 5) = False

 

[wideboydixon]

Tricky to say without the actual data (and not just a picture). Note that Excel may also interpret "1E17" as 100,000,000,000,000,000 which is probably why there are green triangles in the upper right portion. Most logical reason is that the cell contents don't match exactly causing the #N/A error.

WBD

 

[ctouchberry]

How could I tell Excel to automatically format a certain column as Text?

 

[wideboydixon]

This is a new question; I suggest you create a new post as there are users who can deal with this better than I can.

WBD

 

[ctouchberry]

Ok thanks for your help.