VBA version of IF function?

**cicicle** · 02-04-2019, 10:24 AM

Hi all,

I am currently trying to concatenate certain cells based on the value of a particular cell (I.e. if A2 = X, concatenate B2, C2, and D2 , but if A2 = Y, concatenate B2, C2, and E2), separated by commas.

I have managed to do this with an IF function below

=IF(A2="X",B2&"in, "&C2&", "&D2,IF(A2="Y",B2&"in, "&C2&", "&E2))

I know in the future I am going to have more values than just x and y, and I will have to concatenate more columns of info based on them, so I didn't want an IF function that was 3 miles long in case someone has to go back and edit they don't have to search through a formula in a cell.

Is there a way to achieve the same thing in VBA?

(Also, column B in the table specifies a length in inches which is important in the description (the result of concatenating the cells), is there a way to specify units for one column but not others?)

Thanks,

cicicle

**Special-K** · 02-04-2019, 11:06 AM

If A2 is only going to be X or Y you could shorten that formula (as it stands) to

=B2&"in, "&C2&", "&IF(A2="X",D2,E2)

since in both cases B2 and C2 are always concatenated

**cicicle** · 02-04-2019, 11:19 AM

That is sort of the problem it is not always going to be x or y, I might find I need to add a z, or even a thru w criteria that select even more cells, and then my if function will keep growing and it will not be easy for a maintenance standpoint to go through the formula and find the parts I want to change and add more criteria.

That is why I was thinking VBA would be better at least from an organization standpoint, and also automation so I would not have to go and copy the changed formula to all of the cells it would simply update as I change the code.

**6StringJazzer** · 02-04-2019, 12:52 PM

Here is a user-defined function (UDF) that will do what you want, and if you know VBA is simple to update.

The function takes as input the cell that can be X or Y. This will cause it to recalculate when that cells changes, so avoids requiring this to be a volatile function. However that means it will not automatically recalculate if the cells in B, C, D, or E change.

Code below, example attached.

Option Explicit

Public Function CondConcat(TestCell As Range) As String

   With TestCell.Parent
   
      Select Case TestCell
      
         Case "X"
            CondConcat = .Cells(TestCell.Row, "B") & "in, " & .Cells(TestCell.Row, "C") & ", " & .Cells(TestCell.Row, "D")
         
         Case "Y"
            CondConcat = .Cells(TestCell.Row, "B") & "in, " & .Cells(TestCell.Row, "C") & ", " & .Cells(TestCell.Row, "E")
         
      
      End Select
   
   End With
   
End Function