Macro with input box to Extract Data

[MacNew]

<tbody>
[TD="class: xl68"]A[/TD]
[TD="class: xl68"]B[/TD]
[TD="class: xl68"]C[/TD]
[TD="class: xl68, width: 42"]D[/TD]
[TD="class: xl68, width: 22"]E[/TD]
[TD="class: xl68, width: 49"]F[/TD]

[TD="align: right"]1[/TD]
[TD="class: xl65"]Road No[/TD]
[TD="class: xl65"]Road Name[/TD]
[TD="class: xl65"]Code[/TD]
[TD="class: xl65"]From[/TD]
[TD="class: xl65"]To[/TD]
[TD="class: xl65"]Length[/TD]

[TD="align: right"]2[/TD]
[TD="class: xl66"]A001[/TD]
[TD="class: xl66"]ABCD[/TD]
[TD="class: xl66, align: right"]111[/TD]
[TD="class: xl67"]A[/TD]
[TD="class: xl67"]B[/TD]
[TD="class: xl66, align: right"]3[/TD]

[TD="align: right"]3[/TD]
[TD="class: xl66"]A001[/TD]
[TD="class: xl66"]ABCD[/TD]
[TD="class: xl66, align: right"]111[/TD]
[TD="class: xl67"]B[/TD]
[TD="class: xl67"]C[/TD]
[TD="class: xl66, align: right"]2[/TD]

[TD="align: right"]4[/TD]
[TD="class: xl66"]A001[/TD]
[TD="class: xl66"]ABCD[/TD]
[TD="class: xl66, align: right"]111[/TD]
[TD="class: xl67"]C[/TD]
[TD="class: xl67"]D[/TD]
[TD="class: xl66, align: right"]5[/TD]

[TD="align: right"]5[/TD]
[TD="class: xl66"]A002[/TD]
[TD="class: xl66"]EFGH[/TD]
[TD="class: xl66, align: right"]211[/TD]
[TD="class: xl67"]E[/TD]
[TD="class: xl67"]F[/TD]
[TD="class: xl66, align: right"]14[/TD]

[TD="align: right"]6[/TD]
[TD="class: xl66"]A002[/TD]
[TD="class: xl66"]EFGH[/TD]
[TD="class: xl66, align: right"]211[/TD]
[TD="class: xl67"]F[/TD]
[TD="class: xl67"]G[/TD]
[TD="class: xl66, align: right"]8[/TD]

[TD="align: right"]7[/TD]
[TD="class: xl66"]A002[/TD]
[TD="class: xl66"]EFGH[/TD]
[TD="class: xl66, align: right"]211[/TD]
[TD="class: xl67"]G[/TD]
[TD="class: xl67"]H[/TD]
[TD="class: xl66, align: right"]9[/TD]

[TD="align: right"]8[/TD]
[TD="class: xl66"]B001[/TD]
[TD="class: xl66"]WXYZ[/TD]
[TD="class: xl66, align: right"]333[/TD]
[TD="class: xl67"]W[/TD]
[TD="class: xl67"]X[/TD]
[TD="class: xl66, align: right"]11[/TD]

[TD="align: right"]9[/TD]
[TD="class: xl66"]B001[/TD]
[TD="class: xl66"]WXYZ[/TD]
[TD="class: xl66, align: right"]333[/TD]
[TD="class: xl67"]X[/TD]
[TD="class: xl67"]Y[/TD]
[TD="class: xl66, align: right"]4[/TD]

[TD="align: right"]10[/TD]
[TD="class: xl66"]B001[/TD]
[TD="class: xl66"]WXYZ[/TD]
[TD="class: xl66, align: right"]333[/TD]
[TD="class: xl67"]Y[/TD]
[TD="class: xl67"]Z[/TD]
[TD="class: xl66, align: right"]8[/TD]

</tbody>

Hi

I am new to this excellent forum and currently learning excel macro programming. My problem is this. I need an excel macro for the above table (in sheet 1) with an input box, which says, "Enter the Road No"

Then, for an example, if i enter A001 as the Road No in the input box, the macro should extract all the data from cell A2 to F4 and copy to a new sheet.(sheet 2)

Please help me with this.

Thanks

MacNew

 

[hippiehacker]

try

Sub MacNew()
answer = InputBox("Please enter road no.", "Road no.")
If answer = "" Then
 MsgBox "No Road number entered or cancel pressed"
 Exit Sub
End If
With Range("a1:a" & Range("A" & Rows.Count).End(xlUp).Row)
    Set c = .Find(answer, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Address
        r1 = c.Row
        Do
            Set c = .FindNext(c)
            If c.Address <> firstaddress Then r2 = c.Row
        Loop While Not c Is Nothing And c.Address <> firstaddress
        
    End If
    If c Is Nothing Then
     MsgBox "road number not found"
     Else
     Range(Cells(r1, 1), Cells(r2, 6)).Copy Sheets("Sheet2").Range("A1")
    End If
End With
End Sub

 

[MacNew]

Thank you very much hippiehacker for helping me with this code.
This is exactly what I was looking for. You must be a genius.
Thank you once again.
By the way, sorry for the late reply, because I left the office after posting the problem.

 

[hippiehacker]

don't worry this is the changed code like requested in your PM

Sub MacNew()
answer = InputBox("Please enter road no.", "Road no.")
If answer = "" Then
 MsgBox "No Road number entered or cancel pressed"
 Exit Sub
End If
With Range("a1:a" & Range("A" & Rows.Count).End(xlUp).Row)
    Set c = .Find(answer, LookIn:=xlValues, [B][COLOR=#ff0000]lookat:=xlWhole[/COLOR][/B])
    If Not c Is Nothing Then
        firstaddress = c.Address
        r1 = c.Row
        Do
            Set c = .FindNext(c)
            If c.Address <> firstaddress Then r2 = c.Row
        Loop While Not c Is Nothing And c.Address <> firstaddress
        
    End If
    If c Is Nothing Then
     MsgBox "road number not found"
     Else
     Range(Cells(r1, 1), Cells(r2, 6)).Copy Sheets("Sheet2").Range("A1")
    End If
End With
End Sub

look at site https://www.udemy.com/blog/excel-vba-find/ for a very good explanation of the arguments of find method

 

[MacNew]

Thank you once again hippiehacker.Your code is working like a charm.
The site you mentioned, is indeed very helpful.
I am really grateful to you for saving my time while dedicating your own valuable time.