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

 

[hiker95]

Deonvg,

One of your variables is not defined.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Private Sub CheckAmtType()
Dim AmtACAV As Integer
Dim AmtTypeCheck 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

 

[RSXchin]

Well, now that he's defined it, he still needs to assign it a value. I don't see a need for it at all

 

[Deonvg]

Deonvg,

One of your variables is not defined.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Private Sub CheckAmtType()
Dim AmtACAV As Integer
Dim AmtTypeCheck 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

I have added as advised but when I test the code it shows the message box when it comes to the "End If' and thus doesn't loop.

I need the code to check that whole column and if it finds amount types other than La or LV, it should show the message box. If it dosn't find anything else the code should continue and do rest.

Thx for your reply.

 

[hiker95]

Deonvg,

I tested the macro with test data and it worked.

Can we see your data?

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[Norie]

Try this.

Private Sub CheckAmtType()
Dim rng As Range
Dim AmtACAV As Integer
Dim AmtTypeCheck As Integer
 
    AmtACAV = AmtTypeCheck
    
    Set rng = Range("E2")
 
    While rng.Value <> ""
        Select Case rng.Value
        
            Case "LA", "LV"
                ' do nothin
            Case Else
                MsgBox "Amount Type AC or AV Incorrect"
                
        End Select
        
        Set rng = rng.Offset(1, 0)

    Wend
    
End Sub

 

[hiker95]

rsxchin,

Both variables, according to the code, start off as 0.

AmtACAV = AmtTypeCheck

 

[RSXchin]

What if he changed

ActiveCell.Offset(1, 0).Select

to

ActiveCell.Offset(1, 0).activate

 

[Deonvg]

rsxchin,

Both variables, according to the code, start off as

Excel Workbook
	A	B	C	D	E	F	G	H
1	Ledg_id	account	center	year	Type	Jan	Feb	Mrt
2	BNK	A060101	CORBR	2010	LA	0.00	0.00	0.00
3	BNK	A060103	CORBR	2010	LA	0.00	0.00	0.00
4	BNK	A060106	CORBR	2010	LA	0.00	0.00	0.00
5	BNK	A060109	CORBR	2010	LA	0.00	0.00	0.00
6	BNK	A061107	CORBR	2010	LA	0.00	0.00	0.00
7	BNK	A061110	CORBR	2010	LA	0.00	0.00	0.00
8	BNK	A061111	CORBR	2010	LV	0.00	0.00	0.00
9	BNK	A061112	CORBR	2010	LA	0.00	0.00	0.00
10	BNK	I010101	CORBR	2010	LA	0.00	0.00	0.00
11	BNK	I010103	CORBR	2010	LV	0.00	0.00	0.00
12	BNK	I010106	CORBR	2010	LA	0.00	0.00	0.00
13	BNK	I010107	CORBR	2010	LA	0.00	0.00	0.00
0.

#VALUE!

Excel 2007

 

[hiker95]

Deonvg,

Have you tried Norie's code?