MacDaddy
10-26-2009, 08:35 AM
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
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