|
If then VBA
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 . |
| All times are GMT +1. The time now is 02:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com