IfThenElseIfElse statement within For..Next loop

[alienscript]

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

[Paul_Hossler]

Opps -- hit the enter key too fast

[Paul_Hossler]

Well, using your sample XLS, this line


lastrow = Cells(65536, 1).End(xlUp).Row


makes 'lastrow' = 1 since A2 is empty

and then


For i = 2 To lastrow


never get executed.

Is there real data in a real spreadsheet?

Paul

[alienscript]

Yes Paul, I am sorry I removed the real data in column1. Just for this case, please use this:
lastrow = Cells(65536, 2).End(xlUp).Row

[alienscript]

did someone happen to see what's wrong with my conditional statement? Appreciate very much if I can get some help and Thanks a bunch folk ;-)

[Bob Phillips]

Well this line looks wrong to me



If TPM_VitalSpares = " " Then

[Norie]

Well the main thing missing is probably that you aren't putting anything back onto the worksheet. ie the remark

This does not affect the worksheet.

Remark = "need check further"
Put this before Next i.


Cells(i, remk).Value = Remark

[alienscript]

XLD, Norie,
Now I realize the missing piece :-) It's so easy but I had thought for it so long. Thanks so much folk