Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
displaying multiple inputs
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple inputs on a spreadsheet | Excel Discussion (Misc queries) | |||
Can I have a formula with inputs from multiple pages? | Excel Worksheet Functions | |||
Functions with multiple inputs? | Excel Programming | |||
Multiple inputs one output | Excel Discussion (Misc queries) | |||
how do i have multiple inputs and 1 output | Excel Programming |