Consulting

Results 1 to 13 of 13

Thread: Finding data

  1. #1

    Finding data

    Hi All. Yes I am a newbie at coding and thought I was doing well. The celebration that I got this piece working was something to see!

    problem: I have a form in excel. It has a user form. The data I want from sub "finddata" goes in the top part of the form (Employee Information) and sub "finalMID" goes in to the part below (device information). I call both with separate buttons cos I thought it may be easier. The data is searched by 1 reference Bussiness ID although the Device Info also has a separate condition as per code.

    Issues
    The data was on another sheet (Data) but couldnt get it to pull into the active sheet (UserForm) so its in hidden columns A to T
    The returned data is dynamic and can be anything from 1 row to 165 rows
    Obviously if the "finddata" returns more than is avail in Employee Info, it messes up Device Info so they do not list the returned data properly
    as the returned data is dynamic, my structure to clear the contents of returned data in the form can be wrong and changes depending upon how many rows are returned

    Request
    can the data returned be listed in the 2 parts of the form dynamically so Employee and Device Info re size to fit?
    can the data be pulled in from the "data" worksheet rather than being in the "UserForm" worksheet
    would it be better/easier for the data to list in a form created from a user input asking for the search criteria

    I need your genius because I am out of ideas and obviously not up to the task. Yes give me sympathy lol.

    here's the code. Hope you can help and thanks for looking

    Sub finddata()
        Dim athletename As String 'this is actually the search input
        Dim finalrow As Integer
        Dim i As Integer
        Worksheets("UserForm").Range("X19:AC31").ClearContents
        athletename = Worksheets("UserForm").Range("Y1").Value 'search input location
        finalrow = Worksheets("UserForm").Range("A15000").End(xlUp).Row
        Application.ScreenUpdating = False
        For i = 2 To finalrow
            If Cells(i, 1) = athletename Then
                Range(Cells(i, 2), Cells(i, 7)).Copy
                Range("X33").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
            End If
        Next i
        Application.ScreenUpdating = True
        Range("Y1").Select
    End Sub
    
    Sub findMID()
        Dim athletename As String 'same search input as sub finddata
        Dim finalrow As Integer
        Dim i As Integer
        Worksheets("UserForm").Range("X36:AE37").ClearContents
        athletename = Worksheets("UserForm").Range("Y1").Value
        finalrow = Worksheets("UserForm").Range("A15000").End(xlUp).Row
        Application.ScreenUpdating = False
        For i = 2 To finalrow
            If Cells(i, 1) = athletename And Cells(i, 7) = 1 Then 'this needs to be value greater than 0 to cover 1, 2, 3, etc
                Range(Cells(i, 8), Cells(i, 12)).Copy
                Range("X37").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
            End If
        Next i
        Application.ScreenUpdating = True
        Range("Y1").Select
    End Sub

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,132
    Location
    Welcome to VBAX swqa1234. I have amended your title to the thread, and amended the code your submitted so its readable. Hopefully someone can answer your request
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    thx for your help

  4. #4
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,132
    Location
    Any chance you can post a workbook with dummy data? We seriously need to see what you are looking at.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,903
    Untested, both subs in one, so a big guess to test:
    Sub finddata()
    Dim athletename As String                        'this is actually the search input
    Dim finalrow As Long
    Dim i As Long, Destn As Range
    
    With Worksheets("UserForm")
      .Range("X19:AE" & .Rows.Count).ClearContents
      Set Destn = .Range("X19")
      athletename = .Range("Y1").Value               'search input location
      With Worksheets("data")
        finalrow = .Range("A15000").End(xlUp).Row
        Application.ScreenUpdating = False
        For i = 2 To finalrow
          If .Cells(i, 1) = athletename Then
            .Range(.Cells(i, 2), .Cells(i, 7)).Copy
            Destn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
            Set Destn = Destn.Offset(1)
          End If
        Next i
        Destn.Offset(3).Value = "Device Info"
        Set Destn = Destn.Offset(4)
        For i = 2 To finalrow
          If .Cells(i, 1) = athletename And .Cells(i, 7) = 1 Then 'this needs to be value greater than 0 to cover 1, 2, 3, etc
            .Range(.Cells(i, 8), .Cells(i, 12)).Copy
            Destn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
            Set Destn = Destn.Offset(1)
          End If
        Next i
      End With 'Worksheets("data")
      .Range("Y1").Activate
    End With 'Worksheets("UserForm")
    Application.ScreenUpdating = True
    End Sub
    Attach a workbook; it stops us guessing wrongly.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    test file with dummy data - code not inserted however
    Attached Files Attached Files

  7. #7
    done that thx

  8. #8
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,132
    Location
    Quote Originally Posted by swqa1234 View Post
    done that thx
    You have done what exactly? This is a heads up for your information. When you post here, we don't need all the fluff, as it simply confuses the issue. Simply tell us what the issue is, what you were doing at the time the issue failed, what system you are using, and what you would like to see as an end result, and we will do our best to find a solution.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    sorry Aussiebear. I was just replying to p54cal that I had uploaded the dummy file and test data. thought it would go direct to them. cheers for the help

  10. #10
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,132
    Location
    Okay no problem then.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  11. #11
    tried the code but unfortunately throws up an error: PasteSpecial method of range class failed highlighting

    Destn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    any ideas? thx

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,903
    Quote Originally Posted by swqa1234 View Post
    tried the code but unfortunately throws up an error: PasteSpecial method of range class failed highlighting

    Destn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    any ideas? thx
    OK here.
    Attached with a few tweaks.
    I've left it with no data in the 'form' and the value 1065 added to cell Y1. Just click the button (Button4) to see what happens.
    I've moved your Business Unit Survey to one side because the macro has to clear a non-specific number of rows below row 18 in those same columns.

    I see there's a reference to Master Data File v2.5.xlsm; is this where the data on the Data sheet comes from? If so there may be no need to copy/paste that data into this workbook.
    Also, I would recommend the use of Tables and perhaps also (Power) Queries to get the data in. One advantage is that tables can update themselves and shift data/tables below them up and down according to their sizes; this is the main problem behind this thread. There may even be no need for macros at all.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13
    I will add in the last bit to add another list table below what you have already produced as I think I see how it works. Will give it a go anyway. yes, the master file has the original source data which is then pulled into this one as it has different tabs from different sources

    p45cal - A massive thank you for your help. this is excellent and works a treat. Wizard indeed !

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •