Macro to scan a column and return a value

[orlex]

I am new to all of this and have not gotten to the point where I can figure out how to even begin to do this. I have searched quite a bit on many websites to try and find a problem similar to where I can modify the code, but I have not been able to. I can make visual/cosmetic changes in a macro, but not something like this. I desperately need help with it.

I have a spreadsheet that that has columns from A to AE, but the amount of rows is variable every time this report is run. I need a macro that will scan or search all of the cells of column G for any date (dates and blank cells is all that will be in the column) and if a Date is found, run the formula G3-$P$1 (the G3 depends on what row the formula will be run in) in the corresponding cell in the same row in column Q. If a cell is blank in Column G, then I need it to return the value that will be in the the same row in Column P, but in Column Q. All of the other columns have values, but they are independent from these. For a visual example, I have created something below. Any help would be greatly appreciated. Thanks!

3/27/2015

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1/1/2015

7

=G3-$P$1

Blank, returns value of P3 in Q3

12

12

[Arkadi]

orlex,

Which column will have data for every row? can we use A? Also, the Q column calculation, do you want the formula in the cell? or the value that results from the formula?

[pjwhitfield]

Please Login or Register  to view this content.

[Arkadi]

another approach:

Please Login or Register  to view this content.

[orlex]

Thank you both. PJ, yours was close, but slightly off. Arkadi, yours worked perfectly! Thank you both again!

[pjwhitfield]

Out of interest what was mine doing wrong?