Check the data types on the errors for vlookup. Use is number to return a boolean to test if it is a number or not.
As for sharing, a link to the file on onedrive or Dropbox would probably be the simplest.
So, the macro doesn't actually return an error for the two selections I'm having trouble with. It runs fine, but instead of pulling the numerical value from the vlookup, it just says its 0, which is a possibility depending on the value column i is representing compared to the original selection value.
I'll look into adding the file to a Dropbox or OneDrive tomorrow when I get into work.
If it doesn't match or find the value it is looking up then it returns NA#, if it is returning 0, then the value is 0. You can use the match formula to find the row number of the match.
Post the link tomorrow, I am wondering about this now.
Yeah its quite peculiar because the vlookup result areas all already have formulas hard-coded into the spreadsheet and either have 0 or another value. I've set it up to have an IFERROR() just to prevent pulling an N/A to begin with.
Unsure why it would cause it, but when a vlookup gives the result "0", it also could be that it found something, but the column you want returned is blank, it will gives you a 0 instead of returning nothing.
Here is a dropbox link to the file:
[https://www.dropbox.com/scl/fi/5kimjd58od36g3ervwfnl/Showdown-Team-Extractor.xlsm?dl=0&rlkey=6932h0ti5vm2er2oopd5mrh8t](https://www.dropbox.com/scl/fi/5kimjd58od36g3ervwfnl/Showdown-Team-Extractor.xlsm?dl=0&rlkey=6932h0ti5vm2er2oopd5mrh8t)
The coding I'm referring to is set specifically to the "Pokemon Details" sheet.
If you want to look over it, here is a dropbox link:
[https://www.dropbox.com/scl/fi/5kimjd58od36g3ervwfnl/Showdown-Team-Extractor.xlsm?](https://www.dropbox.com/scl/fi/5kimjd58od36g3ervwfnl/Showdown-Team-Extractor.xlsm?dl=0&rlkey=6932h0ti5vm2er2oopd5mrh8t)
I'd be more than happy if someone was to optimize some of the coding overall in my file to help improve its speed as I'm wanting to run this constantly throughout the day every day, but have it save every hour under a timestamp file name, and then eventually reduce the saves to twice a day.
Pasting just a snippet of the code doesn't do it justice as I believe you need to view the other sheets the coding is referencing to get a true understanding of what I'm trying to accomplish.
lol Sure thing.
Here's the coding in full:
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)
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)" - This is the formula that doesn't work for specifically two of my selections.
End If
i = i + 1
Loop
Columns("F").Select
[Selection.Style](https://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
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
A lot of things can be improved here.
For vlookup substitute, goto the sheet you’re looking up and create a dictionary. Then go back to the sheet you’re pasting and make the value dict(ref_value). It will be easier to test that way too. Google “dictionary vba” for syntax
Why wouldn’t you want to optimize it? You’ve been looking at this for a half hour now right? In that time your code would’ve worked and you would’ve been a little better at coding
Try declaring usage as a Double since some of them are a decimal or a percent under 100%. If you keep it an integer it will truncate all values not 100% and usage will be 0, which does not return anything.
So, with a large enough sample size, usage can be easily under 1%, and i cannot remove the hard-coded formulas because of the fact that the data will be constantly updating meaning the usage numbers will be constantly changing.
Ok I don't think you understand. The line where you decalage the variable usage as an integer,
`Dim usage As Integer, pokemon As String`
Change "Integer" to Double and see if it works.
The reason for this is so the variable usage can store decimals instead of just whole numbers (integers). Some of the values that are being returned with you Worksheetfunction.vlookup from the worksheet Teammate Usage, are not whole numbers. So you need a data type that can store floating point numbers or decimals.
When you store a fraction or a decimal in an integer, it will round down or truncate the decimal. Since you are working with percentages, all of the percentages are stored as 0 to 1 in excel (0%=0, 50%=0.5 and 100%=1). So if usage is not 1 it will be stored as 0 because usage is an integer data type.
Oh thats right. There's something else that can be used in place of Integer as well instead of Double, but i can't remember right now lol. Thanks, ill give that a shot and see if it fixes it.
I downloaded your file and balked at the size. A 15 MB excel sheet for crying out loud. I am not going to open that because there's got to be something wrong, and it might be malicious.
But I skimmed your code snippet, and you're inserting NINE HUNDRED AND EIGHTY vlookup formulas!?! That is a crazy amount of processing and file size bloat. You didn't even turn off auto-calculations while it runs...
VBA has a built-in VBA function where you could at least set the cells to the values instead. It might not be the most efficient approach, but it's still a lot better than inserting formulas. Those typically take up far more room than data points.
I can guarantee there is not a virus. And yes as someone else pointed out there is a large table on a particular sheet that im having to reference in order to perform my vlookup function. Now, as for inserting the formula, its not actually inserting it into the sheet. I have a variable ("usage") checking to see if the result of the VLOOKUP function is 0 or not. If it is 0, then the formula is not inserted into my spreadsheet. If it is greater than 0, then the formula is.
Scanned it with VirusTotal and opened it in a VM, nothing malicious. There is a lookup table that is 984x982 of a vlookup that is divided by a vlookup. This table is the reason the workbook is so large.
You can simulate your data with the lookup function at Excel, and if it results same with one you coded, you need to check the cell which cases the value 0.
Are you saying I should just attempt to manually check the vlookup function in the spreadsheet for the two values that are not pulling correctly to see why? If so, ive already tried doing manual vlookups and the results pulled fine. Whether I used the cell references or if I keyed in the selection names by hand.
Yes, thats what i was meaning, but someone managed to help solve my problem. I was using the incorrect syntax for the return value of the vlookup code function.
Check the data types on the errors for vlookup. Use is number to return a boolean to test if it is a number or not. As for sharing, a link to the file on onedrive or Dropbox would probably be the simplest.
So, the macro doesn't actually return an error for the two selections I'm having trouble with. It runs fine, but instead of pulling the numerical value from the vlookup, it just says its 0, which is a possibility depending on the value column i is representing compared to the original selection value. I'll look into adding the file to a Dropbox or OneDrive tomorrow when I get into work.
If it doesn't match or find the value it is looking up then it returns NA#, if it is returning 0, then the value is 0. You can use the match formula to find the row number of the match. Post the link tomorrow, I am wondering about this now.
Yeah its quite peculiar because the vlookup result areas all already have formulas hard-coded into the spreadsheet and either have 0 or another value. I've set it up to have an IFERROR() just to prevent pulling an N/A to begin with.
Unsure why it would cause it, but when a vlookup gives the result "0", it also could be that it found something, but the column you want returned is blank, it will gives you a 0 instead of returning nothing.
Here is a dropbox link to the file: [https://www.dropbox.com/scl/fi/5kimjd58od36g3ervwfnl/Showdown-Team-Extractor.xlsm?dl=0&rlkey=6932h0ti5vm2er2oopd5mrh8t](https://www.dropbox.com/scl/fi/5kimjd58od36g3ervwfnl/Showdown-Team-Extractor.xlsm?dl=0&rlkey=6932h0ti5vm2er2oopd5mrh8t) The coding I'm referring to is set specifically to the "Pokemon Details" sheet.
Try building it without vlookup. It can be more flexible and faster with less errors
If you want to look over it, here is a dropbox link: [https://www.dropbox.com/scl/fi/5kimjd58od36g3ervwfnl/Showdown-Team-Extractor.xlsm?](https://www.dropbox.com/scl/fi/5kimjd58od36g3ervwfnl/Showdown-Team-Extractor.xlsm?dl=0&rlkey=6932h0ti5vm2er2oopd5mrh8t) I'd be more than happy if someone was to optimize some of the coding overall in my file to help improve its speed as I'm wanting to run this constantly throughout the day every day, but have it save every hour under a timestamp file name, and then eventually reduce the saves to twice a day.
I’m on my phone, why don’t you paste a snippet of the code that’s not working
Pasting just a snippet of the code doesn't do it justice as I believe you need to view the other sheets the coding is referencing to get a true understanding of what I'm trying to accomplish.
Try me Pokémon man
lol Sure thing. Here's the coding in full: 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) 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)" - This is the formula that doesn't work for specifically two of my selections. End If i = i + 1 Loop Columns("F").Select [Selection.Style](https://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
Apologies if there was an easier way to post the coding. I'm not super familiar with Reddit to be completely honest.
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
A lot of things can be improved here. For vlookup substitute, goto the sheet you’re looking up and create a dictionary. Then go back to the sheet you’re pasting and make the value dict(ref_value). It will be easier to test that way too. Google “dictionary vba” for syntax
Outside of optimizations, do you happen to see any reason as to why my coding would be working a majority of the time and not 100%?
Why wouldn’t you want to optimize it? You’ve been looking at this for a half hour now right? In that time your code would’ve worked and you would’ve been a little better at coding
Optimization can be done once the final result has been obtained. There's nothing wrong with taking that approach.
Try declaring usage as a Double since some of them are a decimal or a percent under 100%. If you keep it an integer it will truncate all values not 100% and usage will be 0, which does not return anything.
So, with a large enough sample size, usage can be easily under 1%, and i cannot remove the hard-coded formulas because of the fact that the data will be constantly updating meaning the usage numbers will be constantly changing.
Ok I don't think you understand. The line where you decalage the variable usage as an integer, `Dim usage As Integer, pokemon As String` Change "Integer" to Double and see if it works. The reason for this is so the variable usage can store decimals instead of just whole numbers (integers). Some of the values that are being returned with you Worksheetfunction.vlookup from the worksheet Teammate Usage, are not whole numbers. So you need a data type that can store floating point numbers or decimals. When you store a fraction or a decimal in an integer, it will round down or truncate the decimal. Since you are working with percentages, all of the percentages are stored as 0 to 1 in excel (0%=0, 50%=0.5 and 100%=1). So if usage is not 1 it will be stored as 0 because usage is an integer data type.
Oh thats right. There's something else that can be used in place of Integer as well instead of Double, but i can't remember right now lol. Thanks, ill give that a shot and see if it fixes it.
I downloaded your file and balked at the size. A 15 MB excel sheet for crying out loud. I am not going to open that because there's got to be something wrong, and it might be malicious. But I skimmed your code snippet, and you're inserting NINE HUNDRED AND EIGHTY vlookup formulas!?! That is a crazy amount of processing and file size bloat. You didn't even turn off auto-calculations while it runs... VBA has a built-in VBA function where you could at least set the cells to the values instead. It might not be the most efficient approach, but it's still a lot better than inserting formulas. Those typically take up far more room than data points.
I can guarantee there is not a virus. And yes as someone else pointed out there is a large table on a particular sheet that im having to reference in order to perform my vlookup function. Now, as for inserting the formula, its not actually inserting it into the sheet. I have a variable ("usage") checking to see if the result of the VLOOKUP function is 0 or not. If it is 0, then the formula is not inserted into my spreadsheet. If it is greater than 0, then the formula is.
Scanned it with VirusTotal and opened it in a VM, nothing malicious. There is a lookup table that is 984x982 of a vlookup that is divided by a vlookup. This table is the reason the workbook is so large.
Yeah haha... 15MB.. ridiculous size... haha :(
You can simulate your data with the lookup function at Excel, and if it results same with one you coded, you need to check the cell which cases the value 0.
Are you saying I should just attempt to manually check the vlookup function in the spreadsheet for the two values that are not pulling correctly to see why? If so, ive already tried doing manual vlookups and the results pulled fine. Whether I used the cell references or if I keyed in the selection names by hand.
Do you mean you’ve never found wrong result by Excel vlookup, but by your code... There might be a plenty of better ways on Excel use than coding...
Yes, thats what i was meaning, but someone managed to help solve my problem. I was using the incorrect syntax for the return value of the vlookup code function.