If then VBA

[nc]

Hi

Can I simplify the codes below, is it possible to use
Select Case?

Thanks.

On Error Resume Next

Range("B1:B500").Find("Product ").Select
InvReqHeadingRowNo = ActiveCell.Row

If Range("A1") = "" And Err <> 0 Then
Msg = "This invoice request is incorrectly
formatted."
Msg = Msg & vbCrLf & "The title and products must
be in cell A1 and column B respectively."
Msg = Msg & vbCrLf & "Please ammend the invoice
request before tranferring to template."
Msg = MsgBox(Msg, , "Formatting error")
Workbooks(InvReqWbkNme).Activate
Exit Sub
ElseIf Err <> 0 Then
Msg = "This invoice request is incorrectly
formatted."
Msg = Msg & vbCrLf & "The products must be in
column B."
Msg = Msg & vbCrLf & "Please ammend the invoice
request before tranferring to template."
Msg = MsgBox(Msg, , "Formatting error")
Workbooks(InvReqWbkNme).Activate
Exit Sub
ElseIf Range("A1") = "" Then
Msg = "This invoice request is incorrectly
formatted."
Msg = Msg & vbCrLf & "The title must be in cell
A1"
Msg = Msg & vbCrLf & "Please ammend the invoice
request before tranferring to template."
Msg = MsgBox(Msg, , "Formatting error")
Workbooks(InvReqWbkNme).Activate
Exit Sub
End If

 

[Bob Phillips]

Not really, but you could be less repetitive

Const kMsg1 As String = _
"This invoice request is incorrectly formatted."
Const kMsg2 As String = _
"Please ammend the invoice request before tranferring to template."

On Error Resume Next

Range("B1:B500").Find("Product ").Select
InvReqHeadingRowNo = ActiveCell.Row

If Range("A1") = "" And Err <> 0 Then
Msg = kMsg & vbCrLf & "The title and products must be in cell " & _
"A1 and column B respectively."
Msg = Msg & vbCrLf & kMsg2
Msg = MsgBox(Msg, , "Formatting error")
Workbooks(InvReqWbkNme).Activate
Exit Sub
ElseIf Err <> 0 Then
Msg = kMsg & vbCrLf & "The products must be in column B."
Msg = Msg & vbCrLf & kMsg2
Msg = MsgBox(Msg, , "Formatting error")
Workbooks(InvReqWbkNme).Activate
Exit Sub
ElseIf Range("A1") = "" Then
Msg = kMsg & vbCrLf & "The title must be in cell A1 "
Msg = Msg & vbCrLf & kMsg2
Msg = MsgBox(Msg, , "Formatting error")
Workbooks(InvReqWbkNme).Activate
Exit Sub
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[nc]

Thanks

-----Original Message-----
Not really, but you could be less repetitive

Const kMsg1 As String = _
"This invoice request is incorrectly formatted."
Const kMsg2 As String = _
"Please ammend the invoice request before tranferring to template."

On Error Resume Next

Range("B1:B500").Find("Product ").Select
InvReqHeadingRowNo = ActiveCell.Row

If Range("A1") = "" And Err <> 0 Then
Msg = kMsg & vbCrLf & "The title and products must be in cell " & _
"A1 and column B respectively."
Msg = Msg & vbCrLf & kMsg2
Msg = MsgBox(Msg, , "Formatting error")
Workbooks(InvReqWbkNme).Activate
Exit Sub
ElseIf Err <> 0 Then
Msg = kMsg & vbCrLf & "The products must be in column B."
Msg = Msg & vbCrLf & kMsg2
Msg = MsgBox(Msg, , "Formatting error")
Workbooks(InvReqWbkNme).Activate
Exit Sub
ElseIf Range("A1") = "" Then
Msg = kMsg & vbCrLf & "The title must be in cell A1 "
Msg = Msg & vbCrLf & kMsg2
Msg = MsgBox(Msg, , "Formatting error")
Workbooks(InvReqWbkNme).Activate
Exit Sub
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)





.