VBA Formula Not Working on All Data

[SanTrapGamer]

Hello,

I have the provided images that has a portion of coding that activates when a selection within Column A (first screenshot) has been chosen. macro it doesn't seem to pull the formula result correctly.

The data populates in columns E & F perfectly fine for every option in Column A except for "Incineroar" and "Tapu Fini". If I manually key in the VLOOKUP formula (see screenshot below for sheet referenced for VLOOKUP result) for the appropriate cell reference and column index, it pulls the correct usage percentage.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False


    'If chosen cell is not in Column A, then ending macro.
    If ActiveCell.Column <> 1 Then
        Exit Sub
    
    'If chosen cell is in Column A, then move to next step.
    ElseIf ActiveCell.Row = 1 Then
        Exit Sub
        
    Else
        Application.EnableEvents = False
        
        LastRow = ActiveSheet.UsedRange.Rows.Count
        
        Range("E2:F" & LastRow).ClearContents
        
        Sheets(4).Select
        
        i = 2
        
        Dim usage As Integer, pokemon As String
        
        Do Until i = 982
            pokemon = ActiveCell.Value
            usage = Application.WorksheetFunction.VLookup(pokemon, Sheets("Teammate Usage").Range("A1:AKT983"), i, 0) 'portion of coding that is not pulling the correct values for "Incineroar" or "Tapu Fini"
            If usage <> 0 Then
                y = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row + 1
                Range("E" & y) = Sheets(3).Cells(1, i)
                Range("F" & y).Formula = "=VLOOKUP(" & ActiveCell.Address & ",'Teammate Usage'!$A:$AKT," & i & ",0)"
            End If
            i = i + 1
        Loop
        
        Columns("F").Select
            Selection.Style = "Percent"
            Selection.NumberFormat = "0.000%"
            
        'Sorts all of the "Usage %" columns (along with corresponding data) in order from highest to lowest.
        LastRow = ActiveSheet.UsedRange.Rows.Count
        
        Range("F2").Select
        ActiveWorkbook.Worksheets("Pokemon Details").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Pokemon Details").Sort.SortFields.Add2 Key _
            :=Range("F2"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortTextAsNumbers
        With ActiveWorkbook.Worksheets("Pokemon Details").Sort
            .SetRange Range("E2:F" & LastRow)
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
    End If
        
        Application.EnableEvents = True
        
Application.ScreenUpdating = True

End Sub

 

[SanTrapGamer]

Hello,

I have the provided images that has a portion of coding that activates when a selection within Column A (first screenshot) has been chosen. macro it doesn't seem to pull the formula result correctly.


View attachment 30399


The data populates in columns E & F perfectly fine for every option in Column A except for "Incineroar" and "Tapu Fini". If I manually key in the VLOOKUP formula (see screenshot below for sheet referenced for VLOOKUP result) for the appropriate cell reference and column index, it pulls the correct usage percentage.

View attachment 30400

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False


    'If chosen cell is not in Column A, then ending macro.
    If ActiveCell.Column <> 1 Then
        Exit Sub
   
    'If chosen cell is in Column A, then move to next step.
    ElseIf ActiveCell.Row = 1 Then
        Exit Sub
       
    Else
        Application.EnableEvents = False
       
        LastRow = ActiveSheet.UsedRange.Rows.Count
       
        Range("E2:F" & LastRow).ClearContents
       
        Sheets(4).Select
       
        i = 2
       
        Dim usage As Integer, pokemon As String
       
        Do Until i = 982
            pokemon = ActiveCell.Value
            usage = Application.WorksheetFunction.VLookup(pokemon, Sheets("Teammate Usage").Range("A1:AKT983"), i, 0) 'portion of coding that is not pulling the correct values for "Incineroar" or "Tapu Fini"
            If usage <> 0 Then
                y = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row + 1
                Range("E" & y) = Sheets(3).Cells(1, i)
                Range("F" & y).Formula = "=VLOOKUP(" & ActiveCell.Address & ",'Teammate Usage'!$A:$AKT," & i & ",0)"
            End If
            i = i + 1
        Loop
       
        Columns("F").Select
            Selection.Style = "Percent"
            Selection.NumberFormat = "0.000%"
           
        'Sorts all of the "Usage %" columns (along with corresponding data) in order from highest to lowest.
        LastRow = ActiveSheet.UsedRange.Rows.Count
       
        Range("F2").Select
        ActiveWorkbook.Worksheets("Pokemon Details").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Pokemon Details").Sort.SortFields.Add2 Key _
            :=Range("F2"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortTextAsNumbers
        With ActiveWorkbook.Worksheets("Pokemon Details").Sort
            .SetRange Range("E2:F" & LastRow)
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
   
    End If
       
        Application.EnableEvents = True
       
Application.ScreenUpdating = True

End Sub

I apologize if the coding isn't the most optimized. It does its job effectively for the time being except for the small issue.