Formula Help For Macro.

[John]

Hi.

I'm not too great with formulas so I need a little help.

What I'm trying to do within a Macro in Excel is to have an IF
statement so that if there's nothing in any cell in column 12 for row
3 and below, the same cells in column 13 and 14 are deleted of any
contents.

I have no idea how to do this one. I usually look in the help section
of Excel, however I've just got this new system and reloaded
everything on, but I can't remember where I put the Office 2000 disk
and it keeps asking me for it to load the help back up

Anyway, thank you very much for any info on this one.

John

 

[Bob Phillips]

John,

Try this

Dim cLastRow As Long
Dim i As Long

For i = 3 To Cells(Rows.Count,12).End(xlUp).Row
If IsEmpty(Cells(i, 12).Value) Then
Cells(i,13).Value = ""
Cells(i,14).Value = ""
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[John]

Unfortunately it didn't seem to work that one.

John

 

[Bob Phillips]

John,

What happens?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[John]

John,

What happens?

Hi. Nothing seems to happen on that one.

This is a copy of the complete macro below with the added bit at the
bottom.


Sub SuperFormat()
'
' SuperFormat Macro
' Macro recorded 21/01/04 by John
'
' Keyboard Shortcut: Ctrl+k
'
Columns("A:A").ColumnWidth = 10.71
Columns("B:B").ColumnWidth = 12
Columns("C:C").ColumnWidth = 11.43
Columns("F:F").ColumnWidth = 18.57
Columns("G:G").ColumnWidth = 12.86
Columns("I:I").ColumnWidth = 11.29
Columns("L:L").ColumnWidth = 11.86
ActiveWindow.SmallScroll ToRight:=7
ActiveWindow.LargeScroll ToRight:=-1
Columns("F:F").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = True
.MergeCells = False
End With
Selection.ColumnWidth = 14.14
Range("B3:B6238").Select
With Selection.Font
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Columns("B:B").ColumnWidth = 16
Columns("B:B").ColumnWidth = 19.43
Range("C3:C6894").Select
With Selection.Font
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Columns("C:C").ColumnWidth = 14.71
Columns("C:C").ColumnWidth = 18.71
Columns("C:C").ColumnWidth = 20
Range("C5").Select
Columns("D").ColumnWidth = 12
ActiveWindow.SmallScroll ToRight:=3
Range("H3:H7965").Select
With Selection.Font
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Columns("H:H").ColumnWidth = 11.71
Range("J3:J7352").Select
With Selection.Font
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Columns("J:J").ColumnWidth = 12.14
Range("I7").Select
ActiveWindow.LargeScroll ToRight:=-1

Range("L3:L9303").Select
Selection.NumberFormat = "General"

Columns(2).Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Columns(2).Replace What:="/", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Columns(2).Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Columns(2).Replace What:="'", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Columns(12).Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

Range("L3:L9309").Select
Selection.NumberFormat = "0.00"
Range("F16").Select

Dim cLastRow As Long
Dim i As Long

For i = 3 To Cells(Rows.Count, 12).End(xlUp).Row
If IsEmpty(Cells(i, 12).Value) Then
Cells(i, 13).Value = ""
Cells(i, 14).Value = ""
End If
Next i


End Sub



Thanks for your help

John