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

set a variable to the first row that you want, say A1, then columns are
simply offsets to that. so C1 is A1 offset by 2 columns

so
DIM cell as Range
Set cell = Range("A2")
'now cell is A2
'so C2 is cell.Offset( ,2) = "Apple"

DO While cell.Value <> ""
if cell.Offset( ,2).Value = "Apple"
{then do something
end if
' now move down to the next row
set cell = cell.Offset(1) ' one row down
LOOP





"tracktraining" <(E-Mail Removed)> wrote in message
news254C128-5422-4F58-8A83-(E-Mail Removed)...
> 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

how about those count formular, is there a way to replace the word with
variables?

so instead of hardcoding in the word "Apple", i can have a variable called
keyword

keyword = apple

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

i know my syntax is wrong.

--
Learning


"Patrick Molloy" wrote:

> set a variable to the first row that you want, say A1, then columns are
> simply offsets to that. so C1 is A1 offset by 2 columns
>
> so
> DIM cell as Range
> Set cell = Range("A2")
> 'now cell is A2
> 'so C2 is cell.Offset( ,2) = "Apple"
>
> DO While cell.Value <> ""
> if cell.Offset( ,2).Value = "Apple"
> {then do something
> end if
> ' now move down to the next row
> set cell = cell.Offset(1) ' one row down
> LOOP
>
>
>
>
>
> "tracktraining" <(E-Mail Removed)> wrote in message
> news254C128-5422-4F58-8A83-(E-Mail Removed)...
> > 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
>

if you want to count the number of times an item appears in column A

Option Explicit
Sub demo()
MsgBox items("Apple")
MsgBox items("Banana")
End Sub
Function items(item As String) As Long
items = WorksheetFunction.CountIf(Range("A:A"), item)
End Function



"tracktraining" <(E-Mail Removed)> wrote in message
news:A2602CB6-3614-4CB5-B3BA-(E-Mail Removed)...
> how about those count formular, is there a way to replace the word with
> variables?
>
> so instead of hardcoding in the word "Apple", i can have a variable called
> keyword
>
> keyword = apple
>
> Range(" ").FormulaArray =
> "=COUNT(IF(ISNUMBER(SEARCH(""*keyword*"",RC[-4]:R[50]C[-4])),IF(ISNUMBER(SEARCH(""*no*"",RC[-3]:R[50]C[-3])),1)))"
>
> i know my syntax is wrong.
>
> --
> Learning
>
>
> "Patrick Molloy" wrote:
>
>> set a variable to the first row that you want, say A1, then columns are
>> simply offsets to that. so C1 is A1 offset by 2 columns
>>
>> so
>> DIM cell as Range
>> Set cell = Range("A2")
>> 'now cell is A2
>> 'so C2 is cell.Offset( ,2) = "Apple"
>>
>> DO While cell.Value <> ""
>> if cell.Offset( ,2).Value = "Apple"
>> {then do something
>> end if
>> ' now move down to the next row
>> set cell = cell.Offset(1) ' one row down
>> LOOP
>>
>>
>>
>>
>>
>> "tracktraining" <(E-Mail Removed)> wrote in
>> message
>> news254C128-5422-4F58-8A83-(E-Mail Removed)...
>> > 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
>>

thanks!

is there a way to count if item A is in column E AND item B is in column G?
(so, if item A is in column E AND item B is in column G then that is count as
1).

thanks for helping.
--
Learning


"Patrick Molloy" wrote:

> if you want to count the number of times an item appears in column A
>
> Option Explicit
> Sub demo()
> MsgBox items("Apple")
> MsgBox items("Banana")
> End Sub
> Function items(item As String) As Long
> items = WorksheetFunction.CountIf(Range("A:A"), item)
> End Function
>
>
>
> "tracktraining" <(E-Mail Removed)> wrote in message
> news:A2602CB6-3614-4CB5-B3BA-(E-Mail Removed)...
> > how about those count formular, is there a way to replace the word with
> > variables?
> >
> > so instead of hardcoding in the word "Apple", i can have a variable called
> > keyword
> >
> > keyword = apple
> >
> > Range(" ").FormulaArray =
> > "=COUNT(IF(ISNUMBER(SEARCH(""*keyword*"",RC[-4]:R[50]C[-4])),IF(ISNUMBER(SEARCH(""*no*"",RC[-3]:R[50]C[-3])),1)))"
> >
> > i know my syntax is wrong.
> >
> > --
> > Learning
> >
> >
> > "Patrick Molloy" wrote:
> >
> >> set a variable to the first row that you want, say A1, then columns are
> >> simply offsets to that. so C1 is A1 offset by 2 columns
> >>
> >> so
> >> DIM cell as Range
> >> Set cell = Range("A2")
> >> 'now cell is A2
> >> 'so C2 is cell.Offset( ,2) = "Apple"
> >>
> >> DO While cell.Value <> ""
> >> if cell.Offset( ,2).Value = "Apple"
> >> {then do something
> >> end if
> >> ' now move down to the next row
> >> set cell = cell.Offset(1) ' one row down
> >> LOOP
> >>
> >>
> >>
> >>
> >>
> >> "tracktraining" <(E-Mail Removed)> wrote in
> >> message
> >> news254C128-5422-4F58-8A83-(E-Mail Removed)...
> >> > 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
> >>

yes, if in the same row...use array formulae

Chip Pearson's site has it all...
http://www.cpearson.com/Excel/ArrayFormulas.aspx




"tracktraining" <(E-Mail Removed)> wrote in message
news:B74C5634-C0D2-443F-935E-(E-Mail Removed)...
> thanks!
>
> is there a way to count if item A is in column E AND item B is in column
> G?
> (so, if item A is in column E AND item B is in column G then that is count
> as
> 1).
>
> thanks for helping.
> --
> Learning
>
>
> "Patrick Molloy" wrote:
>
>> if you want to count the number of times an item appears in column A
>>
>> Option Explicit
>> Sub demo()
>> MsgBox items("Apple")
>> MsgBox items("Banana")
>> End Sub
>> Function items(item As String) As Long
>> items = WorksheetFunction.CountIf(Range("A:A"), item)
>> End Function
>>
>>
>>
>> "tracktraining" <(E-Mail Removed)> wrote in
>> message
>> news:A2602CB6-3614-4CB5-B3BA-(E-Mail Removed)...
>> > how about those count formular, is there a way to replace the word with
>> > variables?
>> >
>> > so instead of hardcoding in the word "Apple", i can have a variable
>> > called
>> > keyword
>> >
>> > keyword = apple
>> >
>> > Range(" ").FormulaArray =
>> > "=COUNT(IF(ISNUMBER(SEARCH(""*keyword*"",RC[-4]:R[50]C[-4])),IF(ISNUMBER(SEARCH(""*no*"",RC[-3]:R[50]C[-3])),1)))"
>> >
>> > i know my syntax is wrong.
>> >
>> > --
>> > Learning
>> >
>> >
>> > "Patrick Molloy" wrote:
>> >
>> >> set a variable to the first row that you want, say A1, then columns
>> >> are
>> >> simply offsets to that. so C1 is A1 offset by 2 columns
>> >>
>> >> so
>> >> DIM cell as Range
>> >> Set cell = Range("A2")
>> >> 'now cell is A2
>> >> 'so C2 is cell.Offset( ,2) = "Apple"
>> >>
>> >> DO While cell.Value <> ""
>> >> if cell.Offset( ,2).Value = "Apple"
>> >> {then do something
>> >> end if
>> >> ' now move down to the next row
>> >> set cell = cell.Offset(1) ' one row down
>> >> LOOP
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "tracktraining" <(E-Mail Removed)> wrote in
>> >> message
>> >> news254C128-5422-4F58-8A83-(E-Mail Removed)...
>> >> > 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
>> >>