storing a variable as a range to be used later

[losmi8]

Hi. I am trying to store a variable (I will highlight it in red below) to be used later in the same formula. The formula I have now works, but it puts the variable in cell G20, and I do not need this variable anywhere on the sheet. However, I do need it to perform some calculations later in the code. Here is the code:

Please Login or Register  to view this content.

As you can see, in order to get G21, I am using the value from G20. Ideally, I want to be able to calculate G21 without G20 being visible. So instead of my code saving this number in G20, is there a way for the code to remember or store the value (of G20) to be used in the following formula (bottom of top code):

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Yes, just declare another variable. e.g.

Please Login or Register  to view this content.

HTH

[losmi8]

That worked like a charm! Thank you Sir!!

Does it matter if I declare it as a Double, Range or Integer?

I went with Double like you said, but I'm just wondering why Double vs. Range or vs. Integer

[losmi8]

Sub y()

Dim c As Range, lrow As Long, whatever As Double

lrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

For Each c In Sheet1.Range("A2:A29" & lrow)
If c.Value = "Mike" And c.Offset(0, 1).Value = "Fax" Then
whatever = whatever + c.Offset(0, 3).Value
End If
Next c

Range("G21").Formula = "=" & whatever & " / SUMPRODUCT(--(A2:A29=""Mike""),--(B2:B29=""Fax""))"
Range("G22").Formula = "=" & whatever & " - Range("M19") * Range("M25")

Application.ScreenUpdating = True

End Sub

I have a problem with the red highlighted area above. I am trying to Subtract a value from cell M19 from "whatever" double THEN multiply the result by another value stored in cell M25

[losmi8]

Both ranges "M19" and "M25" are already stored in the sheet. They aren't calculated from anything. I manually enter them if that is of any help.

[losmi8]

Anyone? I can't proceed without getting this part...Because I'll need that value to calculate others. Ah, programing can get frustrating sometimes

[losmi8]

If the above explanation of what I need is not good enough...I need to perform some basic math functions including already stored cell values and a variable that is a Double.

double + value in cell + (value in cell - value in a different cell)


in the above example, "whatever" is my Double.

[rylo]

Hi

Please Login or Register  to view this content.

Is this what you are trying to do?


rylo

[losmi8]

Yep, perfect! I was using "Range("M19")" instead of just M19


Another question for you if you don't mind (or anyone else if you get to it first)

I want to do:

Please Login or Register  to view this content.

IF...the value of G25 is a positive number. However, if G25 is a negative number, I'd want to do the following:

Please Login or Register  to view this content.

Hope that makes sense?

[rylo]

Hi

Normal IF statement approach

Please Login or Register  to view this content.

rylo

[losmi8]

Oh cool. That is simple enough! So I should have two lines of code, one for IF it is greater than 0 and one if it is less than 0?

Please Login or Register  to view this content.

[rylo]

Hi

No, only the one line of code as the if statment is in the formula.

rylo

[losmi8]

Okay, thank you. I figured it out!

Thank you for being so helpful rylo! Appreciate it!

[rylo]

Hi

sure

Please Login or Register  to view this content.

rylo

[Richard Buttrey]

That worked like a charm! Thank you Sir!!

Does it matter if I declare it as a Double, Range or Integer?

I went with Double like you said, but I'm just wondering why Double vs. Range or vs. Integer

Hi,

I can see from the further correspondence overnight that you're well on the way, but to answer your question, it all depends on what's in the range you're recording as a variable. If it's only ever an integer value then an integer variable is all you need. If however it's number involving decimals then you'll need the double variable.

Always try to use the variable type which takes the least memory. Double takes up more space than Integer (or Single, Long etc.).

HTH

[DonkeyOte]

In 32 bit VBA Integers are converted "under the hood" to Long, as such some have argued that use of Integers is in fact slower than use of Longs given the additional operations that take place behind the scene, ie: Integer -> Long -> Integer

On that basis I never use Integer type, only Long or Byte if it will suffice... there is no value to using Integer in VBA other than as a sanity check - ie to deliberately generate an Overflow error.


losmi8, given this is an extension of your other thread I hope you will be denoting the other thread as Solved in the immediate future and adding a link from that to this for sake of continuity.

[Richard Buttrey]

Thanks Donkey.

I didn't know that. Interesting.

Rgds

[DonkeyOte]

I only know that little nugget c/o romperstomper... but it's a good one I agree.