VBA - Find a string, move X amount of spaces from that cell.

[crayhons]

Hi all,

I couldn't think of a great title here to really explain what I need to do, but I feel like a picture will make it easy to understand.

Using the picture, this is what I want to do and my explanation for why.

misc demo.JPG

1) Find the string "Miscellaneous Demos" - the values above this area are not static and change almost every time by either 1-3 cells up or down. Using Miscellaneous Demos as a reference point will help find the cells I need.

2) From Miscellaneous Demos, I need to get the cell numbers of:

• Age 40+ AND Salary $40,000+ (%)
• Income $30,000 to $100,000 (%)
• Income over $75,000 (%)
• Age Range 35-54 (%)
• Age Range 35-50 (%)
• Average Salary
• Average Age

These cell numbers are part of a formula that calculates specific values from the input page and with the page being dynamic, I need a way to make it so that no matter where these cells are, it can find them.

3) If anyone finds a way to do this, the next part would be to then add it to the VBA as part of the code. Currently, the VBA inserts the formulas that we use to calculate the output. For instance,

Please Login or Register  to view this content.

in this case, the H67 would be the static location for one of the values, but since it's not static, how would I go about adding the code formulated in part two to the VBA?

I understand this is probably confusing, so please ask away.
Thanks!

[ragulduy]

Many many methods to do this, here is one:

Please Login or Register  to view this content.

[crayhons]

Update: I figured out a simple way to get the cells, now I just need a way to put this into the formula aforementioned.

Is there a way I could get the selected cell inserted into the formula?

Thanks.

[crayhons]

Many many methods to do this, here is one:

Please Login or Register  to view this content.

Didn't work. I should mention the Age_40 are being taken from that sheet and copied to another. I get an error when I try doing what was suggested.

Please Login or Register  to view this content.

The code I listed is what it needs to be doing adding C41 from the Initial Demographics + C40 from the initial Demographics + age_35_to_50 cell.

[ragulduy]

Sorry, I made an error copying it out. The ' and the + the wrong way round after C40 and the ' and ! the wrong way before age_35. Note, you don't need to separate C40 from initial demographics, you can write:

Please Login or Register  to view this content.

[crayhons]

Sorry, I made an error copying it out. The ' and the + the wrong way round after C40 and the ' and ! the wrong way before age_35. Note, you don't need to separate C40 from initial demographics, you can write:

Please Login or Register  to view this content.

Thanks, that did it!

Appreciate the help.