Add Msgbox

[Prasad K]

Hi i have data with Bike Sales in my sheet

i want to add this line "Do you want to change Mobile No Click "Yes" in this below vba
add Yes or No Options in this code if i click "Yes" then it will go to that mobile no cell

i have data like this
Column A - Customer Name
Column B - Mobile Number
Column C - Address
Column D - Purchased Bike
Column E - Purchased Date

Sub SearchNreturn()
Dim Rng As Range
Dim mblNo As String

mblNo = InputBox("Enter Mobile No")
Set Rng = Sheets("Sales").Range("B:B").Find(What:=mblNo, _
            After:=Cells(1, 2), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)

            If Not Rng Is Nothing Then
               MsgBox "Customer Name: - " & Rng.Offset(0, -1) & vbNewLine & _
                    vbNewLine & _
                      "Mobile: - " & Rng.Value & vbNewLine & _
                    vbNewLine & _
                      "Purchased Bike: " & Rng.Offset(0, 2) & vbNewLine & _
                    vbNewLine & _
                      "Purchased Date: " & Rng.Offset(0, 3) & vbNewLine & _
                    vbNewLine & _
                      "Address: - " & Rng.Offset(0, 1)
                   
                Else
                MsgBox "No Details Found in Data."
            End If
End Sub

 

[Fluff]

How about

Sub SearchNreturn()
Dim Rng As Range
Dim mblNo As String

mblNo = InputBox("Enter Mobile No")
Set Rng = Sheets("Sales").Range("B:B").Find(What:=mblNo, _
            After:=Cells(1, 2), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)

            If Not Rng Is Nothing Then
               MsgBox "Customer Name: - " & Rng.Offset(0, -1) & vbNewLine & _
                    vbNewLine & _
                      "Mobile: - " & Rng.Value & vbNewLine & _
                    vbNewLine & _
                      "Purchased Bike: " & Rng.Offset(0, 2) & vbNewLine & _
                    vbNewLine & _
                      "Purchased Date: " & Rng.Offset(0, 3) & vbNewLine & _
                    vbNewLine & _
                      "Address: - " & Rng.Offset(0, 1)
                If MsgBox("Do you want to change Mobile No Click", vbYesNo) = vbYes Then
                    Application.Goto Rng, True
                End If
            Else
                MsgBox "No Details Found in Data."
            End If
End Sub

 

[Prasad K]

How about

Sub SearchNreturn()
Dim Rng As Range
Dim mblNo As String

mblNo = InputBox("Enter Mobile No")
Set Rng = Sheets("Sales").Range("B:B").Find(What:=mblNo, _
            After:=Cells(1, 2), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)

            If Not Rng Is Nothing Then
               MsgBox "Customer Name: - " & Rng.Offset(0, -1) & vbNewLine & _
                    vbNewLine & _
                      "Mobile: - " & Rng.Value & vbNewLine & _
                    vbNewLine & _
                      "Purchased Bike: " & Rng.Offset(0, 2) & vbNewLine & _
                    vbNewLine & _
                      "Purchased Date: " & Rng.Offset(0, 3) & vbNewLine & _
                    vbNewLine & _
                      "Address: - " & Rng.Offset(0, 1)
                If MsgBox("Do you want to change Mobile No Click", vbYesNo) = vbYes Then
                    Application.Goto Rng, True
                End If
            Else
                MsgBox "No Details Found in Data."
            End If
End Sub

Thank you so much Fluff

 

[Fluff]

You're welcome & thanks for the feedback.