SkipBlanks Questions

[fluffynicesheep]

Hi,

I currently have a macro that looks at a line of data in one workbook and inserts this into the next available row in a 2nd workbook. It does this by looking at:

1) If there is a formula in the cell directly above the line of data that is being inserted into workbook 2, it automatically drags the formula down to the cell underneath in the new row , and
2) if the cell above does not contain formula, then it will directly insert the data from workbook 1 into the empty cell.

All works great, until I have added a formula into one of the cells in workbook 2, whereby I want the cell to go blank automatically if another cell has a "0)" in it ....so the cell in question now looks like:

=IF(ISNUMBER(SEARCH("0)",A23)),"",VLOOKUP(RR23,Table17[#All],5,FALSE))

At the moment, the VBA below works fine if cell A23 doesn't contain a "0)" .... it just pulls downs the formula into the new row ....... so the new row would now look like;

=IF(ISNUMBER(SEARCH("0)",A24)),"",VLOOKUP(RR24,Table17[#All],5,FALSE))

However, if cell A23 does contain a "0)" and the cell has gone blank, then the VBA seems to think there is nothing in the cell above, so copies a blank cell (without formula), in the cell below.

I wonder if someone could therefore have a look at the VBA below and let me know how to change this, so it doesn't think the cell above is blank if it contains formula in....

Thanks

'Paste starting at the last empty row
wb.Worksheets("a_Main").Range("A" & LastCellRowNumber).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=True, Transpose:=False
wb.Worksheets("a_Main").Range("A" & LastCellRowNumber).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
    :=True, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True

'Check for formulas and drag down
   For i = 1 To 520
        If Cells(LastCell.Row, i).HasFormula Then _
           Range(Cells(LastCell.Row, i), Cells(LastCellRowNumber, i)).FillDown
    Next

    With Range(Cells(LastCell.Row, 1), Cells(LastCell.Row, 520))
        .Copy
        .Offset(1).PasteSpecial Paste:=xlPasteFormats
    End With

    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With

[ICODE][/ICODE]

 

[fluffynicesheep]

I'm not sure if it's the formula in the cell that needs changing (e.g. the "") or if it's something to do with skipblanks section of the VBA?