fluffynicesheep
Board Regular
- Joined
- Oct 27, 2009
- Messages
- 69
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:
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;
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
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:
Code:
=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;
VBA Code:
=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
VBA Code:
'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]