Macro Alter

[Sunline]

Hello all , i have this macro which cost me lots of $$$ to be made , it basically looks back at a horses last 3 starts and then copies this data to respective cols , excepting PL1 too PL30 (goes back 30) .
Im wanting to delete certain parts as they are of no use then obtain more info from another col . Quite possibly asking to much here .
Have tried changing it my self but keep failing . Im wanting to delete the following .
Dim intYea3 As Integer
Dim intYea2 As Integer
Dim intYea1 As Integer
Dim intMon3 As Integer
Dim intMon2 As Integer , not Dim intMon1 As Integer
Dim intDate3 As Integer
Dim intDate2 As Integer , not Dim intDate1 As Integer
Dim intWeather3 As Integer
Dim intWeather2 As Integer
Dim intWeather1 As Integer
Dim intRail3 As Integer
Dim intRail2 As Integer
Dim intRail1 As Integer
Dim intTime3 As Integer
Dim intTime2 As Integer
Dim intTime1 As Integer
Dim intFsz3 As Integer
Dim intFsz2 As Integer , not Dim intFsz1 As Integer
Dim intWgt3 As Integer
Dim intWgt2 As Integer
Dim intWgt1 As Integer
Dim intWgtc3 As Integer
Dim intWgtc2 As Integer , not Dim intWgtc1 As Integer
Dim intJock3 As Integer
Dim intJock2 As Integer , not Dim intJock1 As Integer
Dim intTrad3 As Integer
Dim intTrad2 As Integer
Dim intTrad1 As Integer
Dim intPtf3 As Integer
Dim intPtf2 As Integer
Dim intPtf1 As Integer
Dim intApp3 As Integer
Dim intApp2 As Integer , not Dim intApp1 As Integer

After eliminating the above i would like too look back the same as PL1 too PL30 but for these
Dim intMar3 As Integer
Dim intMar2 As Integer
Dim intMar1 As Integer , so i guessing there would be a Dim intMar30 As Integer , Macro as follows , Thanks ???? .

Sub SetPlacingMargin()
On Error GoTo X
'To get this to work I think the Whole spreadsheet needs to be put into an array
'I need to get the column no. for each column
'Then I can do my loop to pick up the correct column to add the prev starts
'need to change varArray to equal the whole spreadsheet
Dim varArray() As Variant
Dim rng As Range
Dim rngPlacing As Range
Dim rngDec As Range
Dim intPlacingCol As Integer
Dim intDecCol As Integer
Dim intcheck As Integer
Dim lngLastRow As Long
Dim strAddress As String
Dim intDiff As Integer
Dim lngArrayLoop As Long
Dim intYea3 As Integer
Dim intYea2 As Integer
Dim intYea1 As Integer
Dim intMon3 As Integer
Dim intMon2 As Integer
Dim intMon1 As Integer
Dim intMeet3 As Integer
Dim intMeet2 As Integer
Dim intMeet1 As Integer
Dim intDate3 As Integer
Dim intDate2 As Integer
Dim intDate1 As Integer
Dim intDay3 As Integer
Dim intDay2 As Integer
Dim intDay1 As Integer
Dim intCon As Integer
Dim intCon3 As Integer
Dim intCon2 As Integer
Dim intCon1 As Integer
Dim intConHash As Integer
Dim intConHash1 As Integer
Dim intConHash2 As Integer
Dim intConHash3 As Integer
Dim intWeather3 As Integer
Dim intWeather2 As Integer
Dim intWeather As Integer
Dim intWeather1 As Integer
Dim intRail As Integer
Dim intRail3 As Integer
Dim intRail2 As Integer
Dim intRail1 As Integer
Dim intTime As Integer
Dim intTime3 As Integer
Dim intTime2 As Integer
Dim intTime1 As Integer
Dim intClass As Integer
Dim intClass3 As Integer
Dim intClass2 As Integer
Dim intClass1 As Integer
Dim intDist As Integer
Dim intDist3 As Integer
Dim intDist2 As Integer
Dim intDist1 As Integer
Dim intStakes As Integer
Dim intStakes3 As Integer
Dim intStakes2 As Integer
Dim intStakes1 As Integer
Dim intFsz As Integer
Dim intFsz3 As Integer
Dim intFsz2 As Integer
Dim intFsz1 As Integer
Dim intPQ As Integer
Dim intPQ1 As Integer
Dim intPQ2 As Integer
Dim intPQ3 As Integer
Dim intPl As Integer
Dim intPl1 As Integer
Dim intPl2 As Integer
Dim intPl3 As Integer
Dim intPl4 As Integer
Dim intPl5 As Integer
Dim intPl6 As Integer
Dim intPl7 As Integer
Dim intPl8 As Integer
Dim intPl9 As Integer
Dim intPl10 As Integer
Dim intPl11 As Integer
Dim intPl12 As Integer
Dim intPl13 As Integer
Dim intPl14 As Integer
Dim intPl15 As Integer
Dim intPl16 As Integer
Dim intPl17 As Integer
Dim intPl18 As Integer
Dim intPl19 As Integer
Dim intPl20 As Integer
Dim intPl21 As Integer
Dim intPl22 As Integer
Dim intPl23 As Integer
Dim intPl24 As Integer
Dim intPl25 As Integer
Dim intPl26 As Integer
Dim intPl27 As Integer
Dim intPl28 As Integer
Dim intPl29 As Integer
Dim intPl30 As Integer
Dim intTab As Integer
Dim intTab3 As Integer
Dim intTab2 As Integer
Dim intTab1 As Integer
Dim intFav As Integer
Dim intFav3 As Integer
Dim intFav2 As Integer
Dim intFav1 As Integer
Dim intWgt As Integer
Dim intWgt3 As Integer
Dim intWgt2 As Integer
Dim intWgt1 As Integer
Dim intWgtc As Integer
Dim intWgtc3 As Integer
Dim intWgtc2 As Integer
Dim intWgtc1 As Integer
Dim intJock As Integer
Dim intJock3 As Integer
Dim intJock2 As Integer
Dim intJock1 As Integer
Dim intMar As Integer
Dim intMar3 As Integer
Dim intMar2 As Integer
Dim intMar1 As Integer
Dim intTrad As Integer
Dim intTrad3 As Integer
Dim intTrad2 As Integer
Dim intTrad1 As Integer
Dim intPtf As Integer
Dim intPtf3 As Integer
Dim intPtf2 As Integer
Dim intPtf1 As Integer
Dim intApp As Integer
Dim intApp3 As Integer
Dim intApp2 As Integer
Dim intApp1 As Integer
Dim intHorse As Integer
Dim lngAddLoop As Long
Dim intYea As Integer
Dim intMon As Integer
Dim intMeet As Integer
Dim intDate As Integer
Dim intDay As Integer




lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row


For Each rng In Range("1:1")
rng.Select
Select Case rng.Value
Case "Yea"
intYea = rng.Column
Case "PQ"
intPQ = rng.Column
Case "Mon"
intMon = rng.Column
Case "Meet"
intMeet = rng.Column
Case "Date"
intDate = rng.Column
Case "Day"
intDay = rng.Column
Case "Con"
intCon = rng.Column
Case "Con#"
intConHash = rng.Column
Case "Weather"
intWeather = rng.Column
Case "Rail"
intRail = rng.Column
Case "Time"
intTime = rng.Column
Case "Class"
intClass = rng.Column
Case "Dist"
intDist = rng.Column
Case "Stakes"
intStakes = rng.Column
Case "Fsz"
intFsz = rng.Column
Case "Placing"
intPl = rng.Column
Case "Rno"
intTab = rng.Column
Case "Rfav"
intFav = rng.Column
Case "Wgt"
intWgt = rng.Column
Case "Wgtc"
intWgtc = rng.Column
Case "Jockey"
intJock = rng.Column
Case "Trad"
intTrad = rng.Column
Case "Plctabfav"
intPtf = rng.Column
Case "App"
intApp = rng.Column
Case "Placing"
intPlacingCol = rng.Column
Case "Horse"
intHorse = rng.Column
Case "Dec"
intMar = rng.Column
Case "PQ1"
intPQ1 = rng.Column
Case "PQ2"
intPQ2 = rng.Column
Case "PQ3"
intPQ3 = rng.Column
Case "Yea3"
intYea3 = rng.Column
Case "Yea2"
intYea2 = rng.Column
Case "Yea1"
intYea1 = rng.Column
Case "Mon3"
intMon3 = rng.Column
Case "Mon2"
intMon2 = rng.Column
Case "Mon1"
intMon1 = rng.Column
Case "Meet3"
intMeet3 = rng.Column
Case "Meet2"
intMeet2 = rng.Column
Case "Meet1"
intMeet1 = rng.Column
Case "Date3"
intDate3 = rng.Column
Case "Date2"
intDate2 = rng.Column
Case "Date1"
intDate1 = rng.Column
Case "Day3"
intDay3 = rng.Column
Case "Day2"
intDay2 = rng.Column
Case "Day1"
intDay1 = rng.Column
Case "Con3"
intCon3 = rng.Column
Case "Con2"
intCon2 = rng.Column
Case "Con1"
intCon1 = rng.Column
Case "Con#3"
intConHash3 = rng.Column
Case "Con#2"
intConHash2 = rng.Column
Case "Con#1"
intConHash1 = rng.Column
Case "Weather3"
intWeather3 = rng.Column
Case "Weather2"
intWeather2 = rng.Column
Case "Weather1"
intWeather1 = rng.Column
Case "Rail3"
intRail3 = rng.Column
Case "Rail2"
intRail2 = rng.Column
Case "Rail1"
intRail1 = rng.Column
Case "Time3"
intTime3 = rng.Column
Case "Time2"
intTime2 = rng.Column
Case "Time1"
intTime1 = rng.Column
Case "Class3"
intClass3 = rng.Column
Case "Class2"
intClass2 = rng.Column
Case "Class1"
intClass1 = rng.Column
Case "Dist3"
intDist3 = rng.Column
Case "Dist2"
intDist2 = rng.Column
Case "Dist1"
intDist1 = rng.Column
Case "Stakes3"
intStakes3 = rng.Column
Case "Stakes2"
intStakes2 = rng.Column
Case "Stakes1"
intStakes1 = rng.Column
Case "Fsz3"
intFsz3 = rng.Column
Case "Fsz2"
intFsz2 = rng.Column
Case "Fsz1"
intFsz1 = rng.Column
Case "PL1"
intPl1 = rng.Column
Case "PL2"
intPl2 = rng.Column
Case "PL3"
intPl3 = rng.Column
Case "PL4"
intPl4 = rng.Column
Case "PL5"
intPl5 = rng.Column
Case "PL6"
intPl6 = rng.Column
Case "PL7"
intPl7 = rng.Column
Case "PL8"
intPl8 = rng.Column
Case "PL9"
intPl9 = rng.Column
Case "PL10"
intPl10 = rng.Column
Case "PL11"
intPl11 = rng.Column
Case "PL12"
intPl12 = rng.Column
Case "PL13"
intPl13 = rng.Column
Case "PL14"
intPl14 = rng.Column
Case "PL15"
intPl15 = rng.Column
Case "PL16"
intPl16 = rng.Column
Case "PL17"
intPl17 = rng.Column
Case "PL18"
intPl18 = rng.Column
Case "PL19"
intPl19 = rng.Column
Case "PL20"
intPl20 = rng.Column
Case "PL21"
intPl21 = rng.Column
Case "PL22"
intPl22 = rng.Column
Case "PL23"
intPl23 = rng.Column
Case "PL24"
intPl24 = rng.Column
Case "PL25"
intPl25 = rng.Column
Case "PL26"
intPl26 = rng.Column
Case "PL27"
intPl27 = rng.Column
Case "PL28"
intPl28 = rng.Column
Case "PL29"
intPl29 = rng.Column
Case "PL30"
intPl30 = rng.Column
Case "Tab3"
intTab3 = rng.Column
Case "Tab2"
intTab2 = rng.Column
Case "Tab1"
intTab1 = rng.Column
Case "Fav3"
intFav3 = rng.Column
Case "Fav2"
intFav2 = rng.Column
Case "Fav1"
intFav1 = rng.Column
Case "Wgt3"
intWgt3 = rng.Column
Case "Wgt2"
intWgt2 = rng.Column
Case "Wgt1"
intWgt1 = rng.Column
Case "Wgtc3"
intWgtc3 = rng.Column
Case "Wgtc2"
intWgtc2 = rng.Column
Case "Wgtc1"
intWgtc1 = rng.Column
Case "Jockey3"
intJock3 = rng.Column
Case "Jockey2"
intJock2 = rng.Column
Case "Jockey1"
intJock1 = rng.Column
Case "Marg3"
intMar3 = rng.Column
Case "Marg2"
intMar2 = rng.Column
Case "Marg1"
intMar1 = rng.Column
Case "Trad3"
intTrad3 = rng.Column
Case "Trad2"
intTrad2 = rng.Column
Case "Trad1"
intTrad1 = rng.Column
Case "Ptf3"
intPtf3 = rng.Column
Case "Ptf2"
intPtf2 = rng.Column
Case "Ptf1"
intPtf1 = rng.Column
Case "App3"
intApp3 = rng.Column
Case "App2"
intApp2 = rng.Column
Case "App1"
intApp1 = rng.Column
Exit For
End Select
Next

Set rng = Range(Cells(2, 1), Cells(lngLastRow, intApp1))
rng.Select
varArray = rng.Value


For lngArrayLoop = 1 To UBound(varArray, 1)
If varArray(lngArrayLoop, intPl) = 1 Or varArray(lngArrayLoop, intPl) = "1=" Then
For intcheck = 1 To 10 'need to check if there is a 2= next then will need to pick up result from 3 or 3= I think??
If varArray(lngArrayLoop + intcheck, intPl) <> 1 And varArray(lngArrayLoop + intcheck, intPl) <> "1=" Then
varArray(lngArrayLoop, intMar) = varArray(lngArrayLoop + intcheck, intMar)
Exit For
End If
Next
End If
intcheck = 31
If lngArrayLoop <> 1 Then
For lngAddLoop = (lngArrayLoop - 1) To 1 Step -1
'NEED TO FIX THIS
If varArray(lngAddLoop, intHorse) = varArray(lngArrayLoop, intHorse) Then
intcheck = intcheck - 1
If intcheck <> 0 Then
Select Case intcheck
Case 30 'PL1 This will be the first last start actually
varArray(lngArrayLoop, intYea1) = varArray(lngAddLoop, intYea)
varArray(lngArrayLoop, intPQ1) = varArray(lngAddLoop, intPQ)
varArray(lngArrayLoop, intMon1) = varArray(lngAddLoop, intMon)
varArray(lngArrayLoop, intMeet1) = varArray(lngAddLoop, intMeet)
varArray(lngArrayLoop, intDate1) = varArray(lngAddLoop, intDate)
varArray(lngArrayLoop, intDay1) = varArray(lngAddLoop, intDay)
varArray(lngArrayLoop, intCon1) = varArray(lngAddLoop, intCon)
varArray(lngArrayLoop, intConHash1) = varArray(lngAddLoop, intConHash)
varArray(lngArrayLoop, intWeather1) = varArray(lngAddLoop, intWeather)
varArray(lngArrayLoop, intRail1) = varArray(lngAddLoop, intRail)
varArray(lngArrayLoop, intTime1) = varArray(lngAddLoop, intTime)
varArray(lngArrayLoop, intClass1) = varArray(lngAddLoop, intClass)
varArray(lngArrayLoop, intDist1) = varArray(lngAddLoop, intDist)
varArray(lngArrayLoop, intStakes1) = varArray(lngAddLoop, intStakes)
varArray(lngArrayLoop, intFsz1) = varArray(lngAddLoop, intFsz)
varArray(lngArrayLoop, intPl1) = varArray(lngAddLoop, intPl)
varArray(lngArrayLoop, intTab1) = varArray(lngAddLoop, intTab)
varArray(lngArrayLoop, intFav1) = varArray(lngAddLoop, intFav)
varArray(lngArrayLoop, intWgt1) = varArray(lngAddLoop, intWgt)
varArray(lngArrayLoop, intWgtc1) = varArray(lngAddLoop, intWgtc)
varArray(lngArrayLoop, intJock1) = varArray(lngAddLoop, intJock)
varArray(lngArrayLoop, intMar1) = varArray(lngAddLoop, intMar)
varArray(lngArrayLoop, intTrad1) = varArray(lngAddLoop, intTrad)
varArray(lngArrayLoop, intPtf1) = varArray(lngAddLoop, intPtf)
varArray(lngArrayLoop, intApp1) = varArray(lngAddLoop, intApp)
Case 29
varArray(lngArrayLoop, intYea2) = varArray(lngAddLoop, intYea)
varArray(lngArrayLoop, intPQ2) = varArray(lngAddLoop, intPQ)
varArray(lngArrayLoop, intMon2) = varArray(lngAddLoop, intMon)
varArray(lngArrayLoop, intMeet2) = varArray(lngAddLoop, intMeet)
varArray(lngArrayLoop, intDate2) = varArray(lngAddLoop, intDate)
varArray(lngArrayLoop, intDay2) = varArray(lngAddLoop, intDay)
varArray(lngArrayLoop, intCon2) = varArray(lngAddLoop, intCon)
varArray(lngArrayLoop, intConHash2) = varArray(lngAddLoop, intConHash)
varArray(lngArrayLoop, intWeather2) = varArray(lngAddLoop, intWeather)
varArray(lngArrayLoop, intRail2) = varArray(lngAddLoop, intRail)
varArray(lngArrayLoop, intTime2) = varArray(lngAddLoop, intTime)
varArray(lngArrayLoop, intClass2) = varArray(lngAddLoop, intClass)
varArray(lngArrayLoop, intDist2) = varArray(lngAddLoop, intDist)
varArray(lngArrayLoop, intStakes2) = varArray(lngAddLoop, intStakes)
varArray(lngArrayLoop, intFsz2) = varArray(lngAddLoop, intFsz)
varArray(lngArrayLoop, intPl2) = varArray(lngAddLoop, intPl)
varArray(lngArrayLoop, intTab2) = varArray(lngAddLoop, intTab)
varArray(lngArrayLoop, intFav2) = varArray(lngAddLoop, intFav)
varArray(lngArrayLoop, intWgt2) = varArray(lngAddLoop, intWgt)
varArray(lngArrayLoop, intWgtc2) = varArray(lngAddLoop, intWgtc)
varArray(lngArrayLoop, intJock2) = varArray(lngAddLoop, intJock)
varArray(lngArrayLoop, intMar2) = varArray(lngAddLoop, intMar)
varArray(lngArrayLoop, intTrad2) = varArray(lngAddLoop, intTrad)
varArray(lngArrayLoop, intPtf2) = varArray(lngAddLoop, intPtf)
varArray(lngArrayLoop, intApp2) = varArray(lngAddLoop, intApp)
Case 28 'PL3 This will be for the 3rd last start actually when intcheck has counted down to 1
varArray(lngArrayLoop, intYea3) = varArray(lngAddLoop, intYea)
varArray(lngArrayLoop, intPQ3) = varArray(lngAddLoop, intPQ)
varArray(lngArrayLoop, intMon3) = varArray(lngAddLoop, intMon)
varArray(lngArrayLoop, intMeet3) = varArray(lngAddLoop, intMeet)
varArray(lngArrayLoop, intDate3) = varArray(lngAddLoop, intDate)
varArray(lngArrayLoop, intDay3) = varArray(lngAddLoop, intDay)
varArray(lngArrayLoop, intCon3) = varArray(lngAddLoop, intCon)
varArray(lngArrayLoop, intConHash3) = varArray(lngAddLoop, intConHash)
varArray(lngArrayLoop, intWeather3) = varArray(lngAddLoop, intWeather)
varArray(lngArrayLoop, intRail3) = varArray(lngAddLoop, intRail)
varArray(lngArrayLoop, intTime3) = varArray(lngAddLoop, intTime)
varArray(lngArrayLoop, intClass3) = varArray(lngAddLoop, intClass)
varArray(lngArrayLoop, intDist3) = varArray(lngAddLoop, intDist)
varArray(lngArrayLoop, intStakes3) = varArray(lngAddLoop, intStakes)
varArray(lngArrayLoop, intFsz3) = varArray(lngAddLoop, intFsz)
varArray(lngArrayLoop, intPl3) = varArray(lngAddLoop, intPl)
varArray(lngArrayLoop, intTab3) = varArray(lngAddLoop, intTab)
varArray(lngArrayLoop, intFav3) = varArray(lngAddLoop, intFav)
varArray(lngArrayLoop, intWgt3) = varArray(lngAddLoop, intWgt)
varArray(lngArrayLoop, intWgtc3) = varArray(lngAddLoop, intWgtc)
varArray(lngArrayLoop, intJock3) = varArray(lngAddLoop, intJock)
varArray(lngArrayLoop, intMar3) = varArray(lngAddLoop, intMar)
varArray(lngArrayLoop, intTrad3) = varArray(lngAddLoop, intTrad)
varArray(lngArrayLoop, intPtf3) = varArray(lngAddLoop, intPtf)
varArray(lngArrayLoop, intApp3) = varArray(lngAddLoop, intApp)
Case 27 'PL4
varArray(lngArrayLoop, intPl4) = varArray(lngAddLoop, intPl)
Case 26 'PL5
varArray(lngArrayLoop, intPl5) = varArray(lngAddLoop, intPl)
Case 25 'PL6
varArray(lngArrayLoop, intPl6) = varArray(lngAddLoop, intPl)
Case 24 'PL7
varArray(lngArrayLoop, intPl7) = varArray(lngAddLoop, intPl)
Case 23 'PL8
varArray(lngArrayLoop, intPl8) = varArray(lngAddLoop, intPl)
Case 22 'PL9
varArray(lngArrayLoop, intPl9) = varArray(lngAddLoop, intPl)
Case 21 'PL10
varArray(lngArrayLoop, intPl10) = varArray(lngAddLoop, intPl)
Case 20 'PL11
varArray(lngArrayLoop, intPl11) = varArray(lngAddLoop, intPl)
Case 19 'PL12
varArray(lngArrayLoop, intPl12) = varArray(lngAddLoop, intPl)
Case 18 'PL13
varArray(lngArrayLoop, intPl13) = varArray(lngAddLoop, intPl)
Case 17 'PL14
varArray(lngArrayLoop, intPl14) = varArray(lngAddLoop, intPl)
Case 16 'PL15
varArray(lngArrayLoop, intPl15) = varArray(lngAddLoop, intPl)
Case 15 'PL16
varArray(lngArrayLoop, intPl16) = varArray(lngAddLoop, intPl)
Case 14 'PL17
varArray(lngArrayLoop, intPl17) = varArray(lngAddLoop, intPl)
Case 13 'PL18
varArray(lngArrayLoop, intPl18) = varArray(lngAddLoop, intPl)
Case 12 'PL19
varArray(lngArrayLoop, intPl19) = varArray(lngAddLoop, intPl)
Case 11 'PL20
varArray(lngArrayLoop, intPl20) = varArray(lngAddLoop, intPl)
Case 10 'PL21
varArray(lngArrayLoop, intPl21) = varArray(lngAddLoop, intPl)
Case 9 'PL22
varArray(lngArrayLoop, intPl22) = varArray(lngAddLoop, intPl)
Case 8 'PL23
varArray(lngArrayLoop, intPl23) = varArray(lngAddLoop, intPl)
Case 7 'PL24
varArray(lngArrayLoop, intPl24) = varArray(lngAddLoop, intPl)
Case 6 'PL25
varArray(lngArrayLoop, intPl25) = varArray(lngAddLoop, intPl)
Case 5 'PL26
varArray(lngArrayLoop, intPl26) = varArray(lngAddLoop, intPl)
Case 4 'PL27
varArray(lngArrayLoop, intPl27) = varArray(lngAddLoop, intPl)
Case 3 'PL28
varArray(lngArrayLoop, intPl28) = varArray(lngAddLoop, intPl)
Case 2 'PL29
varArray(lngArrayLoop, intPl29) = varArray(lngAddLoop, intPl)
Case 1 'PL20
varArray(lngArrayLoop, intPl30) = varArray(lngAddLoop, intPl)
End Select
Else
Exit For
End If
End If
Next
End If
Next

rng.Value = varArray


MsgBox "All done"

Z:
On Error Resume Next
Exit Sub
X:
MsgBox "Error ocurred, no. " & Err.Number & vbNewLine & vbNewLine & "Description: " & Err.Description _
, vbCritical, "Code Error"
Resume Z
End Sub