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
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