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]

Sorry for the delay. That doesn't work either.

 

[Fluff]

In what way?

 

[ctouchberry]

In the same way. The loop keeps "looping" in VBA, but the TagRowCounter variable stays fixed at 318, which is the first "value, value" cell.

If I try to really run the macro, Excel will lock up.

 

[Fluff]

Oops, missed an "i"

Loop Until Sheets("Component").Cells(TagRowCounter [COLOR=#ff0000]+ i[/COLOR], 1) = False

 

[ctouchberry]

The i value in the Loop Until statement is counting up, but the TagRowCounter variable is still at 318.

 

[Fluff]

It's meant to be. That's keeping track of the row you want to copy to, whilst the TagRowCounter+i is keeping track of the row you are looking at.

 

[ctouchberry]

Ok. There's still more content past row 318 that I need to reference in. Not sure what needs to change in the logic.

 

[Fluff]

Have you tried it? If so what is or is not happening.
Please remember that I cannot see your workbook, or see what you are trying to achieve.
So without clear & concise details of what is going on, I cannot help.

 

[ctouchberry]

Yeah I've tried doing F8 through the VBA, and the Loop in question works from a syntax POV.

The reason why we implemented the following code was to not reference values from Component that were as "value, value", into JCX. The code also needs to not load blank placeholder rows of those omitted values, and skip right to the next valid value.

Right now, the code doesn't load those "value, value" cells, but it doesn't proceed down the source content column once it sees the first "value, value" cell in the source column.

Does this help?

Do
    'Tag Number
        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

 

[Fluff]

I've knocked up some rough test data & it's working for me.
When you step through the code & you encounter a cell containing a comma, does the code goto the Else line & then i=i+1 ?