Thread: Need Alternate Code or Formula
View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
Don Guillett Don Guillett is offline
external usenet poster
  
Posts: 10,124
Default Need Alternate Code or Formula
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

"DanielleVBANewbie" wrote in
message ...
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
--
Danielle :<)


"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DanielleVBANewbie" wrote
in
message ...
Hi Don,

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


--
Danielle :<)


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DanielleVBANewbie"
wrote
in
message ...
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


--
Danielle :<)





Reply With QuoteReply With Quote