Hi All,

I would like to learn how to call or address the next row. Currently i am
hardcoding the cells (i.e. range("C3"), range("E4")). Please see example
below. So for each fruit, i hardcode in a particular cells, which takes me a
very long time. Is there a better way to code this?

For example: start off with the following sheet

A B C D E
1 apple yes
2 banana no
3 banana yes
4 apple no
5 apple yes
6 pear no

User input (via userform): apple, banana.

A B C D E
1 apple yes Fruit Name Yes No
2 banana no Apple 2 1
3 banana yes Banana 1 1
4 apple no
5 apple yes
6 pear no

my code:

fruit = Me.FruitName.value

Range("C1").FormulaR1C1 = "Fruit Name"
Range("D1").FormulaR1C1 = "Yes"
Range("E1").FormulaR1C1 = "No"

mySplit = Split(fruit, ",")
For iCtr = LBound(mySplit) To UBound(mySplit)
myVal = Trim(mySplit(iCtr))
wordkey = myVal
wordkeyUCASE = UCase(wordkey)
Call fruitcount(wordkeyUCASE)
Next iCtr

sub fruitcount(produce as string)
if produce = "APPLE" then call Applecount
if produce = "BANANA" then call bananacount
end sub

sub applecount()
Range("C2").FormulaR1C1 = "Apple"
Range("D2").FormulaArray =
"=COUNT(IF(ISNUMBER(SEARCH(""*Apple*"",RC[-3]:R[50]C[-3])),IF(ISNUMBER(SEARCH(""*yes*"",RC[-2]:R[50]C[-2])),1)))"
Range("E2").FormulaArray =
"=COUNT(IF(ISNUMBER(SEARCH(""*Apple*"",RC[-4]:R[50]C[-4])),IF(ISNUMBER(SEARCH(""*no*"",RC[-3]:R[50]C[-3])),1)))"
end sub

sub bananacount()
Range("C3").FormulaR1C1 = "banana"
Range("D3").FormulaArray =
"=COUNT(IF(ISNUMBER(SEARCH(""*banana*"",R[-1]C[-3]:R[50]C[-3])),IF(ISNUMBER(SEARCH(""*yes*"",R[-1]C[-2]:R[50]C[-2])),1)))"
Range("E3").FormulaArray =
"=COUNT(IF(ISNUMBER(SEARCH(""*banana*"",R[-1]C[-4]:R[50]C[-4])),IF(ISNUMBER(SEARCH(""*no*"",R[-1]C[-3]:R[50]C[-3])),1)))"
end sub

****-------*********
I would like to have something like this (i just dont' know how to code it,
or if possible):

fruit = Me.FruitName.value

Range("C1").FormulaR1C1 = "Fruit Name"
Range("D1").FormulaR1C1 = "Yes"
Range("E1").FormulaR1C1 = "No"

mySplit = Split(fruit, ",")
For iCtr = LBound(mySplit) To UBound(mySplit)
myVal = Trim(mySplit(iCtr))
wordkey = myVal
wordkeyUCASE = UCase(wordkey)
Call fruitcount(wordkeyUCASE)
Next iCtr

sub fruitcount(produce as string)
keyword = produce

Range("---").FormulaR1C1 = "*"keyword"*"
Range("---").FormulaArray =
"=COUNT(IF(ISNUMBER(SEARCH(""*keyword*"",RC[-3]:R[50]C[-3])),IF(ISNUMBER(SEARCH(""*yes*"",RC[-2]:R[50]C[-2])),1)))"
Range("---").FormulaArray =
"=COUNT(IF(ISNUMBER(SEARCH(""*keyword*"",RC[-4]:R[50]C[-4])),IF(ISNUMBER(SEARCH(""*no*"",RC[-3]:R[50]C[-3])),1)))"

note: ---- refers to the next row.
end sub


Thank you for your help in advance. Feel free to ask more questions to
understand my problem.

Tracktraining
--
Learning