display calculation next row

T

tracktraining

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
 
P

Patrick Molloy

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 said:
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
Click to expand...
 
T

tracktraining

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 said:
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 said:
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
Click to expand...
Click to expand...
 
P

Patrick Molloy

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 said:
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 said:
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





message
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
Click to expand...
Click to expand...
Click to expand...
 
T

tracktraining

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 said:
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 said:
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 said:
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





message
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
Click to expand...
Click to expand...
Click to expand...
 
P

Patrick Molloy

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

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




tracktraining said:
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 said:
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



message
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


:

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





message
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

displaying multiple inputs 1
Macro autofills formula when a cell value is changed 4
Column range to search from a source workbook and then copy/paste 0
Macro to delete row if cells greater than certain value 1
type mismatch error in macro in excel 0
Code stops after column "L" 1
Worksheet_Change 1
Autofill is overwriting the column title..... 4

Top