VBA - criteria lookup and the removing formula from cell [Closed]

Report
Posts
23
Registration date
Monday October 15, 2012
Status
Member
Last seen
August 18, 2016
-
Posts
23
Registration date
Monday October 15, 2012
Status
Member
Last seen
August 18, 2016
-
Hi,

Could someone help... I have written some code but cant get it to do exactly what I want, and think I have over complicated the problem.

Cell J6 - has a trigger either 1 or 0
Cell H6 has formula =If(J6=1,I6,"") - 'this takes a date from I6'

I need to remove the formula from cell H6 and replace with the value.

Then go on to the next row, I also want it to ignore blank cells and leave the formula there.

Can anyone help.

Many Thanks


Worksheet ("Milestones")

3 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
select H6. copy(control+c).
without removing selection cllick "pastevealues"

quote
Then go on to the next row, I also want it to ignore blank cells and leave the formula there.

quote

this is not clear

do you mena to say that when j of a row i1 then corresponding cell in H column should be values not formulas. if 0 keep the formula. clairfy
Posts
23
Registration date
Monday October 15, 2012
Status
Member
Last seen
August 18, 2016
2
Hi, Thanks for your message,

In Column I has an "if" statement, that is looking for a date in column J (which is looking up data from another sheet)

Column H ... "if" statement that is looking if that date is less than or equal to todays date.

All I am trying to do is remove the formula in column H and leave the value so that when the date changes in column I it does not change in column H

If no date in column H then I want the formula to stay
I need to loop through all the rows in the sheet.


Sorry I hope I have explained it better

Debs
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
better way of explaining to send a small extract of your data through speedyshare.com and explain again
Posts
23
Registration date
Monday October 15, 2012
Status
Member
Last seen
August 18, 2016
2
Thanks, I have managed to write something that doing what I want .. probably a better way but it is working

Thanks for your help... next will attach extract