1 Attachment(s)

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.

Thank you in advance

D.

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

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?

1 Attachment(s)

Re: Convert a formula to a Macro

Does the attached help to achieve what you need?:confused:

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

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

1 Attachment(s)

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!