VBA/formula help

[RattlingCarp3048]

I have this spreadsheet that just keeps on circling back with problems, hopefully you guys can help once again....

It is updated with new data multiple times throughout the day so the last row is ever changing. the data is in A-Q with formulas going into R-X. Placement of the formulas in V-X is the issue.

When the spreadsheet is updated i need to find the next available row in column V-X and place the formulas. How can i find the next available row in V-X?

if I use lastrow = Cells(Rows.Count, "V").End(xlUp).Offset (1).Row is shows the next row = 9001 but its not, in this update the next cell in column V-X is 8993

 

[JGordon11]

I suspect V9000 is not empty. Maybe it contains a formula that returns "" or has an invisible character.

Try msgbox len([V9000].formula)

if you get a non-zero, it's not empty

 

[RattlingCarp3048]

I suspect V9000 is not empty. Maybe it contains a formula that returns "" or has an invisible character.

Try msgbox len([V9000].formula)

if you get a non-zero, it's not empty

correct, there was a formula in V-X 9000 that resulted in "" but i copy/pasted special values to remove the formula and leave the cells blank. Ive tried multiple approaches to this and everytime i think its solved i catch an error i didnt see before.

I can leave the formulas the cells, thats not an issue at all. The ultimate goal is when the new data is added in A-Q then find the last row in R-X and fill the formulas down to the last row. thats it. Any ideas how i can do that?

 

[RattlingCarp3048]

I suspect V9000 is not empty. Maybe it contains a formula that returns "" or has an invisible character.

Try msgbox len([V9000].formula)

if you get a non-zero, it's not empty

kind of an example of the code i have at the moment

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("V2:X" & lastRow).SpecialCells(xlCellTypeBlanks).Formula = Range("V2:X2").Formula

when it pulls the formulas down its supposed to copy the formula from V2-X2 and fill down to all the blank cells. Problem is in V9000 the formula should be vlookup(U9000,'Yesterday's Report' U:V,2,0) but instead it is putting vlookup(U3,'Yesterday's Report' U:V,2,0)

 

[RattlingCarp3048]

bump

 

[JGordon11]

Try using .FormulaR1C1 instead

 

[RattlingCarp3048]

Try using .FormulaR1C1 instead

instead of what?

 

[JGordon11]

Range("V2:X" & lastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = Range("V2:X2").FormulaR1C1

 

[RattlingCarp3048]

Range("V2:X" & lastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = Range("V2:X2").FormulaR1C1

Hi JGordon, sorry for the delayed reply. We've been testing out your suggestion over the past week as well as attempting to modify for solutions but are still getting similar errors as before. Initially the Vlookup was starting the count over at U2 when pulling the formulas down. now with the updated code you provided the count is skipping rows when pulling the formulas down, it appears to be at an exponential rate. Any thoughts? Its definitely closer than we had before but still no true fix.

 

[RattlingCarp3048]

'Fill down formlas in Column V-X
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("V2:Y" & lastrow).SpecialCells(xlCellTypeBlanks).Formula = Range("V2:Y2").Formula

then this...
'Fill down formlas in Column V-X
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("V2:Y" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = Range("V2:Y2").FormulaR1C1

in both cases i get a lot of #N/A's when the formula populates which there shouldnt be any at all.... AND the rows are not pulling down sequentially in the formula AND sometimes the formulas will over-write data despite the SpecialCells(xlCellTypeBlanks) being coded. the N/A's and over-writing seem to be at random and inconsistent. i have to re-run the macro 2-3 times before it stops, and sometimes i just end up fixing the issues manually.