Formula Help For Macro.

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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,

What happens?

--

HTH

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

What happens?
Click to expand...

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: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
 
Back
Top