how to make my excel vba more efficient 1

[smooveb]

Hi everyone,

I'm a relative newbie, and am interested in making my excel code more efficient. Just a couple questions.

1. is there a really good newbie reference (free or for purchase) for excel vba? I'll paste an example of my code below, so you can know the general level I'm at right now. I have Power Programming for Excel 2000, but don't find it's a great reference.

2. Instead of all my do while and if-thens, what are some other simple options?

3. How can I free up memory after my code runs? My desktop slows down if I run my macros repeatedly without restarting excel.

4. what's the command so that everything that is going on in the background does not need to be shown? I know I activate a lot of pages and the user sees everything as a result.

Here is an example of the code I'm writing. Thanks for any assistance!

Sub supply()
' reorganizes and aggregates supply
' supply raw data brought in from QueryDemand sub
Dim strSupply As String
Dim strSupply2 As String
strSupply = "Supply"
strSupply2 = "Supply2"
Dim supply As Worksheet
Dim supply2 As Worksheet


Set supply = Worksheets(strSupply)
Set supply2 = Worksheets(strSupply2)

Dim strLot1 As String
Dim strLot2 As String


supply.Activate
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A4").Select

' perform top level formatting
With supply2
.Activate
.Select
.Cells.Select
Selection.ClearContents

Selection.Font.Bold = False

Selection.Interior.ColorIndex = xlNone
.Cells(1, 1).Value = "Item"
.Cells(1, 2).Value = "Lot"
.Cells(1, 3).Value = "Ready Date"
.Cells(1, 4).Value = "Expire Date"
.Cells(1, 5).Value = "Qty"
.Cells(1, 6).Value = "Status"
.Cells(1, 7).Value = "Notes"
End With

Dim i
Dim j
Dim lag
Dim rdate
Dim lngQty As Long

i = 2
j = 2

strLot1 = supply.Cells(i, 3).Value


Do While supply.Cells(i, 1) <> ""
supply2.Cells(j, 1) = supply.Cells(i, 1)

supply2.Cells(j, 2) = supply.Cells(i, 3)
strLot2 = supply2.Cells(j, 2)
' need to add lag to produced month
supply2.Cells(j, 3) = supply.Cells(i, 4)
rdate = supply2.Cells(j, 3).Value
lag = supply.Cells(i, 7).Value
supply2.Cells(j, 4).Value = DateAdd("m", lag, rdate)
supply2.Cells(j, 3).Value = supply2.Cells(j, 4).Value
'supply2.Cells(1, Col).Value = DateSerial(Year(rdate), Month(rdate) + 1, 0) + 1
supply2.Cells(j, 4) = supply.Cells(i, 5)
supply2.Cells(j, 5) = supply.Cells(i, 6)
If supply.Cells(i, 8) = "75" Then
supply2.Cells(j, 6) = "PLANNED"
Else
supply2.Cells(j, 6) = "PRODUCED"
End If
strQty = supply.Cells(i, 6).Value
' second loop to determine whether item has changed
i = i + 1
strLot1 = supply.Cells(i, 3).Value
Do While strLot1 = strLot2
strQty = strQty + supply.Cells(i, 6).Value




i = i + 1
strLot1 = supply.Cells(i, 3).Value
Loop
supply2.Cells(j, 5) = strQty
' going to get lot details from last lot here
supply2.Cells(j, 7) = supply.Cells(i - 1, 9)
j = j + 1
strQty = 0

Loop

End Sub

 

[SkipVought]

Hi,

Check out faq707-4105.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}