[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

Excel VBA: FIND, MODIFY, DELETE

Excel VBA:  Novice
Need assistance creating vba code that'll FIND all instances of a specified criteria within a "table" and list them in a List box for selection .  The end-user would need to be able to select one of the options, then have the choice to MODIFY or DELETE specified fields in the form.  Hope this makes sense and is doable.

I did find some vba but was unable to get it to work in my project .  

Would someone be of assistance in getting this code to work?  Not sure if I'm naming something wrong or if something isn't properly defined.  My form was appearing when I clicked the "FIND" button but now it's giving me Run-Time 424 ...  

Thank you in Advance for the assistance
Sample-MPT-UPDATE.zip
0
"Abys" Wallace
Asked:
"Abys" Wallace
  • 4
  • 4
  • 2
2 Solutions
 
SteveCommented:
OK, first big issue is that PMG is not defined...

need to have some code like:
Dim PMG as worksheet
ThisWorkbook.Sheets("PMG")

Open in new window


as you use PMG a lot is best to define it as a Public variable and then set it as the form loads:
Add this into a module then change the button event to Call 'Run_Uform'

Public PMG As Worksheet
Sub Run_Uform()
Set PMG = ThisWorkbook.Sheets("PMG")
frmMain.Show
End Sub

Open in new window

0
 
SteveCommented:
you are holding the data in a table.. so helps to work with listobjects rather than actial ranges.

This link has some good starting information for working with tables in VBA.

Other than that, it seems to be OK.
Once you get the ListObject under control I am sure it will all fall into place.
0
 
andrewssd3Commented:
There's quite a lot going on here, but I have made the basic changes you need to get this started - in particular as TheBarman said using the ListObject properties to get the relevant column.  I have not fixed everything as it's not always clear what should be happening, but I put in example of the right code in Find, First, Last and Amend - 'c' should always be set to the current row, then the updating should work.  Give it a try and see if you can get it working as you want - any more questions just come back.

Stuart
Sample-MPT-UPDATE.xlsm
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
"Abys" Wallaceself employedAuthor Commented:
@The_Barman  Thank you for the information ..  Seems helpful but as I'm a novice in VBA coding I don't honestly know where to begin with where to place the "listobjects" statements and the appropriate syntax when asking the code to search through a specified range...  I did bookmark the page for more studying once I learn more with working with Tables and VBA
0
 
"Abys" Wallaceself employedAuthor Commented:
@andrewssd3  your example helps a lot!  At least it's doing what it originally did when I placed data in the Search field.  the code finds the "search criteria" and let's me know how many instances were found ..

I just need the found data to be listed in the list box below afterwards {Sub FindAll()} ... Once listed in the listbox the end user should be able to select one of the choices which would then populate the textboxes above..   The end user should then be able to edit and amend the data populated in the textboxes back into the table.

I'm was looking into working with listobjects as suggested by @the_Barman but keep getting object not defined errors .. I'm sooo lost and hope I'm not making more of a mess ... Thank you everyone for looking into this for me

Sub FindAll()
    Dim oSh As Worksheet

    Dim strFind As String    'what to find
    Dim rFilter As Range     'range to search
    Set oSh = ActiveSheet
   
    Set rFilter = oSh.ListObjects("PMGTracker").ListColumns(3).DataBodyRange.Select
    

    Set rng = oSh.ListObjects("PMGTracker").Range.Select
    
    strFind = Me.TextBox1.Value
    With oSh.ListObjects("PMGTracker")
        If Not .AutoFilterMode Then .Range("A3").AutoFilter
        rFilter.AutoFilter Field:=1, Criteria1:=strFind
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
        Me.ListBox1.Clear
        For Each c In rng
            With Me.ListBox1
                .AddItem c.Value
                .List(.ListCount - 1, 1) = c.Offset(0, 3).Value
                .List(.ListCount - 1, 2) = c.Offset(0, 5).Value
                .List(.ListCount - 1, 3) = c.Offset(0, 2).Value
                .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
            End With
        Next c
    End With
End Sub

Open in new window

0
 
"Abys" Wallaceself employedAuthor Commented:
does anyone know how to specify a cell within a table as a list object?  I think once I can code cell reference's I can fix this portion of the Find ALL() code { If Not .AutoFilterMode Then .Range("A3").AutoFilter }:

    strFind = Me.TextBox1.Value
    With rng
        If Not .AutoFilterMode Then .Range("A3").AutoFilter
        rFilter.AutoFilter Field:=1, Criteria1:=strFind
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
        Me.ListBox1.Clear
        For Each c In rng
            With Me.ListBox1
                .AddItem c.Value
                .List(.ListCount - 1, 1) = c.Offset(0, 3).Value
                .List(.ListCount - 1, 2) = c.Offset(0, 5).Value
                .List(.ListCount - 1, 3) = c.Offset(0, 2).Value
                .List(.ListCount - 1, 4) = c.Offset(0, 4).Value

Open in new window

0
 
andrewssd3Commented:
What cell do you want to get exactly.  You can get the whole table with something like
Set rng = oSh.ListObjects("PMGTracker").Range

Open in new window

Do you really need to autofilter here - if you don't need to put a filter on the actual list, you could just read through all the cells in a column, identify the ones that match and get the other info.  If that would do let me know and I'll do some sample code for you
0
 
andrewssd3Commented:
I was just looking at this again - try this for a new find routine which would not need a FindAll. You just look through the rows and build the listbox list as you go, even if there is only one entry.  There is still work to do, but it looks as if you're getting there.  I attach the whole file again as I made a couple of other minor changes (most importantly to recognise that a listindex of 0 does mean the first row is selected in the ListBox1_Click)
Sample-MPT-UPDATE.xlsm
0
 
"Abys" Wallaceself employedAuthor Commented:
Yesss!  :)  andrewssd3  thank you and the Find code works great with your modifications and is much easier without the Find_All()  ..  I'm learning more as I work with tables and vba and how to specify ranges .  I'm currently working to update the Amend, Add, and Delete buttons ...  Making progress and I think you for being patient as I work through this as it is a learning process for me  ..
0
 
andrewssd3Commented:
I'm glad it's helping - the best way to learn is to build on some example code.  If you're happy with this, why not allocate some points and close this one off, then if you have additional questions, you can raise a new query - otherwise this one starts to get a bit cluttered.

Stuart
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now