Need Alternate Code or Formula

[DanielleVBANewbie]

Hi friends,

I have the formula below, it is in column BR. It goes from column V to BN
and it should be copied down from row3 to Row 700. The problem I am having is
the formula is too long for the cell, what would be the equivalent VBA code
or is there a different formula that will work for this?

Thanks for any assistance you can provide.

=IF(V3="X",V$1,â€â€œ )&†“&iF(W3="x",W$1,““)&†“&IF(X3="x",X$1 ,â€â€œ)&â€
“&IF(Y3="x",Y$1 ,â€â€œ)&†“&IF(Z3="x",Z$1&†“&†‘,â€â€)&†“&IF(AA3="x",AA$1 ,â€â€œ)&â€
“&IF(AB3="x",AB$1 ,â€â€œ)&†“&IF(AC3="x",AC$1 ,â€â€œ)&†“&IF(AD3="x",AD$1 ,â€â€œ)&â€
“&IF(AE3="x",AE$1 ,â€â€œ)&†“&IF(AF3="x",AF$1 ,â€â€œ)&†“&IF(AG3="x",AG$1 ,â€â€œ)&â€
“&IF(AH3="x",AH$1 ,â€â€œ)&†“&IF(AI3="x",AI$1 ,â€â€œ)&†“&IF(AJ3="x",AJ$1 ,â€â€œ)


The sheet looks like this:

v w BR
1Criteria1 Criteria 2
2 x =if(V2="x",V$1,"")&" "&if(W2="x",W$1,"")
3 x x =if(V3="x",V$1,"")&" "&if(W3="x",W$1,"")
4

 

[Don Guillett]

This will look in rows 3-6 for x in columns 22-36 and indicate whats in row
1. Is that what you need?

Sub longifs()
For r = 3 To 6
ms = ""
For i = 22 To 36
If UCase(Cells(r, i)) = "X" Then ms = ms & " " & Cells(1, i)
Next i
MsgBox ms
Next r
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Here is a UDF that might be of help to you...

Function ConcatenateCells(R As Range) As String
Dim C As Range
For Each C In R
If C.Value Like "[Xx]" Then
ConcatenateCells = ConcatenateCells & Cells(1, C.Column).Value
End If
Next
End Function

To install it, press Alt+F11 to go into the VB editor, click Insert/Module
on its menu bar and copy/paste the above code into the code window that
appears.

To use it, just pass it the range of cells you want to look at to see if
there is an X in it into the function called from a worksheet cell; if there
is, the cell in that column's 1st row will be included in the concatenation.
So, use this formula to process the cells V3:AJ3, just place this formula
into a cell...

=ConcatenateCells(V3:AJ3)

You can copy this formula down as needed.

Rick

 

[DanielleVBANewbie]

Hi Don,

Yes, that is exactly what I need, however, when I put it in, I got an error
variable not defined?

 

[Don Guillett]

Option Explicit
Sub longifs()
Dim r As Long
Dim i As Long
Dim ms As String
For r = 3 To 6
ms = ""
For i = 22 To 36
If UCase(Cells(r, i)) = "X" Then ms = ms & " " & Cells(1, i)
Next i
MsgBox ms
Next r
End Sub

 

[DanielleVBANewbie]

Hi Don,

This works great as a pop up but is there anyway to make that information go
into column DR for the corresponding row? That way it is populated for each
row (there are 700 rows of data). Some rows pertain to 2 columns while
others pertain to 10.

Thanks

 

[Rick Rothstein \(MVP - VB\)]

Did you see my posting also in this thread? I think it will do what I think
you are asking.

Rick

Hi Don,

This works great as a pop up but is there anyway to make that information
go
into column DR for the corresponding row? That way it is populated for
each
row (there are 700 rows of data). Some rows pertain to 2 columns while
others pertain to 10.

Thanks

 

[Don Guillett]

Option Explicit
Sub longifs()
Dim r As Long
Dim i As Long
Dim ms As String
For r = 3 To 6
ms = ""
For i = 22 To 36
If UCase(Cells(r, i)) = "X" Then ms = ms & " " & Cells(1, i)
Next i
'MsgBox ms
cells(r,"dr")=ms
Next r
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi Don,

This works great as a pop up but is there anyway to make that information
go
into column DR for the corresponding row? That way it is populated for
each
row (there are 700 rows of data). Some rows pertain to 2 columns while
others pertain to 10.

Thanks

 

[DanielleVBANewbie]

This works perfectly, thank you so much.

Have a good weekend.

 

[Don Guillett]

glad to help