Storing variables in VBA for subsequent MAX function within the routine.

I'm looping through a range of cells and I want to store their values in my macro. In other words, B341 = 5/1/2008, B342 = 4/16/2007, B343 = 8/1/2011, B344 = 3/12/2009. I need something that after getting the four values returns the max value (8/1/2011) AS THE VALUE FOR X.

I could swear this was working earlier, but probably only because the MAX date was the last one processed by the macro:

Dim vCel As Range, cel As Range, EIS As Date, RFS As Date, x As Long, z As Long
For Each vCel In [T4:T37]
vCel.Select
    For Each cel In Sheets("Tails Safety").[B341:B397]
    If cel = vCel And cel.Offset(0, -1) = Cells(1, vCel.Column) Then
        EIS = cel.Offset(0, 1)
            If cel.Offset(0, 2) = "" Then
            RFS = 0
        Else
            RFS = cel.Offset(0, 2)
        End If
    End If
    Next cel
    x = WorksheetFunction.Max(EIS)
    z = WorksheetFunction.Max(RFS)
Next vCel

Open in new window


Thanks,
John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FaustulusCommented:
I'm afraid I can't follow your code, in particular RFS. There is a problem in identifying your worksheets, and I can't discern if Sheets("Tails Safety") is identical with your ActiveSheet or not. However, perhaps these comments will help:-

1. Your variables EIS and RFS hold single values. Therefore their MAX() is equal to their value as you have correctly surmised. I suggest that you declare X and Z as Static (Static X As Long) and then use code like this:
[
= IIf(NewNum > X, NewNum, X)

Open in new window

Here NewNum would be a value like cel.Offset(0, 1). You don't need EIS or RFS in this structure, but you must make sure to reset X = 0 before each search for a new maximum.
2. As an alternative, this code will return the maximum value in the specified range:
WorksheetFunction.Max([B341:B397])

Open in new window

If neither of these suggestions are of any help please try to post your worksheet or workbook along with a better description of what you want to do.

PS. The first example should be X = ... or Z = ...
0
SteveCommented:
have you tried adding x or z into the max (as max of a single value is the single value):

Dim vCel As Range, cel As Range, EIS As Date, RFS As Date, x As Long, z As Long
For Each vCel In [T4:T37]
vCel.Select
    For Each cel In Sheets("Tails Safety").[B341:B397]
    If cel = vCel And cel.Offset(0, -1) = Cells(1, vCel.Column) Then
        EIS = cel.Offset(0, 1)
            If cel.Offset(0, 2) = "" Then
            RFS = 0
        Else
            RFS = cel.Offset(0, 2)
        End If
    End If
    Next cel
    x = WorksheetFunction.Max(EIS,x)
    z = WorksheetFunction.Max(RFS,z)
Next vCel

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, guys.  I've made some progress on this over the weekend on the EIS part, and now all I need is the RFS part. The solution may be present in one or both of your posts, but I can't quite get IT. What I do know is that the value I want for RFS is whatever's in the cel to the right of the cell in which the value for EIS was found.

In the macro below, all I need is code in line 17 that will accomplish that and I'll be a very happy camper!
Sub Populate_EIS_RFS_Dates()
Dim topEIS As Range, topEIS2 As Range, btmEIS As Range, _
    topRFS As Range, topRFS2 As Range, btmRFS As Range

Set topEIS = ActiveCell.Offset(3, 3)
Set topEIS2 = topEIS.Offset(1, 0)
Set btmEIS = topEIS.Offset(0, -3).Offset(1000, 0).End(xlUp).Offset(0, 3)

Set topRFS = topEIS.Offset(0, 1)
Set topRFS2 = topEIS2.Offset(0, 1)
Set btmRFS = btmEIS.Offset(0, 1)

topEIS.FormulaArray = "=MAX(IF(LKP_SN=T4, LKP_EIS))"
topEIS.Copy Range(topEIS2, btmEIS)
Range(topEIS2, btmEIS).Calculate

'topRFS= THE VALUE IN THE CELL IMMEDIATELY TO THE RIGHT OF THE CELL IN WHICH THE VALUE FOR topEIS WAS FOUND
topRFS.Copy Range(topRFS2, btmRFS)
End Sub

Open in new window

Thanks,
John
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

FaustulusCommented:
Hi John,
Is this where you find topEIS?
Set topEIS = ActiveCell.Offset(3, 3)
The cell to the right of it would be ActiveCell.Offset(3, 4)

Of course, you already have Set topRFS = topEIS.Offset(0, 1), which is equivalent except that topEIS and topRFS are both ranges, not values.
A range is like a tree where a value would be like an apple. If you take the whole tree you also get the apple, but if you only want the apple why take the entire tree?
You have declared topRFS as a Range. Therefore it is correct to assign a range object to this variable, like, Set topRFS = ActiveCell.Offset(3, 4).
But if you dimension topRFS As Long (meaning, as a number) then you would extract the value from the object with this code topRFS = ActiveCell.Offset(3, 4).Value.
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hi Everybody, sorry I was gone for so long. In he meantime I've come up with a draggable formula in Column E that determines the EIS value I'm looking for:

{=MAX(IF(LKP_SN=B7, LKP_EIS))}

Open in new window

LKP_SN and LKP_EIS are named ranges on the"Tails Safety" sheet.

In this case the Serial Number in B7 is '34597'.  In the LKP_SN column on "Tails Safety," there are 3 occurrences of '34597' and the formula correctly returns the most recent date associated with that number: "3/21/2010"

Now I'm thinking that the formula must know the address of the cell from which it got that value: D21 on "Tails Safety." If so, then all I need is a formula (a macro would also do) that returns the date of the cell immediately to the right, i.e., E21.

I've attached a stripped down workbook which I hope makes everything clear :- )

Thanks, John
0
FaustulusCommented:
Hi John,
Please post a copy of your workbook (it wasn't attached when you posted the array formula), point to the cells or columns you wish to evaluate, define the cell or column where you wish to see the result, and explain the manner in which the result is to be extracted from the data. For the purpose of this explanation please do not use code or formulae - just plain English will serve best.
Faustulus
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Barman and Faustulus, forgive me for being gone so long. I get pulled in a million (well ... maybe 3) directions all the time, but starting with your input I did manage to figure this out:

this code:
topEIS.FormulaArray = "=MAX(IF(LKP_SN=" & SN & ",IF(LKP_ICAO=" & Cstmr.Address & ",LKP_EIS)))"

produces this result:
=(MAX(IF(LKP_SN=H20,IF(LKP_EIS=K20,LKP_RFS))))

this code:
topRFS.FormulaArray = "=MAX(IF(LKP_SN=" & SN & ",IF(LKP_ICAO=" & Cstmr.Address & ",IF(LKP_EIS=" & EIS & ", LKP_RFS))))"

produces this result:
=IF(MAX(IF(LKP_SN=H4,IF(LKP_EIS=K4, LKP_RFS)))=0,"",MAX(IF(LKP_SN=H4,IF(LKP_EIS=K4, LKP_RFS))))

I really appreciate your help; and again I'm sorry for having abandoned the question for so long.

John
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.