Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

trying to put 2 data sets into separate areas in a user form

0

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
Answer
Discuss

Discussion

been solved thx all
swqa1234 May 23, '24 at 1:10 pm
Add to Discussion

Answers

0

 Hello swqa1234 and welcome to the forum,

Thanks for posting that you have solved it. It seems you solved it while I was working on my solution.

I have attached a file with the modificcations I made to solve your issue.

1) changed "Integer" to "Long" >> Integer max is 32,767;  2) added a check to see if a name has been entered;  3) added a sheet "UserForm2" with data (col's A to U) removed; this is the sheeet the revised macros work on;  4) in the "findMID" macro changed Cells(i, 7) = 1 to Cells(i, 7) > 0;  5) added code to clear copy mode.

Cheers   :-)

Discuss

Discussion

Thx WillieD24 unfortunately it doesnt quite work and returns all sorts of data and not in the right place.

Thx for looking however and appreciate the time
swqa1234 May 23, '24 at 4:10 pm
@swqa1234,

Did you use the revised macros in module2? If you used the original macros I can understand there would be issues. I tested the macros in module2 and they seemed to yeild the results you were looking for (at least they worked for me with 2016). I guess I should have updated the buttons to the revised macros.
Nonetheless, glad you were able to find a solution that works for you.

Cheers   :-)
WillieD24 (rep: 557) May 23, '24 at 5:13 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login