excel: searching for cell contents and returning rows 2

[smallcat]

Lousy subject line -- sorry.

Here is my headache:

I have a spreadsheet with about two thousand lines of account information (account number, name, address,etc).
Using an input box I am searching for the for the account number (first cell in each row) and that works fine, but then I want to pick up the rest of the row, copy it and paste it to another worksheet for mail merging.

I cannot seem to find any way to select the whole row, or the first seven columns of the row without using absolute references, i.e. (A1:A7).

Sure would appreciate help -- the boss is breathing down my neck.

-sc-

 

[dsi]

Why don't you use the vlookup command?

This sample is extremely crude, but its time to go home:

Option Explicit

Sub GetData()
    Dim sAcctNo As String
    Dim iRow As Long
    
    sAcctNo = InputBox("Give me the #")
    'Error check the result!!!
    iRow = 1    'first row of data
    Do While Len(Range("A" & iRow).Text) > 0
        If sAcctNo Like Range("A" & iRow).Text Then
            'Found it - Put in on Sheet2
            Rows(iRow & ":" & iRow).Select
            Selection.Copy
            Sheets("Sheet2").Select
            Range("A3").Select
            ActiveSheet.Paste
        End If
        iRow = iRow + 1
    Loop
   
End Sub

Hope this helps!

 

[LoNeRaVeR]

smallcat:

I concur with dsi! This is a perfect use for the [tt]VLOOKUP[/tt] function because you can pull all of the information by account rather than one at a time with VBA code. I had a few more minutes than dsi and created the following code. You will need to change the Sheets where the data is copied from and to.
[tt]
Option Explicit
Sub CopyRow()
Dim sAccount As String
Dim sFound As String
Dim oAccount As Object
sAccount = InputBox(Prompt:="Enter Account Number:&quot
Sheets(1).Select
With Worksheets(1).Range("A:A&quot
Set oAccount = .Find(sAccount)
If Not oAccount Is Nothing Then
sFound = oAccount.Address
Range(sFound).EntireRow.Copy
Sheets(2).Select
Cells(65536, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Cells(65536, 1).End(xlUp).Offset(1, 0).Select
Set oAccount = .FindNext(oAccount)
End If
End With
Application.CutCopyMode = False
Sheets(1).Select
End Sub
[/tt]
Please let me know if you have any questions about using [tt]VLOOKUP[/tt] to accomplish this task much more quickly.

Regards,

LoNeRaVeR

 

[LoNeRaVeR]

smallcat:
[tt]
sAccount = InputBox(Prompt:="Enter Account Number:&quot
[/tt]
Should all be on one line in the code above.

LoNeRaVeR

 

[LoNeRaVeR]

smallcat:

A minor code change for you.

The line just before the [tt]End If[/tt] statement
[tt]
Set oAccount = .FindNext(oAccount)
[/tt]
is not required. I originally wrote the code to loop and continue checking for the same account number, but removed the loop and forgot to remove that line. :-(

Sorry,

LoNeRaVeR