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

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" wrote in message
...
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

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)


"nc" wrote in
message
...
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


.