PDA

View Full Version : I have a formula that needs to be turned into a VBA, please!



estatefinds
03-17-2016, 06:39 AM
looking to convert the formula into a Macro to do the same thing formula is doing, please!

Im attaching the file for example.
so the data in the columns AL12:AP84 change.

the numbers in the column AK12 to AK 84 stay the same.

now in the Columns AA AB AC AD AE have a formula =MIN(IF(U11=$AL$11:$AP$46,$AK$11:$AK$46)) now this formula works with the values in the columns U V W X Y and this correlated to the graph to the right the in columns AL12:AP84.
so I need the formula to be changed to a VBA.
so the values that are in the columns U V W X Y for example are 2 15 16 25 26 these numbers are found in the chart of numbers in AL12:AP84m so the number 2 is found in the chart AL12:AP84 in column AM and found on the same row labeled 1 in column AK, number 15 in column V is found on same row labeled 6 in column AK. and number 16 found on row labeled 8 in the column AK. 25 found and is in column AK on row labeled 7. and the number 26 on the column AK labeled row 8.

there is a reason I need this to be a VBA as the way this data will be working I need to be un limited by where the data in the columns U V W X Y and columns AA AB AC AD AE and how I record them. to explain the data I enter for example in the columns U V W X Y the macro will interpret where number the number is found in the the chart of numbers and what row it is found on, and what I mean the row labeled in the column AK.
(Also the word "DONE" will be placed after the data entered into U V W X Y into the the column Z.)

So once that information is displayed in the columns AA AB AC AD AE the column to the right column AF will display the word "DONE". then When I enter another value in to the U V W X Y below the last data entered; based on the changed Data in the AL12:AP84 will have a different result in the columns AA AB AC AD AE and will results the word "DONE" so when I run the new data this data before the word "DONE" will not be changed.
Thank you!

estatefinds
03-17-2016, 05:38 PM
so simply it looks at the five numbers and looks into the pool of numbers within the thick black box. so the first numbers in the columns U V W X Y which contain the numbers 2 15 16 25 26 all I need is a VBA to look at these given numbers and look at what row is it on when I mean row I mean the row labeled in column AK and then return these numbers from AK in which each number is found on.

for example the number 2 is found on row label 1,
the number 15 found on the row labeled 6,
the number 16 on the row labeled 8.
the number 25 on the row labeled 7,
and finally the number 26 on the row labeled 8.

If you can Produce a VBA for this ill take care of the rest. Thank you!!!

Paul_Hossler
03-17-2016, 07:03 PM
I think this is what you were asking about

15689



Option Explicit
Function MyMin(N As Long, IndexesIn As Range, DataIn As Range) As Variant
Dim rFound As Range

On Error GoTo NiceExit
Set rFound = DataIn.Find(What:=N, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
With IndexesIn
MyMin = Intersect(IndexesIn.EntireColumn, rFound.EntireRow).Value
End With

Exit Function
NiceExit:
MyMin = CVErr(xlErrNA)
End Function

estatefinds
03-18-2016, 07:21 AM
hello, I tried to add this macro to run it to test and its not working. is because macro has no name on it? Meaning it's explicit?

Paul_Hossler
03-18-2016, 09:16 AM
The macro is a function named 'MyMin'

The green and the formula bar show how I used it on a worksheet

You can incorporate the logic into another sub if needed

estatefinds
03-18-2016, 10:35 AM
Ok so I can't add macro to my worksheet to run? I can you use exiting worksheet to add it to worksheet with insructions ?

estatefinds
03-18-2016, 10:38 AM
I ask cause I need to hsve it run each time placing the results in the AA:AE meaning each time I run with new data it will place result below the last.

Paul_Hossler
03-18-2016, 11:09 AM
Not really following all of that, but the key part is below

Since I'm not sure about what you're looking to do ("meaning each time I run with new data it will place result below the last") it might need some work to integrate

I just tried to return your numbers in AA:AE




Set rFound = DataIn.Find(What:=N, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
With IndexesIn
MyMin = Intersect(IndexesIn.EntireColumn, rFound.EntireRow).Value
End With