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. I'm saying that the new logic needs to allow something like that. A phrase with two texts and a space between.

This data comes straight out of Autodesk Revit, and it pretty much gives you everything. Sometimes, Rooms are numeric phrases like "204", but I've also seen plenty of "Conference Room", "Hangar Bay", "Mechanical Room", etc.

 

[Fluff]

The code I supplied will look at every row in the Component sheet & if it has a "," in col M (you'll need to change that to col E) it will split that row onto multiple rows, 1 row for each value.
as per

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.

You can then run your existing macro/s.

If that will not work for you, could you please re-explain your needs, as I didn't understand the first time.

 

[ctouchberry]

Sorry, I don't need the values split into two rows anymore. I asked my boss about it, and found out that I don't need to worry about splitting those areas. The double-value is due to the building designer counting the same door for each room that it separates.

What I do need to do, is omit those double-value cells from loading into the JCX sheet.

Those double-values correlate to an interior door, and my practice (Building Commissioning) is only concerned with exterior doors. So I need to include doors that don't open to another room (exterior), and omit the others.

Does that help?

 

[Fluff]

I don't really understand what you need, but try

   Do
    If Not InStr(Sheets("component").Cells(TagRowCounter, 5).Value, ",") > 0 Then
    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
    End If
   Loop Until Sheets("Component").Cells(TagRowCounter, 1) = False

If col E on components contains a comma, then that row will be ignored

 

[ctouchberry]

That did ignore the first double-value cell, but now the loop won't progress past it. The loop itself still functions, but it won't reference in any more data.

I can basically F8 all day, but nothing more happens once it hits that first comma.

 

[Fluff]

Put the end if in the wrong place

   Do
   If Not InStr(Sheets("component").Cells(TagRowCounter, 5).Value, ",") > 0 Then
      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
   End If
   TagRowCounter = TagRowCounter + 1
   
   Loop Until Sheets("Component").Cells(TagRowCounter, 1) = False

 

[ctouchberry]

Rats! Thanks for the tip.

It's running now, takes a few minutes. I'm noticing that while the new logic skips writing the double-values, it leaves the space for it. Is there a way to take out the empty rows?

 

[Fluff]

Think I've got this the right way round

Do
   i = 0
   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, 1) = False

 

[ctouchberry]

That gives me the loop-stop error that I had before. The loop works, but it doesn't load any new data once you hit the comma values (I can see TagRowCounter not changing).

This also results in Excel locking up when you just "run" the macro.

Do
    'Tag Number
    Sheets("JCX").Range("A:CZ").NumberFormat = "@"
    i = 0
    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, 1) = False

 

[Fluff]

How about

    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, 1) = False