Why must the starting cell be above the range for this to work?

Hi

Try

Please Login or Register  to view this content.

I've changed the variable from cell to ce as cell is a reserved word in excel - saves confusion.

HTH

rylo

Originally Posted by rylo

Hi

Try

Please Login or Register  to view this content.

I've changed the variable from cell to ce as cell is a reserved word in excel - saves confusion.

HTH

rylo

hi Rylo,
How do I alter the copy destination line to copy the exact formula of the above cell? I am still encountering some blanks after running this code. It doesn't fill in all blanks within my range.


Thanks in advance,
Ricky

Originally Posted by ExcelQuestion

Hello,
This code looks for each blank cell and copies the exact formula located right above it. But if the activecell is located within the range, then it doesn't work or sometimes, it partially copies the above formula then continues on leaving some other cells blank. Why must I have the starting line of beginning at cell A2 for this to work?

Please Login or Register  to view this content.

By the way, if the first line was to be Range("A1").Select, it will fail. Don't know why.

Thanks in advance,
Ricky

When you refer to any cell within a for each loop, the referenced cell does not become active cell. A cell becomes active only if you select it or you have a code cell.Activate. Thus through the whole loop the active cell remains A2 which you have selected at the begining. Therefore if you select A1 then offset (-1,0) refers to a nonexistant cell hence the error.

Cell(ActiveCell.Offset(-1,0)) is a wrong syntax. Though it does not give error it may give unpredictable result. Correct syntax is ActiveCell.Offset(-1,0).Formula. Also since cell is a reserved word let us use ce ( though in your case it does not affect ). Selection of any cell at the begining is really not required. Your sub should be

Please Login or Register  to view this content.

This should copy "exact" formula the way you want. I assume that by exact you mean if formula is A3= B3 + C3 then when it gets copies to the cell below it should be same and not become A4 = B4 + C4 etc.

If some of the cells remain blank then perhaps they are not empty but may have a space character which appears to be empty but is not. If you want cells with space characters also to copy formula from the cell above then you need to modify the code accordingly.

A V Veerkar

Originally Posted by avveerkar

When you refer to any cell within a for each loop, the referenced cell does not become active cell. A cell becomes active only if you select it or you have a code cell.Activate. Thus through the whole loop the active cell remains A2 which you have selected at the begining. Therefore if you select A1 then offset (-1,0) refers to a nonexistant cell hence the error.

Cell(ActiveCell.Offset(-1,0)) is a wrong syntax. Though it does not give error it may give unpredictable result. Correct syntax is ActiveCell.Offset(-1,0).Formula. Also since cell is a reserved word let us use ce ( though in your case it does not affect ). Selection of any cell at the begining is really not required. Your sub should be

Please Login or Register  to view this content.

This should copy "exact" formula the way you want. I assume that by exact you mean if formula is A3= B3 + C3 then when it gets copies to the cell below it should be same and not become A4 = B4 + C4 etc.

If some of the cells remain blank then perhaps they are not empty but may have a space character which appears to be empty but is not. If you want cells with space characters also to copy formula from the cell above then you need to modify the code accordingly.

A V Veerkar

Thank-you A V Veerkar,
Your explanation makes complete sense. I understand now. The reason that I had encountered some blank cells after running the macro was because I had a group of cells highlighted. This was before I added Range("A2").Select to the first line.

Thanks again,
Ricky

Ricky

If you want to match the exact formula then

Please Login or Register  to view this content.

rylo

Originally Posted by rylo

Ricky

If you want to match the exact formula then

Please Login or Register  to view this content.

rylo

Thanks Rylo.

Much appreciated.

Ricky