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

 

[Fluff]

If you run this on the component sheet, it should give you an extra row for each floor that has 2 or more rooms

Sub SplitonComma()

   Dim Cnt As Long
   Dim Splt As Long
   
   For Cnt = Range("M" & Rows.Count).End(xlUp).row To 2 Step -1
      If InStr(Range("M" & Cnt), ",") Then
         Splt = UBound(Split(Range("M" & Cnt), ","))
         Rows(Cnt + 1).Resize(Splt).Insert
         Rows(Cnt).Resize(Splt + 1).FillDown
         Range("M" & Cnt).Resize(Splt + 1).Value = Application.Transpose(Split(Range("M" & Cnt), ","))
      End If
   Next Cnt
      
End Sub

This looks at col M, change to suit

 

[ctouchberry]

I'm not sure how to run that on the Component sheet. Can you explain?

 

[Fluff]

What column are your room numbers in? and does your data start in row 2?

 

[ctouchberry]

Actually, after consulting with my boss, I don't need to reference in Rooms that have two values...only single values.

That does complicate things though, as I need to tell Excel that. Right now it just pulls data from Column E, starting from row 2.

The complication is that while I want to exclude a "1A08, 1A16", I need to include something like "Laundry Room", or "Room 203".

Also, I'm not guaranteed to have "value, value" explicitly as a thing to exclude, it might be "value,value" or "value.value".

I realize that might complicate it more.

 

[Fluff]

Could you please supply some data that shows what you do and don't want.
Preferably something that can be copied, rather than images.
Tolls to help post data can be found here
https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[ctouchberry]

I'm having issues getting the image attachment tools properly loaded in Excel.

Best I can do right now is show my code and link to something like Imgur.

 

[Fluff]

The problem with images, is that they tend to only give part of the story, & they cannot be copied. Meaning that any potential helper has to spend time re-creating the data you've already got.
That said, if all you can do is show an image, then it will have to do

 

[ctouchberry]

I'll work on getting a tool loaded. Thanks for the help though, I appreciate it.

The double-values on Component need to be omitted from loading onto JCX, but in a manner that still allows a value such as "Laundry Room" to be loaded. I hope that makes sense.

JCX Sheet
https://imgur.com/a/BUD3v
Component Sheet
https://imgur.com/a/uqLvU
Space Sheet
https://imgur.com/a/ZWrBz

'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

The Room code isn't really dynamic right now, just puts a certain column in a different column on a different sheet.

 

[Fluff]

Your images don't show anything with Laundry room.