Using If Not Is Empty to return a string

[LCarney]

Ok, so I'm new to VBA and am still figuring things out. I have a huge inventory audit I have to do monthly at work. We count all of our cars and they can fit into 5 categories. I need to label the categories to find any cars that didn't go in any of the categories and find out which cars I need to find. Below is kind of a dummy version of our actual template that I'm working on at home, but basically if a cell in B:F has ANY data in it, it need the status in column A to return with "Scanned", "Sold", "Wholesale", "InTransit" or "Whatever" (I forgot what the 5th category is). But one problem is, it's possible for column C and B or D and B to BOTH have data in them in which case I need to return the string I would use for C or D.

I was trying to run a sub like this; just to see if I could make it work for even one column, but it won't return anything. I'm sure I'm missing something small.
Sub sort()
If Not IsEmpty("b2") Then
a2 = "scanned"
End If

End Sub

any pointers are apprecated. TIA

 

[Fluff]

Any particular need for a macro?
You can do that with a simple formula

+Fluff New.xlsm
	A	B	C	D	E	F
1	Status	Scanned	Sold	Wholesale	Transit	Whatever
2
3	Scanned	vin123
4	Wholesale			vin234
5
6	Sold, Whatever		vin654			vin654
7
8	Scanned, Wholesale	vin789		vin789
9
10
Result

Cell Formulas
Range	Formula
A2:A10	A2	=TEXTJOIN(", ",1,IF(B2:F2<>"",$B$1:$F$1,""))

 

[LCarney]

No, no particular need for a macro actually. I just spent so long trying to do a nested if statement i was told a macro might be cleaner, but this would probably work and be a lot simpler! I'm just getting a name errror on my end when I copy your formula into my spreadsheet? I think maybe my excel version is older and doesn't have Textjoin?

 

[Fluff]

If you are using 365, as shown in your profile, then you have the textjoin function.

 

[LCarney]

maybe I just don't have it at my house...I'll try it at the office tomorrow!Thanks!

 

[Fluff]

Ok, another option would be to use a UDF

Function LCarney(Rng As Range) As String
   Dim Cl As Range
   Dim i As Long
   i = Application.Caller.Row - Rng.Row
   For Each Cl In Rng.Offset(i)
      If Cl <> "" Then LCarney = LCarney & Cl.Offset(-i).Value & ", "
   Next Cl
   If LCarney <> "" Then LCarney = Left(LCarney, Len(LCarney) - 2)
End Function

+Fluff New.xlsm
	A	B	C	D	E	F
1	Status	Scanned	Sold	Wholesale	Transit	Whatever
2
3	Scanned	vin123
4	Wholesale			vin234
5
6	Sold, Whatever		vin654			vin654
7
8	Scanned, Wholesale	vin789		vin789
9
Result

Cell Formulas
Range	Formula
A2:A8	A2	=LCarney($B$1:$F$1)

 

[LCarney]

I have Textjoin on my work software, but it gives me a value error when I update the range to apply to my workbook. (G:Q)
Is it possible to use Textjoin to skip the columns your checking? I am actually checking columns G,H,N and Q. but if I have to do a range I can make it work.

 

[Fluff]

Try array entering it, click into the formula bar & then use Ctrl Shift Enter, rather than just enter.

 

[LCarney]

AH! That did it! Thanks!

 

[Fluff]

You're welcome & thanks for the feedback