| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Alternate Code or Formula
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 :<) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Alternate Code or Formula
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" 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 :<) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Alternate Code or Formula
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 :<) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 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 :<) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Alternate Code or Formula
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 :<) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Alternate Code or Formula
Did you see my posting also in this thread? I think it will do what I think
you are asking. Rick "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 :<) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 :<) |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Alternate Code or Formula
This works perfectly, thank you so much.
Have a good weekend. -- 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 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 :<) |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Alternate Code or Formula
glad to help -- Don Guillett Microsoft MVP Excel SalesAid Software "DanielleVBANewbie" wrote in message ... This works perfectly, thank you so much. Have a good weekend. -- 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 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for sum of alternate cells | Excel Worksheet Functions | |||
| formula alternate column call value | Excel Worksheet Functions | |||
| formula alternate column call value | Excel Worksheet Functions | |||
| Formula at alternate Cell Values | Excel Worksheet Functions | |||
| Button Code to Alternate Sort Order ? | Excel Programming | |||
Linear Mode
