Help with vba if statement

A

antthorne

Board Regular
Joined
Feb 18, 2010
Messages
151
Hi

Need some help again!!!

I have the following Macro and it doesn't seem to be working correctly (probably something to do with the person creating it!!!!)

It seems to get past the if statement and when b1 does not equal 0 i want it to preform the else action but it seems to skip this.

Can anyone help

Thanks

Ant

Calculate
Application.ScreenUpdating = False
Sheets("Avonmouth").Select
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.NumberFormat = "dd/mm/yy;@"
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:AC").Select
Selection.EntireColumn.Hidden = True
Columns("AE:AE").Select
Selection.NumberFormat = "dd/mm/yy;@"
If Range("b1") = 0 Then
Range("a1").Select
Else: Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
Sheets("Glasgow").Select
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.NumberFormat = "dd/mm/yy;@"
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:AC").Select
Selection.EntireColumn.Hidden = True
Columns("AE:AE").Select
Selection.NumberFormat = "dd/mm/yy;@"
If Range("b1") = 0 Then
Range("a1").Select
Else: Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
Sheets("Marston Gate (Network)").Select
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.NumberFormat = "dd/mm/yy;@"
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:AC").Select
Selection.EntireColumn.Hidden = True
Columns("AE:AE").Select
Selection.NumberFormat = "dd/mm/yy;@"
If Range("b1") = 0 Then
Range("a1").Select
Else: Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
Sheets("Leeds").Select
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.NumberFormat = "dd/mm/yy;@"
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:AC").Select
Selection.EntireColumn.Hidden = True
Columns("AE:AE").Select
Selection.NumberFormat = "dd/mm/yy;@"
If Range("b1") = 0 Then
Range("a1").Select
Else: Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
Sheets("Rainham").Select
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.NumberFormat = "dd/mm/yy;@"
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:AC").Select
Selection.EntireColumn.Hidden = True
Columns("AE:AE").Select
Selection.NumberFormat = "dd/mm/yy;@"
If Range("b1") = 0 Then
Range("a1").Select
Else: Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
Sheets("Wythenshawe").Select
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.NumberFormat = "dd/mm/yy;@"
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:AC").Select
Selection.EntireColumn.Hidden = True
Columns("AE:AE").Select
Selection.NumberFormat = "dd/mm/yy;@"
If Range("b1") = 0 Then
Range("a1").Select
Else: Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
Sheets("102").Select
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.NumberFormat = "dd/mm/yy;@"
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:AC").Select
Selection.EntireColumn.Hidden = True
Columns("AE:AE").Select
Selection.NumberFormat = "dd/mm/yy;@"
If Range("b1") = 0 Then
Range("a1").Select
Else: Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
Sheets("Cuscol").Select
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.NumberFormat = "dd/mm/yy;@"
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:AC").Select
Selection.EntireColumn.Hidden = True
Columns("AE:AE").Select
Selection.NumberFormat = "dd/mm/yy;@"
If Range("b1") = 0 Then
Range("a1").Select
Else: Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
Sheets("Marston Gate (Fleet)").Select
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.NumberFormat = "dd/mm/yy;@"
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:AC").Select
Selection.EntireColumn.Hidden = True
Columns("AE:AE").Select
Selection.NumberFormat = "dd/mm/yy;@"
If Range("b1") = 0 Then
Range("a1").Select
Else: Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Hi are you wanting the if statement to work when 0 or when the cell is blank. If it is the cell is blank then just change to:

Code: 
If Range("b1") = "" Then

Also you are repeating code as you go through sheets. If you are using this against all sheets then you can loop like this:

Code: 
Sub Test()
Dim Current As Worksheet

Calculate
Application.ScreenUpdating = False
' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets
Current.Select
'Code between here
 Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.NumberFormat = "dd/mm/yy;@"
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:AC").Select
Selection.EntireColumn.Hidden = True
Columns("AE:AE").Select
Selection.NumberFormat = "dd/mm/yy;@"
If Range("b1") = "" Then
Range("a1").Select
Else: Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
       
Next

Application.ScreenUpdating = True
End Sub

It should make it easier to maintain.
 
Upvote 0
You must log in or register to reply here.

Similar threads

tourless
  • Solved
Borders without selection
Replies
7
Views
450
Peter_SSs
Peter_SSs
Jimk229
  • Question
vba create boxes loop
Replies
2
Views
219
Jimk229
Jimk229
T
  • Question
Set bottom border of page to print
Replies
0
Views
323
tiredofit
T
T
  • Question
Set border for page break
Replies
2
Views
347
tiredofit
T
M
  • Question
VBA Excel - Change range automatically
Replies
1
Views
292
Mozzie_Kh
M

Forum statistics

Threads
1,203,128
Messages
6,053,674
Members
444,678
Latest member
Kenny_D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top