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

ctouchberry

New Member
Joined
Dec 4, 2017
Messages
40
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

Code:
'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
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
If you run this on the component sheet, it should give you an extra row for each floor that has 2 or more rooms
Code:
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
 
Upvote 0
What column are your room numbers in? and does your data start in row 2?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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

Code:
'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.
 
Upvote 0
Your images don't show anything with Laundry room.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top