Jump to cell after clicking message box ok

[kamranyd]

These codes search quot no. and show complete quot details, if quot is not found message box appear, after clicking message box ok button it should got back to G3 cell. Now if the quot not found it dont select G3 cell. if quot is found thn it select G3 cell.

Sub search_quot()
    Dim Quot_No As String, Fnd As Range, Ws1 As Worksheet, Ws2 As Worksheet, FileNameRef As String, X As Long, Y As Long: Y = 17
    
    Sheets("Quot").Unprotect
    Application.ScreenUpdating = False
    
    Set Ws1 = Sheets("database")
    Set Ws2 = Sheets("QUOT")
    
    FileNameRef = Range("G3").Value
    
    Set Fnd = Ws1.Range("A:A").Find(Ws2.Range("G3").Value, , , xlWhole, , , , , False)
    If Fnd Is Nothing Then MsgBox "Quotation Number [-]> " & FileNameRef & " <[-]" & vbNewLine & "Not found in Quotation Database.", vbExclamation, "Quote Search ERROR":  Exit Sub
    
With Ws2
        .Range("G4").Value = Fnd.Offset(, 1).Value
        .Range("C9").Value = Fnd.Offset(, 138).Value
        .Range("C8").Value = Fnd.Offset(, 2).Value
        .Range("C9").Value = Fnd.Offset(, 3).Value
        .Range("G5").Value = Fnd.Offset(, 140).Value
        .Range("G6").Value = Fnd.Offset(, 141).Value
        .Range("G7").Value = Fnd.Offset(, 142).Value
        .Range("F66").Value = Fnd.Offset(, 7).Value
        For X = 8 To 137 Step 4
            .Range("A" & Y).Value = Fnd.Offset(, X).Value
            .Range("B" & Y).Value = Fnd.Offset(, X + 1).Value
            .Range("E" & Y).Value = Fnd.Offset(, X + 2).Value
            .Range("F" & Y).Value = Fnd.Offset(, X + 3).Value
            Y = Y + 1
        Next X
End With
     Range("G3").Select
     ActiveWindow.ScrollRow = 1
    Sheets("Quot").Protect AllowFormattingCells:=True
    Application.ScreenUpdating = True
End Sub

 

[dreid1011]

Maybe this:

Sub search_quot()
    Dim Quot_No As String, Fnd As Range, Ws1 As Worksheet, Ws2 As Worksheet, FileNameRef As String, X As Long, Y As Long: Y = 17
    
    Sheets("Quot").Unprotect
    Application.ScreenUpdating = False
    
    Set Ws1 = Sheets("database")
    Set Ws2 = Sheets("QUOT")
    
    FileNameRef = Range("G3").Value
    
    Set Fnd = Ws1.Range("A:A").Find(Ws2.Range("G3").Value, , , xlWhole, , , , , False)
    If Fnd Is Nothing Then MsgBox "Quotation Number [-]> " & FileNameRef & " <[-]" & vbNewLine & "Not found in Quotation Database.", vbExclamation, "Quote Search ERROR": Range("G3").Select: Exit Sub
    
With Ws2
        .Range("G4").Value = Fnd.Offset(, 1).Value
        .Range("C9").Value = Fnd.Offset(, 138).Value
        .Range("C8").Value = Fnd.Offset(, 2).Value
        .Range("C9").Value = Fnd.Offset(, 3).Value
        .Range("G5").Value = Fnd.Offset(, 140).Value
        .Range("G6").Value = Fnd.Offset(, 141).Value
        .Range("G7").Value = Fnd.Offset(, 142).Value
        .Range("F66").Value = Fnd.Offset(, 7).Value
        For X = 8 To 137 Step 4
            .Range("A" & Y).Value = Fnd.Offset(, X).Value
            .Range("B" & Y).Value = Fnd.Offset(, X + 1).Value
            .Range("E" & Y).Value = Fnd.Offset(, X + 2).Value
            .Range("F" & Y).Value = Fnd.Offset(, X + 3).Value
            Y = Y + 1
        Next X
End With
     Range("G3").Select
     ActiveWindow.ScrollRow = 1
    Sheets("Quot").Protect AllowFormattingCells:=True
    Application.ScreenUpdating = True
End Sub

 

[kamranyd]

Maybe this:

Sub search_quot()
    Dim Quot_No As String, Fnd As Range, Ws1 As Worksheet, Ws2 As Worksheet, FileNameRef As String, X As Long, Y As Long: Y = 17
   
    Sheets("Quot").Unprotect
    Application.ScreenUpdating = False
   
    Set Ws1 = Sheets("database")
    Set Ws2 = Sheets("QUOT")
   
    FileNameRef = Range("G3").Value
   
    Set Fnd = Ws1.Range("A:A").Find(Ws2.Range("G3").Value, , , xlWhole, , , , , False)
    If Fnd Is Nothing Then MsgBox "Quotation Number [-]> " & FileNameRef & " <[-]" & vbNewLine & "Not found in Quotation Database.", vbExclamation, "Quote Search ERROR": Range("G3").Select: Exit Sub
   
With Ws2
        .Range("G4").Value = Fnd.Offset(, 1).Value
        .Range("C9").Value = Fnd.Offset(, 138).Value
        .Range("C8").Value = Fnd.Offset(, 2).Value
        .Range("C9").Value = Fnd.Offset(, 3).Value
        .Range("G5").Value = Fnd.Offset(, 140).Value
        .Range("G6").Value = Fnd.Offset(, 141).Value
        .Range("G7").Value = Fnd.Offset(, 142).Value
        .Range("F66").Value = Fnd.Offset(, 7).Value
        For X = 8 To 137 Step 4
            .Range("A" & Y).Value = Fnd.Offset(, X).Value
            .Range("B" & Y).Value = Fnd.Offset(, X + 1).Value
            .Range("E" & Y).Value = Fnd.Offset(, X + 2).Value
            .Range("F" & Y).Value = Fnd.Offset(, X + 3).Value
            Y = Y + 1
        Next X
End With
     Range("G3").Select
     ActiveWindow.ScrollRow = 1
    Sheets("Quot").Protect AllowFormattingCells:=True
    Application.ScreenUpdating = True
End Sub

thnx

 

[dreid1011]

You're welcome.