Finding the last increase/decrease of an series of data (row)
If there are embedded zeroes should we step over them? And if so and the
last two numbers are 0 and 9, should we use the last two non-zero numbers?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Justin Luyt" wrote in message
...
Bob,
I thought and looked at your code again... please note that there will be
days when the value for the day IS ZERO.
it could be: 5 3 0 4 5 0 3 0 5 000000000000....
every colum COULD have the header of the DATE if we could use TODAY to
look
up the correct colum... just a thought.... OR we can make sure the days
that
have not been entered yet for the month is BLANK!
justin
"Bob Phillips" wrote:
Sorry, adjusted to your range
Sub Increase()
Dim iLast As Long
On Error Resume Next
iLast = Application.Match(0, Range("A1:AE1"), 0)
On Error GoTo 0
If iLast = 0 Then iLast = 32
Range("AG2").Value = Format((Cells(2, iLast - 1).Value - _
Cells(2, iLast - 2).Value) / _
Cells(2, iLast - 1).Value, "0%")
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Justin Luyt" wrote in message
...
Bob,
I tried it again... but it still fails. I used it like this:
Sub Increase()
Dim iLast As Long
On Error Resume Next
iLast = Application.Match(0, Range("A1:AE1"), 0)
On Error GoTo 0
If iLast = 0 Then iLast = 32
Range("AG1").Value = Format((Cells(2, iLast - 1).Value - _ Cells(2,
iLast -
2).Value) / _ Cells(2, iLast - 1).Value, "0%")
End Sub
---
where is the wrap around issue?
justin
"Bob Phillips" wrote:
NG wrap-around problem
Sub Increase()
Dim iLast As Long
On Error Resume Next
iLast = Application.Match(0, Range("A2:AF2"), 0)
On Error GoTo 0
If iLast = 0 Then iLast = 32
Range("AG2").Value = Format((Cells(2, iLast - 1).Value - _
Cells(2,
iLast -
2).Value) / _
Cells(2,
iLast -
1).Value, "0%")
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Justin Luyt" wrote in
message
...
Bob,
Thanks, but I need some more guidence here.
I need it to place this increse (in %) in a cell... perhaps in
"AG".
When I use the code as you have given it, it does not like the mgs
box...
justin
"Bob Phillips" wrote:
Sub Increase()
Dim iLast As Long
On Error Resume Next
iLast = Application.Match(0, Range("A2:AF2"), 0)
On Error GoTo 0
If iLast = 0 Then iLast = 32
MsgBox Format((Cells(2, iLast - 1).Value - Cells(2, iLast -
2).Value) /
_
Cells(2, iLast - 1).Value, "0%")
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing
direct)
"Justin Luyt" wrote in
message
...
I have a row of data:
1 5 2 6 8 9 0 0 0 0 0 0 0 0 X (example .. 31 of them, one
for
each
day)
every day, one value is entered. for today the value is "9". I
need a
formula that will automaticly calculate the % increse or
decrese
of
the
last
added value (8-9 in %). The header of these colums can be
DATES
and we
might
use TODAY to locate and calculate the increse/decrese... or
maybe
we
can
read
all of the values into an array... i do not know... i need
help
please.
X can be the output of the last increse or decrese
i am not flewent with vba so be detailed in your guidence
please.
Justin
|