+ Reply to Thread
Results 1 to 7 of 7

How to Use XLOOKUP In VBA

Hybrid View

  1. 11-30-2022, 09:54 AM #1
    PosseJohn
    PosseJohn is offline
    Forum Contributor
    Join Date
    10-25-2010
    Location
    Phoenix, Arizona, USA
    MS-Off Ver
    O365
    Posts
    202

    How to Use XLOOKUP In VBA

    I have a datasheet that contains many cells that receive data from an SQL query.

    The data arrives in 'text' format.

    To perform numerical comparison, I convert the data to numeric, after doing so, the value goes to decimal format.

    Depending on the result, determines the format I want to apply.

    Data Sheet:
    COL-A = Unit Number
    COL-B = System Name
    COL-C = Analysis Name
    COL-D = Sample Date/Time
    COL-E = Oldest Result
    COL-F = Second Oldest Result
    COL-G = Most Recent Result

    Another sheet retains the formatting information
    COL-A = Unit Number
    COL-B = System Name
    COL-C = Analysis Name
    COL-D = Assigned Format

    I am trying to use XLOOKUP to reformat the newly converted text-to-value. See the modSupportCode VBA sheet.

    Any assistance is truly appreciated.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-30-2022, 10:42 AM #2
    WideBoyDixon
    WideBoyDixon is offline
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: How to Use XLOOKUP In VBA

    Probably easier to use a dictionary in this case for the lookup since you want multiple criteria:

    Sub FormatData()
'Used to convert results that are 'numbers' from TEXT to NUMERIC
Dim varMax As Long
Dim varLastRow As Long
Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction
Dim fmt As Worksheet: Set fmt = shFormat
Dim rpt As Worksheet: Set rpt = shReport
Dim Lookup_Value As String
Dim If_Not_Found As String
Dim Result As Variant
Dim Lookup_Array As Object
Dim This_Row As Long

    varLastRow = shReport.Range("A" & Rows.Count).End(xlUp).Row
    varMax = shFormat.Range("A" & Rows.Count).End(xlUp).Row
    
    Set Lookup_Array = CreateObject("Scripting.Dictionary")
    For This_Row = 2 To varMax
        Lookup_Array.Item(shFormat.Cells(This_Row, 1).Value & "|" & shFormat.Cells(This_Row, 2).Value & "|" & shFormat.Cells(This_Row, 3).Value) = shFormat.Cells(This_Row, 4).Value
    Next This_Row
    
    'Perform after all queries
    For Each cl In shReport.Range("E4:G" & varLastRow).Cells
        If IsNumeric(cl.Value) Then
            
            'Inputs
            Lookup_Value = rpt.Cells(cl.Row, 1).Value & "|" & rpt.Cells(cl.Row, 2).Value & "|" & rpt.Cells(cl.Row, 3).Value
            If_Not_Found = "Not Found"
                
            'Perform XLOOKUP and Store To Variable
            If Lookup_Array.Exists(Lookup_Value) Then
                Result = Lookup_Array.Item(Lookup_Value)
            Else
                Result = If_Not_Found
            End If
                
'***********************************************************************
'***********************************************************************
  
            Select Case Result
                Case "0"  '0-decimals
                    cl.Value = Format(Val(Range(cl.Address)), "#,##0")
                Case "1"  '1-decimals
                    cl.Value = Format(Val(Range(cl.Address)), "#,##0.0")
                Case "2"  '2-decimals
                    cl.Value = Format(Val(Range(cl.Address)), "#,##0.00")
                Case "3"  '3-decimals
                    cl.Value = Format(Val(Range(cl.Address)), "#,##0.000")
                Case "4"  '4-decimals
                    cl.Value = Format(Val(Range(cl.Address)), "#,##0.0000")
                Case "5"  '5-decimals
                    cl.Value = Format(Val(Range(cl.Address)), "#,##0.00000")
                Case "6"  '6-decimals
                    cl.Value = Format(Val(Range(cl.Address)), "#,##0.000000")
                Case "7"  '7-decimals
                    cl.Value = Format(Val(Range(cl.Address)), "#,##0.0000000")
                Case "8"  '8-decimals
                    cl.Value = Format(Val(Range(cl.Address)), "#,##0.00000000")
                Case "9"  '9-decimals
                    cl.Value = Format(Val(Range(cl.Address)), "#,##0.000000000")
                Case "General"  'AS-IS
                    cl.Value = Val(Range(cl.Address))
                Case "Date1"    'M/D/YY
                    cl.Value = Format(Val(Range(cl.Address)), "MM-DD-YY")
                Case "Date2"    'MM/DD/YYYY
                    cl.Value = Format(Val(Range(cl.Address)), "MM-DD-YYYY")
                Case "Date3"    'D-MMM-YY
                    cl.Value = Format(Val(Range(cl.Address)), "D-MMM-YY")
                Case "Date4"    'DD-MMM-YY
                    cl.Value = Format(Val(Range(cl.Address)), "DD-MMM-YY")
                Case "Date5"    'DD-MMM-YYYY
                    cl.Value = Format(Val(Range(cl.Address)), "DD-MMM-YYYY")
                Case "Date6"    'D-MMM-YYYY
                    cl.Value = Format(Val(Range(cl.Address)), "D-MMM-YYYY")
                Case "Date7"    'M/D/YY H:NN AM/PM
                    cl.Value = Format(Val(Range(cl.Address)), "M/D/YY H:mm AM/PM")
                Case "Date8"    'M/D/YY H:NN 24
                    cl.Value = Format(Val(Range(cl.Address)), "MM-DD-YY HH:mm")
                Case "Date9"    'H:NN AM/PM
                    cl.Value = Format(Val(Range(cl.Address)), "H:mm AM/PM")
                Case "Date10"   'H:NN 24
                    cl.Value = Format(Val(Range(cl.Address)), "HH:mm")
                Case "Exponential1" '2-decimals + 2 Expo
                    cl.Value = Format(Val(Range(cl.Address)), "0.00E+00")
                Case "Exponential2" '3-decimals + 2 Expo
                    cl.Value = Format(Val(Range(cl.Address)), "0.000E+00")
                Case "Exponential3" '4-decimals + 2 Expo
                    cl.Value = Format(Val(Range(cl.Address)), "0.0000E+00")
                Case "Exponential4" '1-decimals + 3 Expo
                    cl.Value = Format(Val(Range(cl.Address)), "0.0E+000")
                Case "Exponential5" '2-decimals + 3 Expo
                    cl.Value = Format(Val(Range(cl.Address)), "0.00E+000")
                Case "Exponential6" '3-decimals + 3 Expo
                    cl.Value = Format(Val(Range(cl.Address)), "0.000E+000")
                Case "Exponential7" '4-decimals + 3 Expo
                    cl.Value = Format(Val(Range(cl.Address)), "0.0000E+000")
            End Select
        End If
    Next
    
    'Perform after ALL queries and formatting
    For Each cl In Range("TrendTop", Cells(varLastRow, Range("TrendTop").Column)).Cells
        cl.Formula = "=IF(OR(ISTEXT(" & Cells(cl.Row, 5).Address & _
                          "),ISTEXT(" & Cells(cl.Row, 6).Address & _
                          "),ISTEXT(" & Cells(cl.Row, 7).Address & ")),"""",IF(AND(" & _
        Cells(cl.Row, 5).Address & ">" & Cells(cl.Row, 6).Address & "," & Cells(cl.Row, 6).Address & ">" & Cells(cl.Row, 7).Address & "),DD,IF(AND(" & _
        Cells(cl.Row, 5).Address & "<" & Cells(cl.Row, 6).Address & "," & Cells(cl.Row, 6).Address & "<" & Cells(cl.Row, 7).Address & "),UU,IF(AND(" & _
        Cells(cl.Row, 5).Address & "=" & Cells(cl.Row, 6).Address & "," & Cells(cl.Row, 6).Address & "=" & Cells(cl.Row, 7).Address & "),SS,IF(AND(" & _
        Cells(cl.Row, 5).Address & ">" & Cells(cl.Row, 6).Address & "," & Cells(cl.Row, 6).Address & "=" & Cells(cl.Row, 7).Address & "),DS,IF(AND(" & _
        Cells(cl.Row, 5).Address & "<" & Cells(cl.Row, 6).Address & "," & Cells(cl.Row, 6).Address & "=" & Cells(cl.Row, 7).Address & "),US,IF(AND(" & _
        Cells(cl.Row, 5).Address & ">" & Cells(cl.Row, 6).Address & "," & Cells(cl.Row, 6).Address & "<" & Cells(cl.Row, 7).Address & "),DU,IF(AND(" & _
        Cells(cl.Row, 5).Address & "<" & Cells(cl.Row, 6).Address & "," & Cells(cl.Row, 6).Address & ">" & Cells(cl.Row, 7).Address & "),UD,IF(AND(" & _
        Cells(cl.Row, 5).Address & "=" & Cells(cl.Row, 6).Address & "," & Cells(cl.Row, 6).Address & ">" & Cells(cl.Row, 7).Address & "),SD,IF(AND(" & _
        Cells(cl.Row, 5).Address & "=" & Cells(cl.Row, 6).Address & "," & Cells(cl.Row, 6).Address & "<" & Cells(cl.Row, 7).Address & "),SU,""Error""))))))))))"
    Next cl

End Sub
    WBD
    Office 365 on Windows 11, looking for ✶ rep!
    Reply With QuoteRegister To Reply
  3. 11-30-2022, 11:33 AM #3
    PosseJohn
    PosseJohn is offline
    Forum Contributor
    Join Date
    10-25-2010
    Location
    Phoenix, Arizona, USA
    MS-Off Ver
    O365
    Posts
    202

    Re: How to Use XLOOKUP In VBA

    Thank you. The Lookup_Value returns as empty?
    Reply With QuoteRegister To Reply
  4. 11-30-2022, 11:48 AM #4
    WideBoyDixon
    WideBoyDixon is offline
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: How to Use XLOOKUP In VBA

    Oh. I tested this on my Excel before posting. Everything got formatted correctly. Is this on Windows?

    WBD
    Reply With QuoteRegister To Reply
  5. 11-30-2022, 01:02 PM #5
    PosseJohn
    PosseJohn is offline
    Forum Contributor
    Join Date
    10-25-2010
    Location
    Phoenix, Arizona, USA
    MS-Off Ver
    O365
    Posts
    202

    Re: How to Use XLOOKUP In VBA

    Yes, Windows 10 Enterprise, using 365 Excel.

    I'll try again.
    Reply With QuoteRegister To Reply
  6. 11-30-2022, 01:27 PM #6
    PosseJohn
    PosseJohn is offline
    Forum Contributor
    Join Date
    10-25-2010
    Location
    Phoenix, Arizona, USA
    MS-Off Ver
    O365
    Posts
    202

    Re: How to Use XLOOKUP In VBA

    I have copied the entire post you provided, IT DOES WORK!

    Thank you.
    Reply With QuoteRegister To Reply
  7. 11-30-2022, 01:50 PM #7
    WideBoyDixon
    WideBoyDixon is offline
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: How to Use XLOOKUP In VBA

    Cool. You might want to update your profile to reflect the version of Excel that you're using

    WBD
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] XLOOKUP gives N/A
    By Hexdax in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-23-2022, 04:06 AM
  2. [SOLVED] if and xlookup
    By pickslides in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2022, 07:24 AM
  3. If this do this XLOOKUP otherwise do this XLOOKUP
    By MattKoleczko in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-21-2022, 08:14 AM
  4. [SOLVED] Xlookup in PQ
    By Excelski in forum Excel General
    Replies: 7
    Last Post: 09-09-2021, 07:15 AM
  5. Two-way Xlookup
    By Ballet4ever in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-28-2021, 10:11 PM
  6. [SOLVED] Xlookup with VBA, need some help!
    By CSimm in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-19-2021, 03:14 PM
  7. Xlookup & sum
    By lynusann in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-19-2020, 11:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1