swqa1234
05-23-2024, 03:35 AM
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
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