PDA

View Full Version : [SOLVED] VBA Code to enter different formula if value is different



MattehWoo
07-27-2016, 07:41 AM
Hi guys,

Got a slightly difficult/confusing one (well it is for me anyway).

Basically i have a column with dates (which is fine)

I then have a column which has numbers in. Next to this column i have a formula that calculates a usage between the 2.



Date
Read
Usage


01-Jan-01
12345
30.44


01-Jan-02
23456
30.44


01-Jan-03
34567
30.44


01-Jan-04
45678
30.36


01-Jan-05
56789
30.41


01-Jan-06
67890
30.17


01-Jan-07
78901
27.70


01-Jan-08
89012
3.04


01-Jan-09
90123
-243.53


01-Jan-10
1234
0.03



16733

The formula i use is =(B11-B10)/(A11-A10) to bring up the usage...

When the Read goes to 99999 it reverts back to 0, so i have to manually change the code to =(99999-B10+B11)/(A11-A10) as it will show a negative usage....

Sometimes, the reads will only go to 9999 (which is more common) and again i have to manually change the formula.

....

Is there a way of getting it to detect when it changes like this and put the change of code automatically using VBA?

SamT
07-27-2016, 10:32 AM
If(B1>9999,IF(B1>99999, 99999-B1+B2,9999-B1+B2),B1-B2)
Or something like that, I'm not sure of the +'s and -'s

MattehWoo
07-28-2016, 12:51 AM
I managed to get it working as long as it only goes up to 4 digits (9999)

=IF(B11<B10,(9999-B10+B11)/(A11-A10),(B11-B10)/(A11-A10))

Could i use VBA code to detect how many digits there are and use

=IF(B11<B10,(9999-B10+B11)/(A11-A10),(B11-B10)/(A11-A10)) if there are only 4 digits

or

=IF(B11<B10,(99999-B10+B11)/(A11-A10),(B11-B10)/(A11-A10)) if there are 5?

SamT
07-28-2016, 04:40 AM
A VBA User Defined Function can do all that and replace the formula in the cells.

I think a good name for the UDF is "Usage" but a better name would include the usage of what ie "WhatUsage." That is up to you. A UDF must have at least one reference cell. this UDF can have 4,

What is the formula that you currently use in the topmost, (C2,) cell in the Usage column? The UDF will detect that there is a word(s) is in the cell above and use that formula.

The UDF will detect when Rollover occurs (Bn>Bn+1) and compensate.

I don't know when I can get to this, but this is simple enough that anybody here at VBAExpress can do it. Hint, hint, anybody.

mikerickson
07-28-2016, 06:26 AM
Try

=MOD(1000+B11-B10,1000)/(A11-A10)

Also, I think that your correction of adding 9999 is probably inaccurate. It fails to account for the 0000 reading between 9999 and 1.

You also might try the more generic

MOD(10^(1+INT(LOG(B10)))+B11-B10, 10^(1+INT(LOG(B10)))) / (A11-A10)

Paul_Hossler
07-28-2016, 07:15 AM
Here's a simple UDF like SamT suggested, with a 100,000 rollover




Option Explicit

Function WhatUsage(Date1 As Date, Date2 As Date, Read1 As Long, Read2 As Long) As Variant

WhatUsage = CVErr(xlErrNA)

If CLng(Date2) = 0 Then Exit Function

If Read2 < Read1 Then
WhatUsage = (100000 + Read2 - Read1) / (Date2 - Date1)
Else
WhatUsage = (Read2 - Read1) / (Date2 - Date1)
End If
End Function

MattehWoo
07-28-2016, 07:37 AM
Ah brilliant.

I think this could work!

SamT
07-28-2016, 11:44 AM
I've got as minute and since Paul has already doe the hared part, I will just add an easy upgrade

Option Explicit

Function WhatUsage(Date1 As Date, Date2 As Date, Read1 As Long, Read2 As Long) As Variant
Dim RolloverAdjustment As Long

WhatUsage = CVErr(xlErrNA)

If CLng(Date2) = 0 Then Exit Function

If Read2 < Read1 Then

Select Case Len(CStr(Read1))
Case Is < 4
RolloverAdjustment = 0
Case Is = 4
RolloverAdjustment = 1000
Case Is = 5
RolloverAdjustment = 10000
Case Is = 6
RolloverAdjustment = 100000
End Select
End If

WhatUsage = (RolloverAdjustment + Read2 - Read1) / (Date2 - Date1)
End Function

Paul_Hossler
07-28-2016, 04:03 PM
OP said ...


When the Read goes to 99999 it reverts back to 0, so i have to manually change the code to =(99999-B10+B11)/(A11-A10)

so I'm confused as to the need for a 1000 and 10,000 rollover logic

SamT
07-28-2016, 05:48 PM
When the Read goes to 99999 it reverts back to 0, so i have to manually change the code to =(99999-B10+B11)/(A11-A10) as it will show a negative usage....

Sometimes, the reads will only go to 9999 (which is more common) and again i have to manually change the formula.

Adding the superfluous rollovers at 1000 and 10,000 makes it clear to even a rank beginner how to modify the select case to fit any stage rollover.

I added it because nobody knows why the rollover can occur at even two stages. What if "the process gets new equipment" that rolls over at a different point? What if the boss tells the OP "Fix it. NOW!"

4 lines of never to be used code and four? Bytes of memory can be worth paragraphs of comments.

Paul_Hossler
07-28-2016, 07:42 PM
1. Ahhh

2. Slight change to make it 'fire and forget'



Option Explicit

Function SamWhatUsage(Date1 As Date, Date2 As Date, Read1 As Long, Read2 As Long) As Variant
Dim RolloverAdjustment As Long

SamWhatUsage = CVErr(xlErrNA)

If CLng(Date2) = 0 Then Exit Function

If Read2 < Read1 Then RolloverAdjustment = 10 ^ Len(CStr(Read1))

SamWhatUsage = (RolloverAdjustment + Read2 - Read1) / (Date2 - Date1)
End Function


16747

MattehWoo
08-01-2016, 02:37 AM
You guys are amazing.

Thank you so so much!

SamT
08-01-2016, 09:04 AM
Paul is good. Click the six pointed star under his message.