VBA find multiple instances of value in range, return value in col for each row
Hi guys
I have a code I found some time back that will highlight all the cells in a range that contain a value.
The range of data will have multiple instances of the value. What I would like to do is return the value of a certain col for each row that the value is found in the range.
So basically, if the value is found in row 4, 6, 24, 55... I need the value of Col D, row 4 of sheet 1 to be put into sheet 2 col B, row 2 (first empty cell in col B). Then repeat this for all other rows where the value is found and put the values from Sheet 1 onto sheet 2 in the next empty cell in col B.
Re: VBA find multiple instances of value in range, return value in col for each row
Welcome to the forum
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Re: VBA find multiple instances of value in range, return value in col for each row
You did not present a mocked up copy of your expected results. I tried to correlate your explanation in Post 1 with your workbook in Post 3. Hopefully, I have done it correctly. Please advise any changes you require.
Re: VBA find multiple instances of value in range, return value in col for each row
Hi Alan
It is working for the most part... I have attached the actual file.
What I am trying to get right is the following:
In the Details sheet, I want to list everything that I am looking for in the DATA sheet in Row 1, this will be multiple entries in row 1.
I then need to look for the value in Row 1 Col B in the DATA sheet and return the value in col F of the same row.
So, essentially for each col containing a value in the details sheet, I need to find all the values in the DATA sheet and return the value in col F for each row the value is found.
I could tweak it to work in the simple example, but when I try to use it with the actual data it does not work.
Re: VBA find multiple instances of value in range, return value in col for each row
I have no idea what you are saying. It makes no sense to me. Suggest you use a real example explaining criteria, where it is found and what you want it to do. Generalities do not compute. I need to understand specifics. Where are we looking and what are we looking for. Once found, what needs to happen. Show a mocked up solution of several 4-6 records.
but when I try to use it with the actual data it does not work.
As your first attachment does not reflect your actual data layout, it weird to practice like this
or you are very confident with your Excel / VBA skills to amend the code to fit your real workbook !
If the last attachment has at least some results filled with a better explanation like any forum expects
in the initial post in order any helper does not have to guess as here this is not a mind readers forum …
A beginner starter demonstration to paste to the Sheet4(Details) worksheet module :
PHP Code:
Sub Demo1() Dim C&, R&, Rg As Range, A$ Me.UsedRange.Offset(1, 1).Clear Application.ScreenUpdating = False With Sheet1.UsedRange For C = 2 To Me.UsedRange.Columns.Count R = 1 Set Rg = .Columns("CU:IV").Find(Cells(C).Value2 & "*", , xlValues, xlWhole, xlByRows) If Not Rg Is Nothing Then A = Rg.Address Do R = R + 1 Cells(R, C).Value2 = .Cells(Rg.Row, 6).Value2 Set Rg = .Columns("CU:IV").FindNext(Rg) Loop Until Rg.Address = A End If Next End With Application.ScreenUpdating = True Set Rg = Nothing End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 11-03-2019 at 07:29 PM.
Reason: optimization …
Re: VBA find multiple instances of value in range, return value in col for each row
Hey Experts
Have not had a chance to go through everything just yet, crazy day.
Marc, I still need to test your code...
protonLeah, tried your quick and it seems to do everything that I need it to do.
I will work on both tomorrow, there are still a few things I need to add in. So far it looks like with you guys helping I will be able to get to my end goal.
Thanks a ton for the help thus far... off to a good start.
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by Stigmata
Bookmarks