I'm relatively new to Excel VBA, and I'm not sure what code I need

[ctouchberry]

I'm relatively new to VBA, and have run into a "not knowing what code I need" section. I might have used the wrong terminology in my explanation, I apologize in advance.

I'm loading Room and Floor data from Component to JCX (with a cross-sheet check on the Floor data from Space). The problem is, for some instances in the source data on Component, the Rooms are listed as "Room 1, Room 2" (in one cell). That same data is listed in Space as "Room 1", "Room 2" (in cells on top/below each other).

If I had my way on Component, it would be Room 1 in one cell, and then Room 2 in the cell below it. But I can't change the source data.

I need to make my "Room" logic in the first phase account for a "Value1, Value2" situation, but not error if there's only one value. In this updated logic, I need each "Value2" to be in the cell below "Value1", and then copy the associated Tag Number (on the JCX screenshot, that would be the values in the 3rd column.

I also need to apply that same logic to the second phase, as the VLookup right now is looking at the "Value1, Value2" cell in Component, comparing it with a "Value" cell in Space, erroring, and putting a #N/A on the Floor cell in JCX associated with those specific Rooms.

Component
https://imgur.com/a/rI2Pi
Space
https://imgur.com/a/LC5zM
JCX
https://imgur.com/a/ktojj

'First Phase[COLOR=#242729][FONT=Arial]
[/FONT][/COLOR]'Tag Number and Room
'Row Column
Do
    'Tag Number
    Sheets("JCX").Cells(TagRowCounter, 10).Value = Sheets("Component").Cells(TagRowCounter, 1).Value
    'Room
    Sheets("JCX").Cells(TagRowCounter, 9).Value = Sheets("Component").Cells(TagRowCounter, 5).Value
    TagRowCounter = TagRowCounter + 1
        
Loop Until Sheets("Component").Cells(TagRowCounter, 1) = False
Sheets("JCX").Columns("A:AM").AutoFit

'Second Phase
'Floor
'Row Column
TagRowCounter = 2
Do
    Sheets("JCX").Cells(TagRowCounter, 8).Value = Application.VLookup(Sheets("Component").Cells(TagRowCounter, 5), Sheets("Space").Range("A:E"), 5, 0)
    TagRowCounter = TagRowCounter + 1
    
Loop Until Sheets("Component").Cells(TagRowCounter, 5) = False
Sheets("JCX").Columns("A:AM").AutoFit

 

[ctouchberry]

I can take screenshots and link the Imgur uploads here. Would that work for you?

 

[Fluff]

Not really, that only shows me what something "looks like", rather than what is actually in a cell.
Something else to try is

    Sheets("JCX").Range("A:CZ").NumberFormat = "@"
    i = 0
Do
    If Not InStr(Sheets("Component").Cells(tagrowcounter + i, 5).Value, ",") > 0 Then
        Sheets("JCX").Cells(tagrowcounter, 10).Value = Sheets("Component").Cells(tagrowcounter + i, 1).Value
        'Room
        Sheets("JCX").Cells(tagrowcounter, 9).Value = Sheets("Component").Cells(tagrowcounter + i, 5).Value
        tagrowcounter = tagrowcounter + 1
    Else
        i = i + 1
    End If
Loop Until Sheets("Component").Cells(tagrowcounter + i, 1) = False
MsgBox tagrowcounter + i & vbLf & "|" & Sheets("Component").Cells(tagrowcounter + i, 1) & "|"

What does the msgbox say?

 

[ctouchberry]

I put the new line in place of where the old msgbox line was, and I get a Line 326, |Door1A10| box. This repeats on the loop.

 

[Fluff]

It needs to go after the loop, as I've got it in post#42

 

[ctouchberry]

If it goes after the loop, it never does anything, because the Loop Until statement is never satisfied in my situation.

 

[Fluff]

Must admit I don't understand why that is happening.
Would you be willing to share the workbook, via OneDrive, or DropBox?

 

[ctouchberry]

Unfortunately, I can't share the workbook with you, company rules.

I'll see if i can get the HTML Maker thing to install correctly.

 

[Fluff]

Let's do it the other way round & see what happens.
I've loaded a test file here
https://1drv.ms/x/s!AtYrlcV2NWPFgQMa_ICogB5kJX5I

See if the macro works for you with the test data. If so try pasting copying your data across & then see what happens.

 

[ctouchberry]

Yeah our code is a perfect match, but I'm still not able to write any data past row 318 in the Component sheet.

Unfortunately for me, Column E (the Room column) has about 5500 rows in it, and I can't seem to get past row 318.

I appreciate your help though.

 

[Fluff]

Did the test file I supplied work as you'd expect?
If so, did you try copying your data into the test file & run it?