Search for input criteria, return designated cells.

Solved/Closed
crlarus Posts 7 Registration date Friday August 11, 2017 Status Member Last seen August 18, 2017 - Updated on Aug 18, 2017 at 09:32 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 19, 2017 at 04:09 AM
Hey all,

I am trying to write a VBA command/macro that essentially takes a criteria input from the destination worksheet, searches and matches the criteria on an index worksheet, and then returns data located in the same row but specific to locked columns not adjacent to each other.

It is essentially a Vlookup, but I want to be able to copy and paste formulas that will use the data and simply line it up with the destination cells for the aforementioned macro. A Vlookup would ultimately suffice, but the index worksheet is quite large and will be continually updated; I am trying to remove the need to adjust the range and apply it to the entire worksheet indefinitely.

I hope that request does not seem too verbose or vague. If anything, offering some direction regarding what potential VBA templates may apply would be a phenomenal start. Unfortunately, everything I have looked up thus far seems to not be able to return the values to the destination in such a specific manner.

Thanks so much

9 responses

vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 12, 2017 at 08:02 AM
Hello Crlarus,

We'll more than likely need to see a sample of your work book in order to be able to help you out. Please upload a sample (please use dummy data) to a free file sharing site such as GE.TT or Drop Box and then post the link to your file back here. Please also show us what code you have tried to use.

I'm assuming that you would like to select a criteria from, perhaps, a drop down or a search box or an input box in the destination worksheet, find this criteria in a designated column in the index sheet and then transfer the relevant rows of data back to the destination sheet. The data will be sourced from non-contiguous columns. Is this correct?

Following is the link to an example I did for another Poster over a year ago. See if this is at least close to what you are wanting to do:-

http://ge.tt/3zVY29m2

On sheet1, there is a short description of what the code does.

Let us know your thoughts.

Cheerio,
vcoolio.
1
crlarus Posts 7 Registration date Friday August 11, 2017 Status Member Last seen August 18, 2017
Aug 14, 2017 at 10:13 AM
Hey Vccoolio,

Firstly, thank you for your time and response. To answer your clarifying questions, the data will be sourced from non-contiguous columns and too many account numbers to legitimize a drop down select option (full set is about 19,000 Accounts and a total of 660,000 metric reads with corresponding dates). I have not explored the idea of a search bar, but for now, simply an input cell will suffice (I assume should an input not match with anything on the index sheet it would return nothing thus confirming human error at that point?).

Regarding the code I have been working with, I cannot figure where to start. My experience with VBA only provides insight towards its potential; I cannot find a suitable example to even attempt trial and error to any real constructive progress. Find loops and Copy and Paste loops can work, but trying to return the data to a page that has formulas is where I run into issues. A link to a set of dummy data with deliberate redundancies (not to be removed) and deliberate blank cells is below; I hope that is enough data for a sufficient example.

http://ge.tt/96aKAAm2

I will review the example you sent in the meantime.

Thanks so much,

crlarus
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 15, 2017 at 07:37 AM
Hello Crlarus,

Apparently your last reply was trapped by our spam robot because it picked up a couple of words which are regularly used in spam.

However, I've sorted that out and will have a good look at your sample tomorrow.

Just briefly. We could use cell B8 as the input/search box using the account numbers as the identifiers if you like. Using the account numbers would be the safest bet. Yes, if nothing is returned then either there isn't any data to search for or, as you say, human error has buggered things up for you.

I am also assuming that you would like Columns C:G in the Destination sheet populated with data from Columns A, D:E, G and L from the PlaceHolderSample sheet. The remaining columns(H:S) will be left for the formulae to do their job.

I'll be in touch before long.

Cheerio,
vcoolio.
1
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 16, 2017 at 03:59 AM
Hello Crlarus,

Ha. Roberta our robot is a little like an old Head Mistress I had the pleasure(?) of knowing back in my school days. Too literal!

Anyway, I think that the following code may help you out:-

Sub FindDetails()

        Dim lr As Long
        Dim ws As Worksheet
        Dim ws1 As Worksheet
        Dim ASearch As String
Set ws = Sheet1
Set ws1 = Sheet2

AccSearch = ws1.[B8].Value

Application.ScreenUpdating = False

ws.Range("F1", ws.Range("F" & ws.Rows.Count).End(xlUp)).AutoFilter 1, AccSearch
     lr = ws.Range("E" & Rows.Count).End(xlUp).Row
          If lr > 1 Then
               Union(ws.Range("A2:A" & lr), ws.Range("D2:E" & lr), ws.Range("G2:G" & lr), ws.Range("L2:L" & lr)).Copy
                     ws1.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
               End If
ws.[F1].AutoFilter

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


Enter an account number in cell B8 in the destination sheet and the code will filter the source sheet for that account number and then transfer all the rows of data associated with the account number to the destination sheet but only the data from columns A, D:E, G and L will be transferred to columns C:G in the destination sheet.

Following is the link to your work book with the code implemented:-

http://ge.tt/4GWNSBm2

Click on the "Account Search" button to see it work.

I hope that this helps.

Cheerio,
vcoolio.
1
crlarus Posts 7 Registration date Friday August 11, 2017 Status Member Last seen August 18, 2017
Aug 16, 2017 at 11:07 AM
Hey Vcoolio,

This is phenomenal. I will apply it to the actual sheets and see how it runs against the full set of data, but I expect I should not run into any issues. I cannot express my gratitude enough; more importantly, I hope I can learn from it for future endeavors as well.

Thanks so much and best regards,

crlarus
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Updated on Aug 17, 2017 at 04:50 AM
Hello Crlarus,

You're welcome. Glad that I was able to help.

There's one thing that I overlooked. I didn't ask if you wanted Columns C:G in the destination sheet cleared once you were finished with the data of a particular account and before the next lot of data from the next account is transferred across. As the code stands now, it will continue to append new data to the bottom of the previous each time that you click on the button.

This can be arranged if you like.

Cheerio,
vcoolio.
1
crlarus Posts 7 Registration date Friday August 11, 2017 Status Member Last seen August 18, 2017
Aug 17, 2017 at 08:39 AM
Hey Vcoolio,

That suggestion would make it cleaner and again would be appreciated. I was just deleting the pulls and relocating them as needed. What would be the addendum to the macro string?

Thanks,

crlarus
0

Didn't find the answer you are looking for?

Ask a question
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 18, 2017 at 06:10 AM
Hello Crlarus,

You weren't that far off actually.

I've taken it a little further and worked it so that you can use both codes for the same purpose. So, if you only have a meter number to work off, then using that number, you can still transfer all the required data just like the first code would do using the account number.

So, the two (similar) codes are:-

Sub FindDetails()

        Dim lr As Long
        Dim lr1 As Long
        Dim ws As Worksheet
        Dim ws1 As Worksheet
        Dim AccSearch As String
Set ws = Sheet1
Set ws1 = Sheet2
lr1 = ws1.Range("H" & Rows.Count).End(xlUp).Row
AccSearch = ws1.[B8].Value

Application.ScreenUpdating = False

ws1.Range("C9:G" & lr1).ClearContents

ws.Range("F1", ws.Range("F" & ws.Rows.Count).End(xlUp)).AutoFilter 1, AccSearch
     lr = ws.Range("E" & Rows.Count).End(xlUp).Row
          If lr > 1 Then
               Union(ws.Range("A2:A" & lr), ws.Range("D2:E" & lr), ws.Range("G2:G" & lr), ws.Range("L2:L" & lr)).Copy
                     ws1.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
               End If
ws.[F1].AutoFilter

ws1.[J3:J4].ClearContents
ws1.[B8].Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub



and



Sub FindDetails2()

        Dim lr As Long
        Dim lr1 As Long
        Dim ws As Worksheet
        Dim ws1 As Worksheet
        Dim MeterSearch As String
Set ws = Sheet1
Set ws1 = Sheet2
lr1 = ws1.Range("H" & Rows.Count).End(xlUp).Row
MeterSearch = ws1.[J3].Value

Application.ScreenUpdating = False

ws1.Range("C9:G" & lr1).ClearContents

ws.Range("G1", ws.Range("G" & ws.Rows.Count).End(xlUp)).AutoFilter 1, MeterSearch
     lr = ws.Range("E" & Rows.Count).End(xlUp).Row
          If lr > 1 Then
               Union(ws.Range("A2:A" & lr), ws.Range("D2:E" & lr), ws.Range("G2:G" & lr), ws.Range("L2:L" & lr)).Copy
                     ws1.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
                         ws.Range("F2:F" & lr).Copy ws1.[XFD1]
                             ws1.[J4] = ws1.[XFD1]
                                 ws1.[B8] = ws1.[XFD1]
                            End If
          ws.[G1].AutoFilter
          
ws1.[J4].Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


The code "FindDetails" is the same one as previous but now also clears the data from the destination sheet before new data is transferred across.

You should be able to pick up the minor additions.

The code "FindDetails2" is similar but searches for the meter number entered into J3 instead and then transfers the relevant data to the destination sheet as well. The relevant account number (if found) appears in J4 as well as appearing in B8.

BTW, don't use the same subroutine name for more than one code in the same project as an error will be thrown up.

Here is the link to the updated version:-

http://ge.tt/7orqzCm2

Play around with it and see if it works for you.

Test it in a copy of your work book first.

I hope that this helps.

Cheerio,
vcoolio.
1
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 19, 2017 at 04:09 AM
Hello Crlarus,

Excellent! I'm glad that both solutions worked for you and, again, I'm happy to have been able to help.

No, there aren't feedback forms that can be filled in. There is just a "Helpful" box at the top left of each post which you can add a plus or a minus to.

Anyway, you being thankful and appreciative for any help is enough for me as would any Volunteer here at CCM appreciate a simple "thank you" for any help offered.

I hope that your work burden has now been reduced somewhat!

Take care.

Cheerio,
vcoolio.
1
crlarus Posts 7 Registration date Friday August 11, 2017 Status Member Last seen August 18, 2017
Aug 15, 2017 at 10:26 AM
Hey Vcoolio,

I can't imagine what words I used actually relegated my comment to spam (haha). I have been trying to tailor the previous example to my needs but to no avail. Regarding the task at hand, everything you mentioned seems exactly what I am trying to accomplish. The account numbers would be the identifier, and the respective headings between index to destination would be populated with the corresponding data. I will reiterate that I very much appreciate your time and effort. I look forward to hearing and learning from your input here shortly.

Kindest regards,

crlarus
0
crlarus Posts 7 Registration date Friday August 11, 2017 Status Member Last seen August 18, 2017
Updated on Aug 18, 2017 at 04:40 AM
Hey Vcoolio,

There may be a potential for a second module with the same data. I am attempting to include a reverse account number look-up should a potential input only be a meter number. I have been trying to manipulate the successful macro provided but realize I must need a different sub-routine altogether. So, the string would Find the specified value, search the index for the first appearance of the value, and copy and paste the first iteration of the account number.

So far for the second module with no success, the adaptation from the first module is as follows:

Sub FindDetails()

Dim lr As Long
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim ASearch As String
Set ws = Sheet1
Set ws1 = Sheet2

MeterSearch = ws1.[J3].Value

Application.ScreenUpdating = False

ws.Range("G1", ws.Range("G" & ws.Rows.Count).End(xlUp)).AutoFilter 1, AccSearch
lr = ws.Range("E" & Rows.Count).End(xlUp).Row
If lr > 1 Then
ws.Range("G2:G").Copy
ws1.Range("J4").End(3)(2).PasteSpecial xlPasteValues
End If
ws.[F1].AutoFilter

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Obviously, it is not working. Below is a link with the formatted changes and attempt for Module 2 shown above.



Thanks so much,

crlarus
0
crlarus Posts 7 Registration date Friday August 11, 2017 Status Member Last seen August 18, 2017
Aug 18, 2017 at 10:19 AM
Hey Vcoolio,

This was a phenomenal help, and I hope I can learn from all that you have provided (you definitely made it seem so much more attainable). I have applied and troubleshot the new modules to the complete data set with no errors. I cannot express enough gratitude for your time since this is going to expedite a significant amount of leg work in our project for the coming months.

Should there be some sort of feedback report please let me know where I can fill one out to provide a stellar review. It was a pleasure conversing with you here.

Very much thanks and deepest regards,

crlarus
0