S
ste mac
Hi There.. I was hoping some clever being could help me out...
I am quite new to VBA and the code below works (shocked and stunned)
its just that it takes a long time to run as the workbook is quite
large..
is there any way to make it quicker?
I know I could stop it updating in the statusbar but its nice to know
where the process is upto....thanks in advance...
ste
Public Sub controldata()
Dim b1 As Variant
Dim b2 As Variant
Dim b3 As Variant
Dim b4 As Variant
Dim b5 As Variant
Dim xlrow As Long
Application.StatusBar = False
Application.ScreenUpdating = False
For sheetnumber = 1 To 56
SheetName = "S" & Format(sheetnumber, "##0")
Sheets(SheetName).Select
ActiveSheet.Cells(3, 36).Select
xlrow = 3
Do While Not (ActiveSheet.Cells(xlrow, 1).Value = "")
b1 = ActiveSheet.Cells(xlrow, 3).Value
b2 = ActiveSheet.Cells(xlrow, 4).Value
b3 = ActiveSheet.Cells(xlrow, 5).Value
b4 = ActiveSheet.Cells(xlrow, 6).Value
b5 = ActiveSheet.Cells(xlrow, 7).Value
ActiveCell = b1 & ("&") & b2
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b1 & ("&") & b3
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b1 & ("&") & b4
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b1 & ("&") & b5
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b2 & ("&") & b3
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b2 & ("&") & b4
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b2 & ("&") & b5
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b3 & ("&") & b4
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b3 & ("&") & b5
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b4 & ("&") & b5
ActiveCell = ActiveCell.Offset(1, -9).Select
'ActiveCell = ActiveCell.Offset(1, -23).Select
xlrow = xlrow + 1
Application.StatusBar = "System Status: " & Format(xlrow /
ActiveSheet.Cells(1, 2).Value, "00%") & SheetName & " of Data is
completed ..."
Loop
ActiveCell.Value = ""
Application.StatusBar = False
Next
Application.ScreenUpdating = True
End Sub
I am quite new to VBA and the code below works (shocked and stunned)
its just that it takes a long time to run as the workbook is quite
large..
is there any way to make it quicker?
I know I could stop it updating in the statusbar but its nice to know
where the process is upto....thanks in advance...
ste
Public Sub controldata()
Dim b1 As Variant
Dim b2 As Variant
Dim b3 As Variant
Dim b4 As Variant
Dim b5 As Variant
Dim xlrow As Long
Application.StatusBar = False
Application.ScreenUpdating = False
For sheetnumber = 1 To 56
SheetName = "S" & Format(sheetnumber, "##0")
Sheets(SheetName).Select
ActiveSheet.Cells(3, 36).Select
xlrow = 3
Do While Not (ActiveSheet.Cells(xlrow, 1).Value = "")
b1 = ActiveSheet.Cells(xlrow, 3).Value
b2 = ActiveSheet.Cells(xlrow, 4).Value
b3 = ActiveSheet.Cells(xlrow, 5).Value
b4 = ActiveSheet.Cells(xlrow, 6).Value
b5 = ActiveSheet.Cells(xlrow, 7).Value
ActiveCell = b1 & ("&") & b2
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b1 & ("&") & b3
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b1 & ("&") & b4
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b1 & ("&") & b5
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b2 & ("&") & b3
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b2 & ("&") & b4
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b2 & ("&") & b5
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b3 & ("&") & b4
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b3 & ("&") & b5
ActiveCell = ActiveCell.Offset(0, 1).Select
ActiveCell = b4 & ("&") & b5
ActiveCell = ActiveCell.Offset(1, -9).Select
'ActiveCell = ActiveCell.Offset(1, -23).Select
xlrow = xlrow + 1
Application.StatusBar = "System Status: " & Format(xlrow /
ActiveSheet.Cells(1, 2).Value, "00%") & SheetName & " of Data is
completed ..."
Loop
ActiveCell.Value = ""
Application.StatusBar = False
Next
Application.ScreenUpdating = True
End Sub