ExcelBanter - displaying multiple inputs

On Apr 14, 5:26*pm, tracktraining
wrote:
Hi All,

How do i go about displaying a user input string into several cells? *

Example:
User input: cat, dog, horse, chicken

* * * * A * * * * * *B * * * * * *C * * * *D * * * *E * * * * *F
1 * * * * * * * * * * * * * * * * *cat * * dog * *horse * chicken

I know i can use the following code to split the words, but i just don't
know how to display it into each cell, also, sometime the user may enter 1
word, sometime 3 words:
* * * * * * mySplit = Split(productStrUCASE, ",")
* * * * * * * * For iCtr = LBound(mySplit) To UBound(mySplit)
* * * * * * * * * * myVal = Trim(mySplit(iCtr)) * * 'remove any
leading/trailing spaces
* * * * * * * * * * wordkey = myVal
* * * * * * * * * * wordkeyUCASE = UCase(wordkey)
* * * * * * * * * * write to cell
* * * * * * * * Next iCtr

Also, how would i count a row that has the word "cat" in column L and the
word "yes" in column K?

I have the following countif code, but the keywords are hardcode in, is
there a way to not hardcode the keywords in but have it as a variable
instead?

Range("AM10").FormulaArray =
"=COUNT(IF(ISNUMBER(SEARCH(""*Cat*"",R[-8]C[-34]:R[600]C[-34])),IF(ISNUMBER*(SEARCH(""*yes*"",R[-8]C[-27]:R[600]C[-27])),1)))"

thanks a lot for your help!
tracktraining

--
Learning

tracktraining,

I've included some syntax (and my comments) below. Simply asking "How
do I go about displaying a user input string into several cells?" is
rather vague. Do you want the data to be input via rows then columns,
columns then rows, overwriting existing data, etc. As a result, I
made some simple assumptions, which you'll see below, for "displaying
a user input string into several cells." Also, I'm sure there are
multiple ways to address your array formula, but I've included one way
below.

Best,

Matthew Herbert

Sub SampleSub()

Dim varMySplit As Variant
Dim strMyStr As String
Dim intCnt As Integer
Dim varMyVal As Variant
Dim intOutCnt As Integer
Dim Wks As Worksheet
Dim strOutCol As String
Dim strSearch As String
Dim strYes As String

'set worksheet object
Set Wks = ActiveSheet

'initialize strMyStr
strMyStr = "Cat,Dog,Horse,Chicken"

'split returns a zero-based one dimensional array
varMySplit = Split(strMyStr, ",")

'test if the Split function returns anything
If UBound(varMySplit) = -1 Then
MsgBox "There was nothing to split."
Exit Sub
End If

'initialize a column to output the items in varMySplit
strOutCol = "C"

'create a counter for outputting the cells on the
' next row (assumes a header row if the worksheet
' is "blank")
With Wks

'look at the contiguous cells starting in row 1,
' column strOutCol
intOutCnt = .Range(strOutCol & "1").CurrentRegion.Rows.Count

'add one to the counter, i.e. the output will
' be one row below the existing data
intOutCnt = intOutCnt + 1
End With

'loop through each item in varMySplit
For intCnt = LBound(varMySplit) To UBound(varMySplit)
varMyVal = Trim(varMySplit(intCnt))
varMyVal = UCase(varMyVal)

'write to cell
With Wks
'output the items in varMySplit in successive
' columns on the appropriate row
.Cells(intOutCnt, strOutCol).Offset(0, intCnt) = varMyVal
End With
Next intCnt

'initialize search strings for the function
strSearch = "*cat*"
strYes = "*yes*"

'insert the search strings into the array function
Range("AM10").FormulaArray = "=COUNT(IF(ISNUMBER(SEARCH(""" & _
strSearch & """,E2:E610)),IF(ISNUMBER(SEARCH(""" & strYes & _
""",L2:L610)),1)))"

End Sub