Return cell address in messagebox

[MacDaddy]

Good afternoon,

I'm very new to VBA programming so I'm sorry if this is an idiot question.

I'm writing a macro in Excel 2007 which scans a section in a workbook using a do loop and looks for invalid values, if it finds a wrong value I want it to display a mesagebox with the cell address of the incorrect value found by the macro.

I thought that by using ActiveCell.Address I would see the results I wanted and if I run the macro on its own I do. However, as this macro is called in a sequence of other macros the ActiveCell.Address value is returning the address of the last cell referenced by the macro executed immediately prior.

The code I've written is as follows, again please bear in mind that this is one of the first bits of VBA code I've ever written, please be nice :o:


Sub CheckQty()

Dim MsgNoQty As String
Dim MsgWrongFormat As String
Dim MsgQtyHeader As String

MsgNoQty = "Please enter a valid quantity in cell " & ActiveCell.Address
MsgWrongFormat = "The value in cell " & ActiveCell.Address & " is not valid. Please enter a valid quantity"
MsgQtyHeader = "The value 'Qty' in cell " & ActiveCell.Address & " is in the wrong location, please correct"

Application.ScreenUpdating = False

Range("J10").Select
Do Until ActiveCell.Row = 98
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" And ActiveCell.Offset(0, -1).Value <> "" Then
MsgBox (MsgNoQty)
End
Else
If ActiveCell.Value <> "" And ActiveCell.Value <> "Qty" And IsNumeric(ActiveCell.Value) = False Then
MsgBox (MsgWrongFormat)
End
Else
If ActiveCell.Value = "Qty" And ActiveCell.Offset(0, -1).Value <> "Op No" Then
MsgBox (MsgQtyHeader)
Else
If ActiveCell.Value <> "" And Left(ActiveCell.Formula, 1) = 0 And Mid(ActiveCell.Formula, 2, 1) <> "." Then
MsgBox (MsgWrongFormat)
End
Else
If ActiveCell.Value <> "" And ActiveCell.NumberFormat = "@" Then
MsgBox (MsgWrongFormat)
End
Else
If ActiveCell.Value <> "" And ActiveCell.PrefixCharacter = "'" Then
MsgBox (MsgWrongFormat)
End
End If
End If
End If
End If
End If
End If
Loop

End Sub

[Bob Phillips]

When you set that message up, the activecell is added there.

When you go through the loop, the activecell is changing, but the message isn't, so you poin t to the original activecell.

Activecell is not necessary anyway



Sub CheckQty()

Dim MsgNoQty As String
Dim MsgWrongFormat As String
Dim MsgQtyHeader As String
Dim i As Long

MsgNoQty = "Please enter a valid quantity in cell <cell>"
MsgWrongFormat = "The value in cell <cell> is not valid. Please enter a valid quantity"
MsgQtyHeader = "The value 'Qty' in cell <cell> is in the wrong location, please correct"

Application.ScreenUpdating = False

For i = 11 To 98

With Cells(i, "J")

If .Value = "" And .Offset(0, -1).Value <> "" Then

MsgBox Replace(MsgNoQty, "<cell>", .Address)
Exit For
ElseIf .Value <> "" And .Value <> "Qty" And IsNumeric(.Value) = False Then

MsgBox Replace(MsgWrongFormat, "<cell>", .Address)
Exit For
ElseIf .Value = "Qty" And .Offset(0, -1).Value <> "Op No" Then

MsgBox Replace(MsgQtyHeader, "<cell>", .Address)
Exit For
ElseIf ActiveCell.Value <> "" And Left(ActiveCell.Formula, 1) = 0 And Mid(ActiveCell.Formula, 2, 1) <> "." Then

MsgBox Replace(MsgWrongFormat, "<cell>", .Address)
Exit For
ElseIf ActiveCell.Value <> "" And ActiveCell.NumberFormat = "@" Then

MsgBox Replace(MsgWrongFormat, "<cell>", .Address)
Exit For
ElseIf ActiveCell.Value <> "" And ActiveCell.PrefixCharacter = "'" Then

MsgBox Replace(MsgWrongFormat, "<cell>", .Address)
Exit For
End If
End With
Next i

End Sub

[p45cal]

This is better than what I was just about to post, the only thingI'd add is directly from vba Help:"Remember to set the ScreenUpdating property back to True when your macro ends"

[JP2112]

The reason why ActiveCell.Address is returning the address of the last cell you referenced is because all three of your String variables at the top of the procedure are calling ActiveCell.Address before the Do loop. So ActiveCell.Address is pointing at whatever was previously the active cell, not at where you are looping in this macro.

[Bob Phillips]

This is better than what I was just about to post, the only thingI'd add is directly from vba Help:"Remember to set the ScreenUpdating property back to True when your macro ends"

Good point sir!

[MacDaddy]

Thank you!!

Not only have you helped me solve my problem but I've learned an awful lot to boot. :pleased:

Thanks again,

Chris