Smart Macro

[rz6657]

EXCEL 2003

Rows = 165

Goal: Run a macro that will 'check' if a cell is empty. If so, copy previous cell into next column. Column / rows are static, data is dynamic.

For example (I will have a separate macro for each corresponding month):

MACRO 1
Month is February. If M5 is blank, copy data from L5 and past in S5.

MACRO 2
Month is March. If T5 is blank, copy data from S5 and past in Z5.

ETC...

If the cell is not empty, ignore.

If there is a better way to do this, please recommend. Thanks.

[Trebor76]

Hi,

Test the following code, and once your happy it works, copy it 11 times (one for each remaining month) and change the macro name and the starting cell (i.e. for March change Range("M5").Select to Range("T5").Select) for each one.

Sub February()

Application.ScreenUpdating = False

Dim intCounter As Integer
Dim strCellAddress As String

Range("M5").Select

Do Until intCounter = 165
    If IsEmpty(ActiveCell) = True Then
    strCellAddress = ActiveCell.Address(False, False)
    ActiveCell.Offset(0, -1).Select
    Selection.Copy
    ActiveCell.Offset(0, 7).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range(strCellAddress).Select
    ActiveCell.Offset(1, 0).Select
    Else
    ActiveCell.Offset(1, 0).Select
    End If

intCounter = intCounter + 1

Loop

Application.ScreenUpdating = True

End Sub

Hope this helps.

Kind regards,

Robert

[rz6657]

I forgot to mention that the M5 cell has a formula which pulls data from a table. Your code did not work because of this. I did test your code, however, on an empty cell with no formula and it WORKS!

Is there a way to where the macro can ignore the formula? The cell it's looking to see if it's empty pulls a dynamic date. Thanks in advance...

[rz6657]

If it makes a difference, we could also use cell P5. Either an "H" or "S" is expected here, otherwise, it's considered 'empty' (formula in this one too). Than it could copy L5 to S5 if no "H" or "S". Thanks.

[Trebor76]

Hi,

Replace

If IsEmpty(ActiveCell) = True Then

with

If IsEmpty(ActiveCell) = True Or ActiveCell.HasFormula Then

The body of the code will then run if a cell is either blank or has a formula in it (I think this is what your asking for).

I hope it solves the issue.

Kind regards,

Robert

[rz6657]

Actually, column M from rows 1 - 165 has an embedded formula. In some cases, the formulas will extract data (in this particular column / cell combination, it extracts a date via VLOOKUP.

The column L, from which the macro will copy from, has numerical entries in all 165 rows (which are entered manually). On a per month basis, the fomulas will extract and update the date at the most 30 of the 165 in column M.

My goal is to copy those numerical entries which column L does not have a date updated. Save me time.

Since the cells in column L are not empty (they have formulas), the original code did not work. With this new line entry you provided, it copies all those which have formulas. Thanks.