VBA Formula Not Working on All Data

SanTrapGamer

New Member
Joined
Nov 29, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.


1611516164267.png



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.

1611516318800.png


VBA Code:
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

SanTrapGamer

New Member
Joined
Nov 29, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,547
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top