PDA

View Full Version : cleaning up sheet/cell references



AleemAM123
12-03-2008, 06:28 AM
This is the first piece of code I've written by myself (and i'm quite proud of it :cloud9: ). I realise though that there are pieces that could have been written better, can anyone point anything?

I figure there is a better way to reference the cells (e.g. critloc) instead of how I did it can someone show me how?

Sub ALM2()
'
' AutoALM Macro
' Macro recorded 11/26/2008 by
'
'
Dim Criteria As String

With Worksheets("Aleem")

Dim nextrow As Long
Dim critloc As Long
Dim result As Long
Dim sampdat As Long
Dim rowcount As Long
Dim lowout As Long
Dim highout As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With


Range("J1").Select
Range(Selection, Selection.End(xlDown)).Select

rowcount = Selection.Count - 1

'this is where the stream names are
critloc = 266

'put the result heading here
result = 15

'put the sample data under the corresponding result heading
sampdat = 271

'lower outliers
lowout = 268

'higher outliers
highout = 269

Do While rowcount > 0



Criteria = Cells.Item(critloc)

Cells.Item(result) = Criteria

Selection.AutoFilter Field:=3, Criteria1:=Criteria

Selection.AutoFilter Field:=8, Criteria1:=">0.691", Operator:=xlAnd, _
Criteria2:="<1.5403758"

Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Cells.Item(sampdat).Select
ActiveSheet.Paste


critloc = critloc + 256
highout = highout + 256
lowout = lowout + 256
result = result + 1
sampdat = sampdat + 1
rowcount = rowcount - 1

Loop




End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=8

End Sub

Bob Phillips
12-03-2008, 07:16 AM
What is it doing?