Not returning a number

[Ric]

Thanks in advance. Attempting to write a formula (IF THEN, IFERROR
not really sure) go to an adjacent work sheet and look thru a column
starting at bottom at lines C74, C63,C52,C41 and C30 and when it gets
to the first line of data (example $180) it stops and shows that value-
in this case line C30. Next time may be C41 the time after that C63
but cannot get to work. HELP

 

[Patrick Molloy]

Function GetValue() as string
WITH WORKSHEETS()"Sheet1")
SELECT CASE TRUE
CASE .Range("C74")<>""
GetValue= .Range("C74")
CASE .Range("C63")<>""
GetValue= .Range("C63")
CASE .Range("C52")<>""
GetValue= .Range("C52")
CASE .Range("C41")<>""
GetValue= .Range("C41")
CASE .Range("C30")<>""
GetValue= .Range("C30")
CASE ELSE
END SELECT
END FUNCTION

 

[Ric]

Function GetValue() as string
WITH WORKSHEETS()"Sheet1")
SELECT CASE TRUE
CASE .Range("C74")<>""
    GetValue= .Range("C74")
CASE .Range("C63")<>""
    GetValue= .Range("C63")
CASE .Range("C52")<>""
    GetValue= .Range("C52")
CASE .Range("C41")<>""
    GetValue= .Range("C41")
CASE .Range("C30")<>""
    GetValue= .Range("C30")
CASE ELSE
END SELECT
END FUNCTION





- Show quoted text -

I am completely lost-write a function how GET VALUE ('Piazzo Ware C74"

 

[Rick Rothstein]

I am completely lost-write a function how

GET VALUE ('Piazzo Ware C74"

We'll get you straightened out, but first... this appears to be a different
question than you asked originally. Let's see if we can find out your exact
question so we can write the code to do what you want. First off, let's make
sure the range of cells you gave us wasn't just an example. The only cells
you want to search for data in are C30,C41,C52,C63 and C74... correct? If
not, you need to tell us what is actually to be search (that is, what cells
can contain data that you are looking for). Next, what is it you are looking
for. Your first post appeared to say you want the last one (highest row
numbered cell) of C30,C41,C52,C63,C74 that contains any data... is that
correct (there was a small question in my mind whether you wanted the first
rather than the last cell in the column with data)? Your latest post appears
to say you are looking for the text "Piazzo Ware C74", and not just any
text... which is it? Perhaps if you gave us a couple of example data layouts
and showed what you are looking for (and where you want it at), then maybe
we can better help you out.

 

[Ric]

We'll get you straightened out, but first... this appears to be a different
question than you asked originally. Let's see if we can find out your exact
question so we can write the code to do what you want. First off, let's make
sure the range of cells you gave us wasn't just an example. The only cells
you want to search for data in are C30,C41,C52,C63 and C74... correct? If
not, you need to tell us what is actually to be search (that is, what cells
can contain data that you are looking for). Next, what is it you are looking
for. Your first post appeared to say you want the last one (highest row
numbered cell) of C30,C41,C52,C63,C74 that contains any data... is that
correct (there was a small question in my mind whether you wanted the first
rather than the last cell in the column with data)? Your latest post appears
to say you are looking for the text "Piazzo Ware C74", and not just any
text... which is it? Perhaps if you gave us a couple of example data layouts
and showed what you are looking for (and where you want it at), then maybe
we can better help you out.

Thanks. So, yes those are the cells, yes I want to start at line 74
and work up ending at line 30. Here is what I have-Have worksheet
(Piazzo Ware Deal Sheet C6 where the final value will be displayed)
that will go to adjacent worksheet (Piazzo Ware-where all these cells
are C74-C30) and I want it to start at bottom of sheet at Line C74 and
return the value ($180) if the fomula in there calculates one. If it
doesn't then go to C63 and if there's no value then C52 and so on. So
whenever and where ever the first value is calculated, I want it to
appear-it could be on C74 or it could be on C30 (all these cells
C74,C63,C52,C41 and C30 have a formula in them but only calaculate
based on a value in another cell in a different part of the same
worksheet).

After it is working, I want to drag it across so the same happens in
D, then E then F in this worksheet (Piazzo Ware Deal sheet) . I am
happy to attach the 2 sheet if possible so you can see them
together.

 

[Steve Dalton]

Hi Ric

Your problem might have a very easy solution if you can be more specific
about your searh criterion "first line of data". Is that always numerical
data you are looking for as your example of $180 suggests? Is it non-zero
or positive data you are looking for? What formulae do those cells contain?

If, for example, you were just looking for non-zero numerical data then a
worksheet formula such as

=IF(N(C74),C74,IF(N(C63),C63,IF(N(C52),C53,IF(N(C41),C41,IF(N(C30),C30,0))))

would do is and would satisfy your need to copy to columns D, E and so on.
It would also avoid you having to write a VBA function.

If your criterion is more complicated you still might be able to adapt this
approach to make it work.

Regards

Steve Dalton

 

[Rick Rothstein]

Try this formula...

=IF('Piazzo Ware'!C74<>"",'Piazzo Ware'!C74,IF('Piazzo Ware'!C63<>"",
'Piazzo Ware'!C63,IF('Piazzo Ware'!C52<>"",'Piazzo Ware'!C52,IF(
'Piazzo Ware'!C41<>"",'Piazzo Ware'!C41,IF('Piazzo Ware'!C30<>"",
'Piazzo Ware'!C30,"None")))))

--
Rick (MVP - Excel)

We'll get you straightened out, but first... this appears to be a
different
question than you asked originally. Let's see if we can find out your
exact
question so we can write the code to do what you want. First off, let's
make
sure the range of cells you gave us wasn't just an example. The only cells
you want to search for data in are C30,C41,C52,C63 and C74... correct? If
not, you need to tell us what is actually to be search (that is, what
cells
can contain data that you are looking for). Next, what is it you are
looking
for. Your first post appeared to say you want the last one (highest row
numbered cell) of C30,C41,C52,C63,C74 that contains any data... is that
correct (there was a small question in my mind whether you wanted the
first
rather than the last cell in the column with data)? Your latest post
appears
to say you are looking for the text "Piazzo Ware C74", and not just any
text... which is it? Perhaps if you gave us a couple of example data
layouts
and showed what you are looking for (and where you want it at), then maybe
we can better help you out.

Thanks. So, yes those are the cells, yes I want to start at line 74
and work up ending at line 30. Here is what I have-Have worksheet
(Piazzo Ware Deal Sheet C6 where the final value will be displayed)
that will go to adjacent worksheet (Piazzo Ware-where all these cells
are C74-C30) and I want it to start at bottom of sheet at Line C74 and
return the value ($180) if the fomula in there calculates one. If it
doesn't then go to C63 and if there's no value then C52 and so on. So
whenever and where ever the first value is calculated, I want it to
appear-it could be on C74 or it could be on C30 (all these cells
C74,C63,C52,C41 and C30 have a formula in them but only calaculate
based on a value in another cell in a different part of the same
worksheet).

After it is working, I want to drag it across so the same happens in
D, then E then F in this worksheet (Piazzo Ware Deal sheet) . I am
happy to attach the 2 sheet if possible so you can see them
together.

 

[Ric]

Try this formula...

=IF('Piazzo Ware'!C74<>"",'Piazzo Ware'!C74,IF('Piazzo Ware'!C63<>"",
'Piazzo Ware'!C63,IF('Piazzo Ware'!C52<>"",'Piazzo Ware'!C52,IF(
'Piazzo Ware'!C41<>"",'Piazzo Ware'!C41,IF('Piazzo Ware'!C30<>"",
'Piazzo Ware'!C30,"None")))))

--
Rick (MVP - Excel)




Thanks.  So, yes those are the cells, yes I want to start at line 74
and work up ending at line 30.  Here is what I have-Have worksheet
(Piazzo Ware Deal Sheet C6 where the final value will be displayed)
that will go to adjacent worksheet (Piazzo Ware-where all these cells
are C74-C30) and I want it to start at bottom of sheet at Line C74 and
return the value ($180) if the fomula in there calculates one.  If it
doesn't then go to C63 and if there's no value then C52 and so on.  So
whenever and where ever the first value is calculated, I want it to
appear-it could be on C74 or it could be on C30 (all these cells
C74,C63,C52,C41 and C30 have a formula in them but only calaculate
based on a value in another cell in a different part of the same
worksheet).

After it is working, I want to drag it across so the same happens in
D, then E then F in this worksheet (Piazzo Ware Deal sheet) .  I am
happy to attach the 2 sheet  if possible so you can see them
together.- Hide quoted text -

- Show quoted text -

Still not working. There isn't anything showing in the cell (except
your formula Rick). It should return the value of $160 which is in
line C30. All other cells referenced have these formulas in them
(example from line C74 =IF(AND(ISNUMBER(C69),C69>0),C16-C25-C69,"
") line C63 is =IF(AND(ISNUMBER(C58),C58>0),C16-C25-C58," ")
i am always lookin g for the first numberical value that is calculated
and in most cases line C30 and C41 will have values. Sometimes it
will stop at C63 since that is first value

 

[Rick Rothstein]

Still not working. There isn't anything showing in the cell

(except your formula Rick). It should return the value of
$160 which is in line C30. All other cells referenced have
these formulas in them (example from line C74
=IF(AND(ISNUMBER(C69),C69>0),C16-C25-C69,"")
line C63 is =IF(AND(ISNUMBER(C58),C58>0),C16-C25-C58," ")

The problem is you have YOUR formulas returning a blank character when your
logical expression is FALSE, not the empty string (""). Two choices...
change your formula to return the **more normal** empty string for that
condition, or change each "" in my formula to " " (quote, blank, quote) to
match what your cells are displaying.

 

[Ric]

The problem is you have YOUR formulas returning a blank character when your
logical expression is FALSE, not the empty string (""). Two choices...
change your formula to return the **more normal** empty string for that
condition, or change each "" in my formula to " " (quote, blank, quote) to
match what your cells are displaying.

I knew it has something to do with the way I wrote the formuals.
Thanks, it is working great!! How on earth can I learn this stuff??
Would you be interested in giving me your email so I can directly send
you my files next time? I am constantly amazed how you folks can
figure this stuff out so quickly.

Thanks again

 

[Rick Rothstein]

I knew it has something to do with the way I wrote the

formuals. Thanks, it is working great!! How on earth
can I learn this stuff?? Would you be interested in
giving me your email so I can directly send you my
files next time? I am constantly amazed how you
folks can figure this stuff out so quickly.

You would do better posting any future questions to the newsgroups as you
have been doing. Not all of the volunteers here (especially me) are
knowledgeable about all the areas of Excel, so restricting yourself to one
volunteer risks your not getting timely answers to your questions. As for
learning "this stuff"... reading Excel books, reading Excel oriented
websites, reading others' questions and the answers they receive on these
newsgroups all help; and, of course, trial and error attempts on your own as
well.