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]

Right now, TagRowCounter is 318 and i is 59.

My step results are If Not line, Else line, i = line, End If line, Loop Until line, repeat.

 

[Fluff]

That's fine.
What happens when you get to another value that does not contain a coma?

 

[ctouchberry]

I can't get past that first comma value, that's the issue.

 

[Fluff]

Add this

    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
      [COLOR=#ff0000]MsgBox Sheets("Component").Cells(tagrowcounter + i, 5).Value & vbLf & "Row " & tagrowcounter + i[/COLOR]
        i = i + 1
    End If
Loop Until Sheets("Component").Cells(tagrowcounter + i, 1) = False
End Sub

Every time you encounter a cell with a , a msgbox will appear showing you the cell value & the row number. Does the message change?

 

[ctouchberry]

The box comes up on Row 318, with "1B11, 1B04" as values. And again on 319 with "1A03, 1AC5".

This is consistent with the source content on Component. I assume this will happen until there's a cell with no comma.

 

[Fluff]

I assume this will happen until there's a cell with no comma.

That's right, but it shows that the code is doing what it's meant to.
Obviously having the msgbox come up all the time is going to get annoying, so you can simply delete the line in red.

 

[ctouchberry]

The code may be doing what it's supposed to do, but that doesn't mean it's doing what I need it to do.

I just need to skip the comma values entirely. If a value order went like this in Component: Normal, Normal, Normal, Comma, Comma, Normal, Normal...then I just need it to be Normal, Normal, Normal, Normal, Normal. With no placeholder rows for those Comma values. Sorta like the "hide" function in Excel.

 

[Fluff]

Does it copy all the "normal" values to the JCX sheet?

 

[ctouchberry]

It copies the normal values until the 318 row, then all copying stops.

 

[Fluff]

Without being able to see your data, I'm not sure that I can help any further. With data like this

Excel 2013 32 bit
	A	B	C	D	E
1	A1	2	3	4	5
2	A2				a
3	A3				b
4	A4				c
5	A5				d
6	A6				e,e
7	A7				f,f
8	A8				g
9	A9				h
10	A10				i,i
11	A11				j, j
12	A12				k
Component

I get

Excel 2013 32 bit
	H	I	J
1
2		a	A2
3		b	A3
4		c	A4
5		d	A5
6		g	A8
7		h	A9
8		k	A12
JCX