# Convert a formula to a Macro

• 08-10-2018, 12:19 AM
Dominic.Brice
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.
• 08-10-2018, 01:07 AM
Siaolang
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
• 08-10-2018, 01:13 AM
Dominic.Brice
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?
• 08-10-2018, 01:20 AM
Siaolang
Re: Convert a formula to a Macro
Does the attached help to achieve what you need?:confused:
• 08-10-2018, 03:03 AM
Dominic.Brice
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
• 08-15-2018, 06:41 AM
Dominic.Brice
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
• 08-15-2018, 10:19 AM
Siaolang
Re: Convert a formula to a Macro
Quote:

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

Code:

```Option Explicit Function SearchA(rngEmpID As Range, rngDate As Range) As String Dim dblRow As Double Dim dblColumn As Double Dim strCode As String Dim lastCol As Long Dim strCodeClean As String Dim strOutput As String Dim intToCut As Integer Dim i As String On Error GoTo ErrHandler dblRow = Application.Match(rngEmpID, Sheets("Sorted Data").Columns(1), 0) dblColumn = Application.Match(rngDate, Sheets("Sorted Data").Rows(1), 0) lastCol = Sheets("Sorted Data").Cells(1, Columns.Count).End(xlToLeft).Column     strCode = Application.WorksheetFunction.Index(Sheets("Sorted Data").Range(Columns(1).Address, Columns(lastCol).Address), _                                                 dblRow, dblColumn)     i = Right(strCode, 1) Do Until i <> "." And i <> "," And i <> " " And i <> Chr(133)     intToCut = intToCut + 1     i = Mid(strCode, Len(strCode) - intToCut, 1) Loop strCodeClean = Left(strCode, Len(strCode) - intToCut) On Error GoTo ErrLookup strOutput = Application.WorksheetFunction.VLookup(strCodeClean, Sheets("PARAMETER").Range("A:B"), 2, False) SearchA = strOutput Exit Function ErrHandler: SearchA = "N/A" Exit Function ErrLookup: SearchA = "" End Function```
Thanks!