Hi

A basic stocks spreasheet made of 3 worksheets: All(sheet1),
In(Sheet2), and Out(Sheet3).
I need to write a macro which copies/adds the contents of cells from
Worsheet 1, into Worksheet 2 and 3, according to wether the goods move
in or out, for any given date.

I am looking for ways to make the code faster(and maybe shorter) as the
spreadsheet is very very long. I have read about using ranges or arrays,
or using shorthand like -Set rng = Worksheets(1).[a1]-, but somehow I
can't make it work.


Here is what I could come up with so far(sorry about the length):


Sub FlashyMacro()

Screen Update = False
Dim Row1 As Double, Row2 As Double, Row3 As Double, SheetOnScreen As
String
Application.Calculation = xlManual

Row1 = 5
Row2 = 2
Row3 = 2

While Worksheets("Sheet1").Cells(Row1, 1) < ""
If Worksheets("Sheet1").Cells(Row1, 1) = "In" Then
If Worksheets("Sheet2").Cells(Row2, 1) = "" Then
Worksheets("Sheet2").Cells(Row2, 1) =
Worksheets("Sheet1").Cells(Row1, 2)
[...]
Worksheets("Sheet2").Cells(Row2, 5) =
Worksheets("Sheet1").Cells(Row1, 8)
Else
Worksheets("Sheet2").Cells(Row2, 2) =
Worksheets("Sheet2").Cells(Row2, 2) + Worksheets("Sheet1").Cells(Row1,
5)
[...]
End If
End If
If Worksheets("Sheet1").Cells(Row1, 1) = "Out" Then
' Same for "out"
End If
Row1 = Row1 + 1
If Worksheets("Sheet1").Cells(Row1, 1) = "In" And
Worksheets("Sheet2").Cells(Row2, 1) < Worksheets("Sheet1").Cells(Row1,
2) Then
Row2 = Row2 + 1
If (SheetOnScreen = "Sheet2") Then
Worksheets("Sheet2").Cells(Row2 + 1, 1).Select
End If
Worksheets("Sheet2").Cells(Row2, 1) = ""
[...]
Worksheets("Sheet2").Cells(Row2, 5) = ""
End If
' Same for "OUT"
Wend

Calculate
Application.Calculation = xlAutomatic

Worksheets(SheetOnScreen).Activate

End Sub

Thanks


--
HoogaBooger
------------------------------------------------------------------------
HoogaBooger's Profile: http://www.excelforum.com/member.php...o&userid=34084
View this thread: http://www.excelforum.com/showthread...hreadid=538479