Search for input criteria, return designated cells.Solved/Closed

Question

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

vcoolio · Answer

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.

vcoolio · Answer

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.

crlarus · Answer

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

vcoolio · Answer

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.

vcoolio · Answer

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.

crlarus · Answer

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

vcoolio · Answer

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.

crlarus · Answer

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

vcoolio · Answer

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.

Search for input criteria, return designated cells.

9 responses

Similar discussions

Subscribe To Our Newsletter!