.Find function

[rgiglio7489]

Hi- I am struggling a bit with the find function. I currently have a summary sheet called "Comments" and I have a data sheet called "Data". In my comments sheet, I am showing the following 7 columns (I have an empty column in between each):
1. Company name (column B)
2. Type of business (column D)
3. Date (column F)
4. Type of business code (column H)
5. Clientel (column J)
6. Revenue (column L)
7. Comments (column N)

These 7 columns are being populated from the Data tab manually. the Data tab has about 25 columns of data, and I want my VBA to:
1. look in column 21, which is a column full of formulas, and find every cell with a "yes"
2. in that same row, grab the information needed above, and place it into the Comments tab.

The VBA below gets me halfway there... If there are 15 cells in column 21 with the value of "yes", then there should be 15 rows in the Comments tab. I'm having a hard time finding the "yes" in column 2, then placing it in Comments tab, then going to the next "yes" value in column 21 and placing in the next row of the Comments tab. Very hard to follow- I'm sorry. Here is what I have so far:

Please Login or Register  to view this content.

[mehmetcik]

If you are just copying "yes" to the comments column why can you not use a formula in the comments column?

[alansidman]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html

Please read the forum rules and abide by them in the future



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[rgiglio7489]

I'm looking for anything that says "yes" in column 21, and I need to return values in other columns based on multiple criteria. Trust me, I would much rather use a formula (like a vlookup or an INDEX), but i think VBA would work smoother.

[rgiglio7489]

sorry. i will do that going forward

[mongoose36]

Perhaps...

Please Login or Register  to view this content.

[mehmetcik]

Ok

This is the fast way to do what you want.

This finds Yes in Column 3 and puts a Yes in Column 5.

Please Login or Register  to view this content.

[rgiglio7489]

This hit the nail on the head when it comes to what I'm trying to do, thank you... on small problem... lets say there are 3 "yes" values in column 21:

row 20, row 40 and row 60 have a "yes" value in column 21... the vba is counting the # of "yes" values correctly, and its returning 3 comments correctly to the comments sheet... however its not taking the values from the offset in rows 20/40/60... rather its taking the values from the offset in rows 1/2/3... i'm not sure what the issue is, because everything below seems to be correct below.

Does this make sense?

Perhaps...

Please Login or Register  to view this content.

[mongoose36]

I'm not sure what would be causing this behavior. It works fine in my test (see attached).

http://www.excelforum.com/attachment...1&d=1460474277

[rgiglio7489]

very interesting... I just tried yours and it works in your spreadsheet. the only difference between mine and yours is that all of my data is in a table, and some of the values are formulas (but thats covered with LookIn:=xlFormulas). any ideas? I'm still playing around with it... does excel version matter?

I'm not sure what would be causing this behavior. It works fine in my test (see attached).

http://www.excelforum.com/attachment...1&d=1460474277

[jolivanes]

No need counting or looping, is there?
Change all sheet/cell references as required.
Your explanation lacks information for me so can't be more specific.

Please Login or Register  to view this content.

[mongoose36]

very interesting... I just tried yours and it works in your spreadsheet. the only difference between mine and yours is that all of my data is in a table, and some of the values are formulas (but thats covered with LookIn:=xlFormulas). any ideas? I'm still playing around with it... does excel version matter?

The addition of a table to my test did not alter the result. There must be something else going on. Perhaps if you could upload the workbook you are working with.

@jolivanes I agree there are better ways to achieve the desired result, however what you have suggested is not quite what @rgiglio7489 is trying to do. Below is your code with some alterations to do what @rgiglio7489 wants.

Please Login or Register  to view this content.

[rgiglio7489]

See attached... Its taking the 3 "Yes" values, and applying it in column 20 for the first 3 rows... not sure whats going on. Might be a setting? I also had no issue with your spreadsheet when I created a table.

[mongoose36]

I think the code below will deliver the results you are looking for...

Please Login or Register  to view this content.

[rgiglio7489]

I haven't tested your new suggestion, but i found a workaround:

Please Login or Register  to view this content.