Macro which searches for cell with text "Total Cost" and references cell 3 to the right

Hi,

Thanks again for all of your help.

Please help me create a macro that does the following. I will keep it in my personal macro workbook as I will need it across workbooks.

Selects sheet "Summary" as the Active Sheet
Selects cell D15 on the worksheet entitled "Summary"
Search for the cell with the text "Total Cost" on sheet "Dec" and selects the cell exactly 3 cells to the right of that cell. (if cell with text "Total Cost" is Dec!B27, the cell I need is Dec!E27)
Display the contents of the Dec!E27 in D15 of sheet "Summary"

It would be a simple reference to Dec!E27, but the cell I want will not ALWAYS be in E27, and this will be for different documents every time, which is why I want it to search for it.

Hi SF1eagles, try this:

Please Login or Register  to view this content.

No prob, that makes sense, however, I am still receiving the subscript error (I had edited my post, you may have missed it)

Hi,

This is what I have right now - working well, except that I want your code to CHECK whether there is any COST in that cell, and then set D15 to the found value. This is because I am pasting data into the "Dec" sheet and so your code prematurely writes "no data" before I paste. I need the sequence to be:

1) Duplicate "NOV" sheet
2) Rename copy "DEC"
3) Set C2 in "DEC" sheet to equal sheet name
4) Clear "DEC" data between row 3 where Placement/Impressions/Clicks Recorded/Media Cost sits and the row where Total Cost sits (the latter is variable, as each Nov sheet I copy has a different number of rows, this clear is tricky)
5) Once cleared, immediately assign a absolute formula in D15 on "Summary" to display the value (0,3) from "Total Cost" on "DEC"

This may mean that the "found" function you created might not work. Thanks for the help! I have a project to complete, so I can't dig in to the code as much as I'd like to myself. I hate just giving directions, but appreciate the help!

Please Login or Register  to view this content.