Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

I don't know if this is beyond the scope of Excel, but I thought I'd at least ask before giving up.

The software we are currently using for managing our inventory and known products is going to be replaced by a different one that lacks the same search capabilities (yay for parent company shenanigans). I've been trying to find a way to use Excel instead for searching, and it sort of works, but it's unsurprisingly much more fiddly than the current software we use. Below is an example of how it looks in Excel with only the bare minimum required fields:

Apart from the first 3 columns plus the MRSP column, all of the columns contain a mix of text and numbers somewhere. While I can search by using the filter buttons on each column, this is as mentioned fiddly and I would like to emulate the current software by simply being able to type what I'm looking for in a cell in row 2 and have it search the whole column beneath it and filter out the results. Likewise clearing the contents of the cell should also clear the filtering.

Because we don't always know the exact measurements of what we're looking for, we often do a range search between two values, which I can replicate by hitting the filter button and going into "Number Filters--> Between", but again, a lot of clicking for what is really quick and easy today; we simply type something like 80:85 in a cell to list everything from 80.00 to 85.00. In the text fields, we mostly use wildcard searches with * .

I'm thinking that it should be possible, but I imagine it will require VBA which I don't know. I've been googling for the past week or so trying to find a ready made solution for this, but so far I've come up empty. Normally I'd resign myself to my fate, but our inventory list has some 65.000 entries which often requires a ton of fine-tuning searches across multiple columns to find what we're looking for, hence why I'm desperate for something faster and easier than Excels filter button fiddling.

Is what I want even possible in Excel?

AG3 fucked around with this message at 14:37 on Feb 15, 2022

# ¿ Feb 15, 2022 14:34

Adbot: ADBOT LOVES YOU

# ¿ May 11, 2024 12:14

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

Thanks a lot for the code! I couldn't quite get it to work, but at least I learned something trying to troubleshoot.

Basically when I type something in a box, it'll pop up a runtime error 1004 "Autofilter method of range class failed", with the debug button highlighting this:

code:

'clear existing filer on this column
Range(data_range).AutoFilter Field:=Target.Column

If I comment it out and try again, the same error pops up but targeting this section:

code:

'apply filter
If filter_type = "between" Then
Range(data_range).AutoFilter Field:=Target.Column, Criteria1:=">=" & lower_filter_value, Operator:=xlAnd, Criteria2:="<=" & upper_filter_value

ElseIf filter_type = "contains" Then
ActiveSheet.Range(data_range).AutoFilter Field:=Target.Column, Criteria1:="=*" & filter_value & "*", Operator:=xlAnd

It'll highlight either the top or bottom depending on which type of search I do.

The same error likewise pops up when I try to clear a search box, but with the debug marking that relevant part of the code. Googling seems to indicate that it's a common error, but I can't quite wrap my head around how a fix would work for this particular code.

Time to call it a day and look at it again tomorrow when brain is less fried.

# ¿ Feb 16, 2022 15:29

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

After changing this

code:

data_range = "a1:" & last_cell

to this

code:

data_range = "a1:$j$54381" & last_cell

it changed the error to "application-defined or object-defined error" pointing towards the same code lines as earlier. Just to test I also made a new file with just 11 lines and changed the range accordingly and it gave the same error. Googling and trying various things from there doesn't seem to have done much, it still throws out that error. It doesn't seem to narrow down things much more than the previous error :sweatdrop:

I have the code pasted in the sheet directly instead of as a separate module since I couldn't get it to run at all when it was in a separate module, I don't suppose that's an issue?

# ¿ Feb 17, 2022 10:19

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

Holy crap, that actually fixed it! It doesn't seem to like "& last_cell" , but manually setting the range and dropping last_cell made the whole thing work! I guess the code needs to change as the list grows, but that's a tiny price to pay for a an actual practical filter function.

One thing I noticed is that the wildcard "*" are reduntant since the filter in Excel seems to do that by default; i.e if I search for 1234 it'll still show a line containing ABC1234EDF whether the wildcard * is there or not. Is there a way to make it default to "begins with" when there are no wildcard * present? And for the numbers only fields (columns A to C) "equals" when there are no ":" present?

# ¿ Feb 17, 2022 13:40

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

DRINK ME posted:

Yeah that�s all possible but it was just easier to knock it up than to try type out each change. The wildcard filtering doesn�t sound like it�s doing what it should from your description, so hopefully this works.

I changed how the data range is determined, since the previous attempt wasn�t happening. I think P is your last column but that may need updating - I added capslock notes for each of them.

This works more or less exactly how I had imagined. Thank you so very much, this is going to save me no small amount of headaches if my worst fears regarding the new software comes to pass! If I can buy you a couple of beers through paypal or something do let me know.

# ¿ Feb 18, 2022 11:51

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

So I've been fiddling with this for a bit and tweaked a few things (thanks to Google; I still can't code the stuff :v:

) and there is one thing that has me stumped again. How on earth do you make sorting results not include row 2, the one you enter search criteria into? You'd think there would be an easy way to tell Excel "exclude this row from sorting" or "anchor this row in place and don't move it or its contents" but there doesn't really seem to be, except for row 1 which is the header.

The problem of course is that as soon as you sort, the results start at row 2, which means that you can't use the search box again unless you delete the contents that just appeared (bad) or try to CTRL+Z, which works... sometimes at least. For now I'm resetting it by giving the search fields in row 2 a blue background, which I can sort by to get it to come back on top after I'm done with the actual search. If getting the sort function to leave row 2 alone is impossible then the only solution that comes to mind is to replace entering data in row 2 with ActiveX text fields for entering data instead or something. But that seems like a lot of work for something you'd think was straight forward to tell Excel to do (leave row 2 alone). But then again a lot of things aren't straight forward in Excel, so.

# ¿ Feb 21, 2022 15:42

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

I'm using a VBA code I googled to alter the enter key behavior for my sheet, to make it move right by default instead of down. The problem is, this code affects Excel permanently even in other files and persists through Excel restarts, until I manually run the code again to toggle it off. It looks like this:

code:

Sub EnterKeyBehavior()

If Application.MoveAfterReturnDirection = xlToRight Then
    Application.MoveAfterReturnDirection = xlDown
Else: Application.MoveAfterReturnDirection = xlToRight

End If
End Sub

Is there an easy way to make it turn itself on when the file is opened and turn itself off when it's closed?

# ¿ Oct 13, 2022 10:31

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

Harminoff posted:

The easiest way would be to just put it in workbook activate/deactiviate

Put this in the ThisWorkbook module

Private Sub Workbook_Activate()
Application.MoveAfterReturnDirection = xlToRight
End Sub

Private Sub Workbook_DeActivate()
Application.MoveAfterReturnDirection = xlDown
End Sub

This way, whenever you click onto that workbook it'll change it to the right direction. When you click into another workbook, it'll change it to down.

Thank you, this works great! Several of the other solutions would've been fine for me personally, I didn't even mind the way it worked originally, but the worksheet I'm making is going to be used by people who can charitably be described as "not tech savvy". I need anything that can make this thing work by itself and not cause more tech support work for me.

# ¿ Oct 14, 2022 07:49

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

Is it possible to use vba to import a row from one sheet to another just by typing a specific number in a cell?

For example: I want to be able to type the number 150 or a text string like ABCD in a cell in Column A, and have Excel search a specific column in a different sheet for that string and then copy the contents of the row containing that string to the current row I'm typing in. I've tried googling this and I can't seem to find anything like this, or any commands that seem like they can do exactly this.

# ¿ Jan 18, 2023 11:26

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

Harminoff posted:

It's crazy how good it is, like just trying AG3's question gives working results.

Of course, this one would need a little edit to see the other workbook instead of looking up the current one, but the code does work and is a good starting point.

Here is what I get by editing the quesiton a bit

you could then use a shortcut and press that to bring the data over.

Hey, after reading this I tried it myself, and I guess I phrased it differently because it gave me this code:

code:

Sub SearchAndCopy()
    Dim searchValue As Variant
    Dim searchRange As Range
    Dim foundRow As Range
    
    ' Get the search value from the active cell
    searchValue = ActiveCell.Value
    
    ' Set the search range to the first column of the sheet named "Data"
    Set searchRange = Worksheets("Data").Columns(1)
    
    ' Search for the value in the search range
    Set foundRow = searchRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
    
    ' If the value is found, copy the row to the active sheet
    If Not foundRow Is Nothing Then
        foundRow.EntireRow.Copy
        ActiveCell.Offset(1, 0).EntireRow.Insert xlShiftDown
    Else
        MsgBox "Value not found in data sheet."
    End If
End Sub

It's not exactly what I wanted since I wanted to type the number in the field and then have it search for that number and copy the contents of the row, while this code looks up a number that has already been typed into the currently active cell and then copies the contents of that row into the row beneath the currently active cell. Still, it's amazing that it even worked, but I might be thinking that since I don't know VBA myself :v:

# ¿ Feb 15, 2023 13:17

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

It does, thank you very much!

# ¿ Feb 17, 2023 14:39

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

I have a slightly modified piece of VBA code I got from google that I've been using for one of my Excel sheets at work, but for some reason I can't get it to work on the Excel version on my other PC. It goes like this:

code:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A1:M600")) Is Nothing Then

If Target.Cells.Count > 1 Then
Exit Sub
End If

Selection.Worksheet.Cells.FormatConditions.Delete
Selection.FormatConditions.Add xlExpression, , "TRUE"
Selection.FormatConditions(1).Interior.Color = vbWhite
End If
End Sub

This sets the background color of the selected cell to white instead of whatever color it normally is. This works perfectly on my work PC Excel, but not the Excel version on my other PC. They're both Microsoft 365, though I don't know if there's a version difference since they are from two different providers. It's not that the code crashes or anything on the other Excel version, it just doesn't do anything and I can't figure out why, and all the other code in the sheet works just as it should.

The only appreciable difference between the two that I can think of is that I manually changed the list separator and decimal signs in my work PC's locale in order to make Excel CSV exports work with our web shop, because I couldn't get it to import CSV files that used semicolon as a list separator instead of comma, and I also changed the decimal sign from comma to period to match English. I did try doing the same on the other PC, but that didn't seem to make a difference in getting the code to work.

AG3 fucked around with this message at 14:14 on Nov 14, 2023

# ¿ Nov 14, 2023 14:10

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

That's what's puzzling me. Everything is working perfectly on my primary work PC, but on my other work PC all of the code in the file except the background color changing works and I can't find any reason why that would be the case. No amount of stopping and restarting the code seems to make the color changing function come on either. FormatConditions seems like such a basic function too, so I can't think of a reason why only that would be blocked and not the other code. I'll have to test on a couple of other PCs at work and see if there's a setting on mine specifically that's making things strange.

# ¿ Nov 14, 2023 23:44

Adbot: ADBOT LOVES YOU

# ¿ May 11, 2024 12:14

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

Ninja.Bob posted:

Given you mention changing locale settings, if your language is not English then you may need to change
Visual Basic .NET code:
Selection.FormatConditions.Add xlExpression, , "TRUE"
'e.g. german
Selection.FormatConditions.Add xlExpression, , "WAHR" 

Holy poo poo, it was actually this. I had to change "TRUE" to "SANN" (Norwegian for true) and the code worked. Now the baffling thing here is that my work PC, where the code worked, IS set to Norwegian locale already, but the actual display language in Windows itself is set to English by default from the provider, while my other PC has a Norwegian display language. It's not the actual locale settings that made it stop working, it's the Windows display language.

Thank you so very much, I would never have thought to try that.

# ¿ Nov 15, 2023 09:14

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread