0

I am working with lots of laboratory test results that are presented within one column in excel however the results come in different units from different labs so I need to convert some of the data so that all of the lab data is in the same units.

I have created a macro that looks up the lab data that I want to change and will convert the data however some of the results are presented as <0.07 etc. however the macro will not recognise and convert anything with < at the start of the number.

Can anyone suggest a way that I can amend the following macro to also convert lab results that contain < at the start?

Sub CONVERT_UNITS()
'
' CONVERT_UNITS Macro
'

'
    ActiveCell.FormulaR1C1 = _
        "=IF(RC12=""ug/kg"",RC11/1000,IF(RC12=""mg/l"",RC11*1000,RC11))"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M" & Range("L" & Rows.Count).End(xlUp).Row)
    Range(Range("M2"), Range("M2").End(xlDown)).Select
End Sub

For reference my lab data is contained in column K with the units presented in column L. I want the converted lab results to be put in column M (see example data below). I am only converting results that are in ug/kg (divide result by 1000) and mg/l (multiply result by 1000), all the other results will remain as they are.

Example Lab Test Data

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
user916794
  • 1
  • What is the format of those cells? usually `<0.07` should be read as text, so it's no surprise you can't use it as a number. You will need to test for `<` and maybe `>` and remove it before doing your calculation, then reinsert it afterwards – cybernetic.nomad Jun 26 '18 at 21:30

1 Answers1

1

I would create a User Defined Function for that:

Public Function convertResults(ByVal result As Variant, ByVal unit As String) As Variant
    Dim hasSmaller As Boolean
    If Left(result,1) = "<" Then
        hasSmaller = True
        result = Replace(result, "<", "")
    End If
    If unit = "ug/kg" Then result = result / 1000
    If unit = "mg/l" Then result = result * 1000
    If hasSmaller Then result = "<" & result
    convertResults = result
End Function

... and then (for example in cell M2) just write =convertResults(K2, L2).

Matteo NNZ
  • 11,930
  • 12
  • 52
  • 89