How to find lowest value while ignoring 0's and print a string as an excel formula

Question

I have an excel column with more than 1K text formulas. These formulas (e.g. (dAA11b+dAA12b)/dAA13b*100, (dAA07b_2G+dAA08b_2G)/dAA09ab_2G*100, ...) are composed by some codes (e.g. dAA11b or dAA07b_2G) that store an int value in a different sheet.

I'm trying to convert these text formulas into proper excel formulas. For that I developed some code that loops through every text formula character.

Assuming we are working with (dAA11b+dAA12b)/dAA13b*100

I defined two strings, str_var = "VLOOKUP(""" and str_var1 = """;Variáveis;MATCH(F1;Variáveis!$A$1:$AD$1;0);FALSE)", that will be added to the main string str_f = "" (that is initially empty) everytime a certain character is detected (e.g. str_f = str_f & str_var and str_f = str_f & a & str_var1).

Moving on, everytime the loop finds a character = "d" the code will add str_var to the main string and after that it will look for the next operator in the formula (+, -, * or /) and detect his corresponding character number (e.g. "+" is e = 8, which represents his position in the length of the whole formula). After finding the next operator the code will look for all the characters behind it until the current character (represented by i) we are in ("d" in this case) and store it initially in a variable (a = dAA11b) and then add it to the main string (str_f) along with the second (str_var1), resulting in str_f = str_f & a & str_var1.

str_var = "VLOOKUP("""
str_var1 = """;Variáveis;MATCH(F1;Variáveis!$A$1:$AD$1;0);FALSE)"

cel = Worksheets(2).Cells(2, 3)
str_f = ""
strLen = Len(cel)


With Sheets("Indicadores")

i = 1
terminou = False
carater = 1

Do
carater = Mid(cel, i, 1)
    If carater = "(" And i = 1 Then
    str_f = "(" & str_f
    .Cells(567, 3).Value = str_f
    End If

    If carater = "(" And i > 1 Then
    str_f = str_f & "("
    .Cells(567, 3).Value = str_f
    End If

    If carater = ")" Then
    str_f = str_f & ")"
    .Cells(567, 3).Value = str_f
    End If

    If carater = "d" Then
    str_f = str_f & str_var
        b = InStr(1, Mid(cel, 1, 999), "/", vbTextCompare)
        c = InStr(1, Mid(cel, 1, 999), "*", vbTextCompare)
        d = InStr(1, Mid(cel, 1, 999), "-", vbTextCompare)
        e = InStr(1, Mid(cel, 1, 999), "+", vbTextCompare)
        f = InStr(1, Mid(cel, 1, 999), ")", vbTextCompare)

        minimo = 0
        If b <> 0 Then
        minimo = b
        End If

        'test = Application.WorksheetFunction.MinIfs(i, f)

        If c <> 0 And c < minimo Then
        minimo = c
        End If

        If d <> 0 And d < minimo Then
        minimo = d
        End If

        If e <> 0 And e < minimo Then
        minimo = e
        End If

        If f <> 0 And f < minimo Then
        minimo = f
        End If

        a = Mid(cel, i, minimo - 2)
        str_f = str_f & a & str_var1
        .Cells(567, 3).Value = str_f

    End If

carater = Mid(cel, i, 1)
    If carater = "+" Then
        str_f = str_f & "+"
        .Cells(567, 3).Value = str_f
    End If

carater = Mid(cel, i, 1)
    If carater = "/" Then
        str_f = str_f & "/"
        .Cells(567, 3).Value = str_f
    End If

carater = Mid(cel, i, 1)
    If carater = "*" Then
        str_f = str_f & "*"
        .Cells(567, 3).Value = str_f
    End If

carater = Mid(cel, i, 1)
    If carater = "+" Or carater = "-" Or carater = "*" Or carater = "/" Then
        b1 = InStr(1, Mid(cel, i, 999), ")", vbTextCompare)
        c1 = InStr(1, Mid(cel, i, 999), "*", vbTextCompare)
        d1 = InStr(1, Mid(cel, i, 999), "-", vbTextCompare)
        e1 = InStr(1, Mid(cel, i, 999), "+", vbTextCompare)
        f1 = InStr(1, Mid(cel, i, 999), "/", vbTextCompare)
        If b1 <= 5 And c1 <= 1 And d1 <= 1 And e1 <= 1 And f1 <= 1 Then
            a1 = Mid(cel, i + 1, (strLen - (strLen - 3)))
        End If
        str_f = str_f & a1
        .Cells(567, 3).Value = str_f
    End If

    If carater = "-" Then
        d2 = InStr(1, Mid(cel, i, 999), "-", vbTextCompare)
        If d2 <= 1 Then
            a2 = Mid(cel, i - 1, i - 2)
        End If
        str_f = str_f & a2
        .Cells(567, 3).Value = str_f
    End If

    carater = Mid(cel, i, 1)
    If carater = "-" Then
        str_f = str_f & "-"
        .Cells(567, 3).Value = str_f
    End If

i = i + 1

Loop Until carater = ""

    '.Cells(567, 3).Formula = str_f
    '.Cells(568, 3).Formula = 
    'end result: str_f = "=(VLOOKUP("dAA11b";Variáveis;MATCH(F1;Variáveis!$A$1:$AD$1;0);FALSE)+VLOOKUP("dAA12b";Variáveis;MATCH(F1;Variáveis!$A$1:$AD$1;0);FALSE))/VLOOKUP("dAA13ab";Variáveis;MATCH(F1;Variáveis!$A$1:$AD$1;0);FALSE)*100
End With"

End Sub

This works fine for some formulas but not for others. The problem is when the code tries to get the characters behind the operator, and because de codes varie in length, the variable a = Mid(cel, i, minimo - 2) assumes the minimo value (which is e = 8 in the example mentioned before) as starting in the current character ("d", i = 2). So even tho "+" is in the 8th position in the formula length the Mid function assumes it as if it the 10th (8+i). This wouldn't be a problem because I subtract -2 but, since the codes varie in length this will not work properly everytime. Is there any other way i can get the full codes before the operators?

The 2nd problem is when I'm about to print the final formula,stored in str_f, .Cells(567, 3).Formula = str_f to the excel sheet it does not recognize it as a formula. I tried in many different ways but nothing... the main problem seems to be because of the equal sign and the quote signs in the codes ("dAA11b"). Any way i can print the string as seen in the last comment of the code into the excel sheet?

I apologize if this sounds very confusing but I can't explain it in any other way, I tried to do it in a previous question with fewer details and people suggested me to ask the same question again but with all the details possible.

Answer 1

Make a copy of your workbook to test this code on. It makes some assumptions because a few details were missing from your question, but I commented the code to try to make it as clear as possible and point out where you can make adjustments to suit your needs. It ran successfully and provided expected results on a sample workbook I created, so you should be able to make this work for you. It doesn't use cell C567 at all because it just converts the Formulas As Text in place. It would be a simple matter to change where the results are output to if necessary though.

Sub tgr()

    Dim wb As Workbook
    Dim wsLookup As Worksheet
    Dim wsData As Worksheet
    Dim aLookup() As Variant
    Dim aData() As Variant
    Dim lCodesLookupCol As Long
    Dim lCodesConvertCol As Long
    Dim i As Long

    Set wb = ActiveWorkbook

    'Change this to the actual worksheet that contains the data that needs to be converted
    Set wsData = wb.Worksheets(2)

    'Change this to the actual worksheet that contains the Codes to Int Values conversion table
    Set wsLookup = wb.Worksheets("Variáveis")

    'This line loads the Codes we need to lookup and their associated Int Values into an array
    'In this sample code, Column A contains the Codes and Column B contains their assocated Int Values
    'Change the column letters to suit your actual data
    'Don't include the header row (which is why it starts at A2)
    aLookup = wsLookup.Range("A2:B" & wsLookup.Cells(wsLookup.Rows.Count, "A").End(xlUp).Row)

    'This sample code assumes that the Codes Column is *BEFORE* the associated Int Values Column (for example, codes in col A is before the int values in col B)
    'If that is not the case, swap the LBound and Ubound (for example, if the codes are in col B and the int values in col A, then the codes are After the int values and the ubound and lbound would need to be swapped)
    lCodesLookupCol = LBound(aLookup, 2)
    lCodesConvertCol = UBound(aLookup, 2)

    'This is the range containing the Formulas Stored As Text on your data worksheet
    'This sample code assumes those formulas are in column C (inferred from your provided code)
    'Be sure to exclude the header row (which is why it starts at C2)
    With wsData.Range("C2", wsData.Cells(wsData.Rows.Count, "C").End(xlUp))

        'Loop through the lookup array and convert all codes into their assocated int values using the Range.Replace method
        For i = 1 To UBound(aLookup, 1)
            .Replace aLookup(i, lCodesLookupCol), aLookup(i, lCodesConvertCol), xlPart, , False
        Next i

        'Now all of the codes should have been replaced with their associated Int values, but the formulas are still just text
        'This block will Load the Formulas As Text into an array
        If .Cells.Count = 1 Then
            ReDim aData(1 To 1, 1 To 1)
            aData(1, 1) = .Formula
        Else
            aData = .Formula
        End If

        'Loop through the aData array to prepend an = sign to convert them into formulas
        For i = 1 To UBound(aData, 1)
            'Verify the cell isn't blank and that it's not already a formula
            If Len(aData(i, 1)) > 0 And Left(aData(i, 1), 1) <> "=" Then aData(i, 1) = "=" & aData(i, 1)
        Next i

        'Text formulas now converted to actual formulas, update the worksheet so that Excel will calculate them
        .Formula = aData
    End With

End Sub