Could this code be faster?
 

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

Could this code be faster?
 

Hi Ste

Definitely. Two things:

1 Don't select cells. Change

ActiveCell.Offset(0, 1).Select
ActiveCell = b4 & ("&") & b5

to

ActiveCell.Offset(0, 1).Value = b4 & ("&") & b5

or even better

Cells(xlrow, columnnumber).Value = b4 & ("&") & b5

2) When you enter into cells, Excel recalculates. How long time that takes
depends on how many formulas you have. Put
Application.Calculation = xlCalculationManual
in the beginning (up with screenupdating), and
Application.Calculation = xlCalculationAutomatic
at the end.

Yes, keep the statusbar. It seems longer if absolutely nothing is seen, even
when it's actually faster. Anything 2 seconds should show progress or
"working" indication.

--
HTH. Best wishes Harald
Excel MVP
Followup to newsgroup only please

"ste mac" skrev i melding
om...
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

Could this code be faster?
 

Thanks Harald...that was a great help...now it goes like a rocket..
cheers....