Formatting cells to numbers in vba

Question

I'm currently exporting a document (wsCopy) from a software and paste special valuing the data to another excel workbook I'm creating (wsDest). Within (wsDest), I also have two vlookups, columns AI:AJ, which reference the newly created exported data, a "SupportReference" tab, and a "RegionLookup" tab. My issue is that the values in the exported data is formatted weirdly. Even though the exported data format says "General", the vlookups in column AJ are returning #N/A errors. The only ways to fix this problem is if I click on the referenced cells (column AH), which includes the exported data, hit F2, then Enter, or create a vba that will multiply all of the vlookups in column AI by 1. However, I don't want to do the first option since I'll have to do it for ~14000 rows. The issue with the second option is that, multiplying the entire AI column by 1 won't work if the vlookup in column AI returns with any letter; also, it gets rid of the vlookup in column AI. Below is what I came up with:

Sub CopyOver()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

  Set wsCopy = Workbooks("Export").Worksheets("Sheet1")
  Set wsDest = ThisWorkbook.Worksheets("Data")
  
    '1. Find last used row in the copy range based on data in column A
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
      
    '2. Find first blank row in the destination range based on data in column A
    'Offset property moves down 1 row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
    
    '3. Clear contents of existing data range
    wsDest.Range("A9:AH" & lDestLastRow).ClearContents

    '4. Copy & Paste Data
    wsCopy.Range("A2:AH" & lCopyLastRow).SpecialCells(xlCellTypeVisible).Copy
    wsDest.Range("A9").PasteSpecial Paste:=xlPasteValues

'Start copy down formulas
Dim DestLastPopulatedRow As Long
'Find last populated row
DestLastPopulatedRow = wsDest.Range("A" & Rows.Count).End(xlUp).Row
'Select the rows where formula is to be populated
wsDest.Range("AI9: " & "AJ" & DestLastPopulatedRow).FillDown

End Sub

The very last part of the code (wsDest.Range("AI9: " & "AJ" & DestLastPopulatedRow).FillDown) is where I start having issues because the vlookup formula starts in cell AI9. The vlookup formula is as follows:

Column AI Vlookup: =VLOOKUP(AH9,'SupportReference'!E:E,1,FALSE)

Column AJ Vlookup: =VLOOKUP(AI9,'RegionLookup'!M:M,1,FALSE)

Attempted VBA code multiplying column AI:

With wsDest.Range("AI9: " & "AI" & DestLastPopulatedRow)
    .Value = Evaluate(.Address & "*1")
End With

Let me know if you need more clarification or further data.

Thank you.

Answer 1

If you don't need the VLOOKUP functions to be active, which I expect to be the case because you are copying a large block of data in, you can use VBA to set the value of the lookup cells instead of a function. Here's a sub-procedure where you pass it a destination cell and other lookup parameters, and the VBA finds the value and puts it in the destination cell:

Sub exact_lookup(destination As Range, lookup_value As Variant, lookup_table As Range, column As Integer)
    Dim foundCell As Range
    Set foundCell = lookup_table.Columns(1).Find(lookup_value, , , xlWhole)
    
    If foundCell Is Nothing Then
        destination.Value = CVErr(xlErrNA)
    Else
        destination.Value = foundCell.Offset(0, column - 1).Value
    End If
    
End Sub

Then write a loop to fill the value of each cell where your current approach uses lookup formulas. An example call is:

exact_lookup wsDest.range("AI9"), wsDest.range("AH9").value, worksheets("RegionLookup").range("E:E"), 1

Note: I'm not entirely sure I understand just what you doing, but hopefully this approach will work this is enough to get headed in the right direction.