alienscript
02-29-2008, 04:39 PM
Hello VBA Guru,
I used Find to store the column numbers in variables, then used the variables in cells(i, "columnNumber").value and loop from row2 ro lastrow. It did not populate any in column "Remark". Does anyone knows what was missing in my procedure?
I attach my test file so that it will be easier to see.
Sub test()
Dim i As Long, lastrow As Long, loc As Byte, m As Byte, ttsl As Byte, sga As Byte
Dim sgm As Byte, sgt As Byte, tpmvs As Byte, obm As Byte, ipp As Byte, remk As Byte
Dim Location, OptMin As Integer, TotalTSL As Integer, SGAnnualDmd As Long, SGMOU As Byte
Dim sgTKMMOU As Byte, ObsoletePartMOU As Byte, TPM_VitalSpares As String, Remark As String
Dim IPP_FinalMin As Integer, SGLastSixMonths As Long
Rows("1:1").Cells.Find(What:="location", After:=[A1]).Select
loc = ActiveCell.Column
Rows("1:1").Cells.Find(What:="opt*Min", After:=[A1]).Select
m = ActiveCell.Column
Rows("1:1").Cells.Find(What:="Total*TSL*", After:=[A1]).Select
ttsl = ActiveCell.Column
Rows("1:1").Cells.Find(What:="SG*Annual*", After:=[A1]).Select
sga = ActiveCell.Column
Rows("1:1").Cells.Find(What:="SG*MOU", After:=[A1]).Select
sgm = ActiveCell.Column
Rows("1:1").Cells.Find(What:="8800*TKM*MOU", After:=[A1]).Select
sgt = ActiveCell.Column
Rows("1:1").Cells.Find(What:="TPM*VIT*CE*", After:=[A1]).Select
tpmvs = ActiveCell.Column
Rows("1:1").Cells.Find(What:="ipp*min*", After:=[A1]).Select
ipp = ActiveCell.Column
Rows("1:1").Cells.Find(What:="obsolete*mou*", After:=[A1]).Select
obm = ActiveCell.Column
Rows("1:1").Cells.Find(What:="remark*", After:=[A1]).Select
remk = ActiveCell.Column
lastrow = Cells(65536, 1).End(xlUp).Row
For i = 2 To lastrow
Location = Cells(i, loc).Value
OptMin = Cells(i, m).Value
TotalTSL = Cells(i, ttsl).Value
SGAnnualDmd = Cells(i, sga).Value
SGLastSixMonths = Application.WorksheetFunction.Sum(Cells(i, sga).Offset(, -6).Resize(, 6))
SGMOU = Cells(i, sgm).Value
sgTKMMOU = Cells(i, sgt).Value
TPM_VitalSpares = Cells(i, tpmvs).Value
IPP_FinalMin = Cells(i, ipp).Value
ObsoletePartMOU = Cells(i, obm).Value
Remark = Cells(i, remk).Value
If Location = "8800" And OptMin = 0 And TotalTSL > 0 Then
If SGMOU = 0 And sgTKMMOU = 0 And ObsoletePartMOU = 0 Then
If TPM_VitalSpares = " " Then
Remark = "agree to remove TSL"
ElseIf TPM_VitalSpares <> "" Then
Remark = "disagree to remove TSL: " & TPM_VitalSpares
Else
If IPP_FinalMin <> OptMin Then
Remark = "disagree: mismatch between Opt. Min and IPP Final Min"
End If
End If
End If
Else
Remark = "need check further"
End If
Next i
End Sub
I used Find to store the column numbers in variables, then used the variables in cells(i, "columnNumber").value and loop from row2 ro lastrow. It did not populate any in column "Remark". Does anyone knows what was missing in my procedure?
I attach my test file so that it will be easier to see.
Sub test()
Dim i As Long, lastrow As Long, loc As Byte, m As Byte, ttsl As Byte, sga As Byte
Dim sgm As Byte, sgt As Byte, tpmvs As Byte, obm As Byte, ipp As Byte, remk As Byte
Dim Location, OptMin As Integer, TotalTSL As Integer, SGAnnualDmd As Long, SGMOU As Byte
Dim sgTKMMOU As Byte, ObsoletePartMOU As Byte, TPM_VitalSpares As String, Remark As String
Dim IPP_FinalMin As Integer, SGLastSixMonths As Long
Rows("1:1").Cells.Find(What:="location", After:=[A1]).Select
loc = ActiveCell.Column
Rows("1:1").Cells.Find(What:="opt*Min", After:=[A1]).Select
m = ActiveCell.Column
Rows("1:1").Cells.Find(What:="Total*TSL*", After:=[A1]).Select
ttsl = ActiveCell.Column
Rows("1:1").Cells.Find(What:="SG*Annual*", After:=[A1]).Select
sga = ActiveCell.Column
Rows("1:1").Cells.Find(What:="SG*MOU", After:=[A1]).Select
sgm = ActiveCell.Column
Rows("1:1").Cells.Find(What:="8800*TKM*MOU", After:=[A1]).Select
sgt = ActiveCell.Column
Rows("1:1").Cells.Find(What:="TPM*VIT*CE*", After:=[A1]).Select
tpmvs = ActiveCell.Column
Rows("1:1").Cells.Find(What:="ipp*min*", After:=[A1]).Select
ipp = ActiveCell.Column
Rows("1:1").Cells.Find(What:="obsolete*mou*", After:=[A1]).Select
obm = ActiveCell.Column
Rows("1:1").Cells.Find(What:="remark*", After:=[A1]).Select
remk = ActiveCell.Column
lastrow = Cells(65536, 1).End(xlUp).Row
For i = 2 To lastrow
Location = Cells(i, loc).Value
OptMin = Cells(i, m).Value
TotalTSL = Cells(i, ttsl).Value
SGAnnualDmd = Cells(i, sga).Value
SGLastSixMonths = Application.WorksheetFunction.Sum(Cells(i, sga).Offset(, -6).Resize(, 6))
SGMOU = Cells(i, sgm).Value
sgTKMMOU = Cells(i, sgt).Value
TPM_VitalSpares = Cells(i, tpmvs).Value
IPP_FinalMin = Cells(i, ipp).Value
ObsoletePartMOU = Cells(i, obm).Value
Remark = Cells(i, remk).Value
If Location = "8800" And OptMin = 0 And TotalTSL > 0 Then
If SGMOU = 0 And sgTKMMOU = 0 And ObsoletePartMOU = 0 Then
If TPM_VitalSpares = " " Then
Remark = "agree to remove TSL"
ElseIf TPM_VitalSpares <> "" Then
Remark = "disagree to remove TSL: " & TPM_VitalSpares
Else
If IPP_FinalMin <> OptMin Then
Remark = "disagree: mismatch between Opt. Min and IPP Final Min"
End If
End If
End If
Else
Remark = "need check further"
End If
Next i
End Sub