Loop Error

[Deonvg]

Hi all,

I have the below code, which I cannot get to work , I'm obviously overlooking something here . Would someone be able to assist?

Private Sub CheckAmtType()
Dim AmtACAV As Integer
AmtACAV = AmtTypeCheck
Range("E2").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Value <> "LA" Then
MsgBox "Amount Type AC or AV Incorrect"
ElseIf ActiveCell.Value <> "LV" Then
MsgBox "Amount Type AC or AV Incorrect"
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[Deonvg]

Hiker95,

Yes I have, didn't see that doing the trick either.

thx

 

[Deonvg]

Hiker95,

Seems like Norie's code is only being applied to Range E2 instead of the entire column or until it finds a blanks or empty cell. Might even be on line 3500
thx
Deon

 

[RSXchin]

try this

Option Explicit
Sub CheckAmtType()
    Dim LR As Long, i As Long
    LR = Cells(Rows.Count, 5).End(xlUp).Row
    For i = 2 To LR
        If Cells(i, 5) <> "LV" And Cells(i, 5) <> "LA" Then
            Cells(i, 5).Select
            MsgBox "Amount Type AC or AV Incorrect in row " & i
            Exit Sub
        End If
    Next i
End Sub

 

[Norie]

The code I posted works for the data you posted, or it does work when a value is changed in column E.

All the values in your data are LV or LA, so the messagebox will never get called otherwise.

 

[RSXchin]

I think Data validation would be a better idea for this. if it's ALWAYS these 2 things

 

[hiker95]

Deonvg,

Be right back.

 

[Deonvg]

Hiker95 and Norie,

Thank you very much for your reply, Sorry yes, Norie and Hiker95's (last code) works perfectly.

Indeed if you dont have any other amount type the message box will not appear, ONLY unless you have a AC or AV for argument. That is exaclt what it's suppose to do. As the file should only contain LA's or LV's inorder for the uplaod to work correctly, if it doens't it could jeopordise other results.

Thx so much

 

[Deonvg]

Hi,

I have also managed to get another code to work.

Private Sub CheckAmt()
Range("E2").Select
Do While Not IsEmpty(ActiveCell)
If ActiveCell.Value <> "LA" And ActiveCell.Value <> "LV" Then
MsgBox "Amt Type must be LA or LV"
End
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

 

[Deonvg]

Thansk for all you help. Much appreciated