Finding data

[swqa1234]

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

[Aussiebear]

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

[swqa1234]

thx for your help

[Aussiebear]

Any chance you can post a workbook with dummy data? We seriously need to see what you are looking at.

[p45cal]

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.

[swqa1234]

test file with dummy data - code not inserted however

[swqa1234]

done that thx

[Aussiebear]

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.

[swqa1234]

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

[Aussiebear]

Okay no problem then.

[swqa1234]

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

[p45cal]

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.

[swqa1234]

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 ! :bow: