# Convert a formula to a Macro

1. ## Convert a formula to a Macro

Hi All

I am hoping that some one out there will be able to help me. I am looking to change a nested formula to a macro due to the amount of conditions that need to be met.

I have an export out of a system that is rather "ugly". This schedule has stuff in like holiday, Bank Holidays, Sickness and so on. I would like to take out of this just the Data if someone is off on holiday, sick, etc.

As mentioned the formula that I have is here: =IF(ISNUMBER(SEARCH("Hol 7:30",INDEX('Sorted Data'!\$C\$3:\$NC\$243,MATCH(D\$1,'Sorted Data'!\$A\$3:\$A\$243,0),MATCH(\$B6,'Sorted Data'!\$C\$1:\$NC\$1,0)))),"HOL",IF(ISNUMBER(SEARCH(".Ho 3:45",INDEX('Sorted Data'!\$C\$3:\$NC\$243,MATCH(D\$1,'Sorted Data'!\$A\$3:\$A\$243,0),MATCH(\$B6,'Sorted Data'!\$C\$1:\$NC\$1,0)))),"HALF",IF(ISNUMBER(SEARCH("M 7:30",INDEX('Sorted Data'!\$C\$3:\$NC\$243,MATCH(D\$1,'Sorted Data'!\$A\$3:\$A\$243,0),MATCH(\$B6,'Sorted Data'!\$C\$1:\$NC\$1,0)))),"MAT",IF(ISNUMBER(SEARCH("CoU 7:30",INDEX('Sorted Data'!\$C\$3:\$NC\$243,MATCH(D\$1,'Sorted Data'!\$A\$3:\$A\$243,0),MATCH(\$B6,'Sorted Data'!\$C\$1:\$NC\$1,0)))),"Comp Unpd",IF(ISNUMBER(SEARCH("SSC 7:30",INDEX('Sorted Data'!\$C\$3:\$NC\$243,MATCH(D\$1,'Sorted Data'!\$A\$3:\$A\$243,0),MATCH(\$B6,'Sorted Data'!\$C\$1:\$NC\$1,0)))),"SICK",IF(ISNUMBER(SEARCH("CoP 7:30",INDEX('Sorted Data'!\$C\$3:\$NC\$243,MATCH(D\$1,'Sorted Data'!\$A\$3:\$A\$243,0),MATCH(\$B6,'Sorted Data'!\$C\$1:\$NC\$1,0)))),"Comp Pd",IF(ISNUMBER(SEARCH("Dec 8:00",INDEX('Sorted Data'!\$C\$3:\$NC\$243,MATCH(D\$1,'Sorted Data'!\$A\$3:\$A\$243,0),MATCH(\$B6,'Sorted Data'!\$C\$1:\$NC\$1,0)))),"Decline",IF(ISNUMBER(SEARCH("Dec 4:00",INDEX('Sorted Data'!\$C\$3:\$NC\$243,MATCH(D\$1,'Sorted Data'!\$A\$3:\$A\$243,0),MATCH(\$B6,'Sorted Data'!\$C\$1:\$NC\$1,0)))),"Decline",""))))))))

I am using Excel 2016 however, the formula will need to be compatible with excel 2003 onwards. I have attached an example spreadsheet with the desired outcome and an example of the raw data that I am working with. This also has the formula in above.

I also need to need be able to add more codes to what ever solution is implemented as the codes that I have so far is not exhaustive.

I think that I have covered most things off but if you do have any questions please just ask I will be more than happy to answer.

D.

2. ## Re: Convert a formula to a Macro

Would it help to shorten your formula if you build an extra reference table (say, in a separate tab) to list down the various conditions and their desired output?
eg,
CODE OUTPUT
Hol 7:30 HOL
.Ho 3:45 HALF
M 7:30 MAT
CoU 7:30 Comp Unpd
SSC 7:30 SICK
CoP 7:30 Comp Pd
Dec 8:00 Decline
Dec 4:00 Decline

Then, in your original schedule, you may still index the employee ID and date to get the code, then vlookup to get the desired output. That way, you may simply extend on this extra reference table when new combinations arise in the future

3. ## Re: Convert a formula to a Macro

That would make sense however, I am not sure how that would look. Do you have any suggestions?

4. ## Re: Convert a formula to a Macro

Does the attached help to achieve what you need?

5. ## Re: Convert a formula to a Macro

It kind of does however, the cell would need to parameter would need to match the sorted data sheet exactly. In some instances the cell has spaces or "," after the exception code. This would need to be replicated for the lookup to work....

Is there a way of incorporating an issearch so that I can just find a certain string within the cell?

Sorry to be a pain

6. ## Re: Convert a formula to a Macro

Hi All

I have still been unable to find a solution to the above. Does any one have any other suggestions? Or can any one point me in the right direction?

Thanks again

Dominic

7. ## Re: Convert a formula to a Macro

Originally Posted by Dominic.Brice
It kind of does however, the cell would need to parameter would need to match the sorted data sheet exactly. In some instances the cell has spaces or "," after the exception code. This would need to be replicated for the lookup to work....

Is there a way of incorporating an issearch so that I can just find a certain string within the cell?

Sorry to be a pain
Yes, should the reference data be mixed with trailing spaces or "," or "."... the lookup will not work...

Im not sure if there is a more elegant way to resolve your problem (other than ensuring the user inputs the data correctly), but below is a function code which will eliminate the trailing spaces and "." and "," before passing it as a vlookup to get the output

In short, the function works this way = SearchA(cell with the employee ID, cell with the date). This function works just like the previous method without VBA, excepts it removes the trailing spaces and "." before looking up the parameter list

Again, simply extend the PARAMETER list should you have a new code

``Please Login or Register  to view this content.``
Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)