Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > display calculation next row
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 68
Default display calculation next row
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,049
Default display calculation next row
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" wrote in 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
--
Learning

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 68
Default display calculation next row
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" wrote in 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
--
Learning

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,049
Default display calculation next row
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" wrote in 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


"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" wrote in
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
--
Learning

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 68
Default display calculation next row
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" wrote in 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


"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" wrote in
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
--
Learning

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,049
Default display calculation next row
yes, if in the same row...use array formulae

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




"tracktraining" wrote in message
...
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" wrote in
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


"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" wrote in
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
--
Learning

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
DIsplay a value of the cell calculation is #N/A BZeyger Excel Programming 3 October 18th 07 07:05 AM
Can I display a cell based on a calculation (eg M(14/2)? rnrxtreme Excel Worksheet Functions 3 June 29th 06 11:32 PM
Save cell display but not calculation [email protected] New Users to Excel 4 May 9th 06 04:04 PM
Using rounded numbers for display, but not for the calculation. MKode Excel Discussion (Misc queries) 1 March 3rd 06 12:23 AM
IF formula to display a product of a calculation Blue Max[_2_] Excel Programming 1 June 1st 04 06:46 AM


All times are GMT +1. The time now is 09:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement