Forum Discussion

sunnyschindler's avatar
sunnyschindler
Brass Contributor
Jul 25, 2023

LOOKUP to include formatting with its Return Value? Formatting Code Function? non-VBA request

Without VBA, is it possible for LOOKUP to return formatting of its Return Value?

It is much easier if Formatting Code Function (similar to CODE function) can be exposed to end user.

 

I need to differentiate LOOKUP output from other formulae outputs within long formulae.

  • bosinander's avatar
    bosinander
    Steel Contributor

    You could come close using HTML but as stated, you need a command macro to make the conversion.

    Column E is textjoined with html tags for sup and sub.

    E1 is named htmlSource. All cells below are saved and reopened as html.

    Pasted in F1, named htmltarget.

    Sub FilterHTML()
        tmpFile = "_tmp.htm"
        
        ThisWorkbook.Activate
        Application.Goto "htmlSource"
        rowCount = Range("a1").SpecialCells(xlCellTypeLastCell).Row - Range("htmlSource").Row
        
        text = "<HTML>"                                 'begin with HTML tag
        For rowCounter = 0 To rowCount                  'loop through the cells
            str = Range("htmlSource").Offset(rowCounter, 0).Value
            If str = "" Then str = "&#00"               'nothing on empty rows
            text = text & str & "<BR>" & vbCrLf         'add line break
        Next rowCounter
        text = text & "</HTML>"                         'end with closing HTML tag
        
        path = ThisWorkbook.path & "\" & tmpFile        'get path to a temp file in same folder
        
        fileNo = FreeFile
        Open path For Output As #fileNo
        Print #fileNo, text                             'Write the selected data into the text file. 'Write' would include "
        Close #fileNo
    
        Application.Goto "htmlTarget"
        Range(ActiveCell, ActiveCell.Offset(Range("a1").SpecialCells(xlCellTypeLastCell).Row - ActiveCell.Row)).ClearContents
        
        Workbooks.Open path                             'fetch html data
        Range(ActiveCell, Range("a1").SpecialCells(xlCellTypeLastCell)).Copy
        ThisWorkbook.Activate
        ActiveSheet.Paste
        
        Windows(tmpFile).Close False                    'dispose the temp file
        Kill path
    End Sub

     

    • sunnyschindler's avatar
      sunnyschindler
      Brass Contributor

      bosinander 

      Thanks for very useful code. However, it is not working; Is it because Unicode is the problem? My string is ":question_mark:4⸍24:bed:️SOME:heavy_dollar_sign:549;:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43;:heavy_dollar_sign:394:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43;:question_mark:4⸍25:bed:️SOME:heavy_dollar_sign:549;:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43;:question_mark:4⸍26:bed:️SOME:heavy_dollar_sign:2619;:heavy_dollar_sign:394:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43;6⸍21:heavy_dollar_sign:1723🛧;:question_mark:7⸍10:bed:️FAIR_MAKATI:heavy_dollar_sign:1581;:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43;:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43:heavy_dollar_sign:84;:question_mark::fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43:heavy_dollar_sign:84:bed:️FAIR_MAKATI:heavy_dollar_sign:1721;:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43:heavy_dollar_sign:84;:heavy_dollar_sign:543:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43:heavy_dollar_sign:84;:question_mark::fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43:heavy_dollar_sign:84:heavy_dollar_sign:37:bed:️FAIR_MAKATI:heavy_dollar_sign:3972"

       

      Reason for my suspecting Unicode as problem is because VBA local window is not showing Unicode for said string.

       

      Thanks for helping!

       

      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor

        because VBA local window is not showing Unicode for said string.

        How about build or edit html page and copy and paste to excel

  • bosinander's avatar
    bosinander
    Steel Contributor

    sunnyschindler If your formatting needs are limited to find out which part of a long formula has returned the result, it may be possible to convert the lookup result to text. ie, if the result is to be numeric. Apply TEXT function around LOOKUP.

    You may then switch between "developer mode" to user mode by right adjusting the result column and have all values look the same but if you need to use the result as input for further calculations, numbers as text will give you bad results.
    This is a possibility but I do not recommend it because of possible calculation erorrs further on.

     

    I'ld rather use a cell with 1 or 0 to switch between visualising what formula part the result comes from.

     

    Above usable in classic Excel vesions.

    If 365 the formula may handle both alfa and numeric replies without making the formula even longer by repeating the lookup part (formula row 3)

     

    If the need is for the end user to know, then I would recommend another approach by putting the lookup part in an own hidden column. Then you could also use conditional formatting and have most of the formatting possibilities.

    It's also possible to use positive and negative results but _showing_ them as positives with some possibilities to switch font color if found by lookup (=negative numbers).

    Here, the numbers are formatted as 0;[Color5]0 and color5 looks like blue.

     

    More about Excel colors eg at http://dmcritchie.mvps.org/excel/colors.htm

Resources