VBA to search for a row of text, then display that row for manual data entry

Hi all,
I am getting stuck in my code and need help solving it. Here is the code:

Option Explicit

Sub findData()
Dim GCell As Range
Dim Txt$, MyPath$, MyWB$, MySheet$
Dim myValue As String

Dim wbMain As Workbook
Set wbMain = ThisWorkbook

Txt = InputBox("What Organization do you want to search for?")

MyPath = "C:\users\DKane\My Documents\"
MyWB = "EVHC Master Hiring Spreadsheet range find.xlsx"

Dim ws As Worksheet
Set ws = wbMain.Sheets("Sheet1")

Application.ScreenUpdating = False

Dim wbSearch As Workbook
Set wbSearch = Workbooks.Open(Filename:=MyPath & MyWB)

Set GCell = wbSearch.Sheets(1).Cells.Find(Txt)

If Not GCell Is Nothing Then


********this is were the error seems to be *****

Dim lCol As Long
lCol = wbSearch.Range("A1").End(xlToRight).Column


wbSearch.Range(Range(Range("A1"), Cells(1, lCol))).Copy ws.Range("A1")


wbSearch.Range(Range(Cells(GCell.Row, 1), Cells(GCell.Row, lCol))).Copy ws.Range("B1")

ws.Columns.AutoFit

Else

MsgBox "Org Not Found"

below are the row headings:
HR Contact (person who can answer questions about this org) Region Organization (Company.Location Code.Department) Location in Oracle format Job Title PT/FT Local Tax Element Benefits Code Benefits Code Comments (if multiple, how can MHRC determine what benefit code should be used?) Mailstop Internal Transfer? Payroll ID Local Tax Element Union Code Union Code Comments (if multiple, how can MHRC determine when to use which code?) Uniform Allowance PTO Date Drug Screen Provider (e.g. Quest, internal, Concentra) Sign-on bonus instructions (including under what circumstances each is used, if multiple)



Thanks in advance for any help.

lCol = wbSearch.Range("A1").End(xlToRight).Column

You could try

lCol = wbSearch.Cells(1, Columns.Count).End(xlToLeft).Column

But I don't really see why you would get an error with the original code. BTW, what is the error message and which line is highlighted when you click the debug button?

yes that is what is highlighted when I debug...

Dim lCol As Long
lCol = wbSearch.Range("A1").End(xlToRight).Column

Thanks so much....seems to be highlighting the prior debug error:

Dim lCol As Long
lCol = wbSearch.Range("A1").End(xlToRight).Column

I do not think it likes the range here either, but not sure what else to use.
Thanks again

this is the error I am now receiving


Run-time error '438':

Object doesn't support this property or method.

Just about all of your WBSearch statements are missing the worksheet reference. eg. wbSearch.Sheets("Sheet1")...etc.

so would that look like this:

lCol = wbSearch.Range("A1").End(xlToRight).Column

wbSearch.Sheets("Sheet1")


wbSearch.Range(Range(Range("A1"), Cells(1, lCol))).Copy ws.Range("A1")


wbSearch.Range(Range(Cells(GCell.Row, 1), Cells(GCell.Row, lCol))).Copy ws.Range("B1")

Here is how I would write the code

Option Explicit

 Sub findData()
 Dim GCell As Range
 Dim Txt$, MyPath$, MyWB$, MySheet$
 Dim myValue As String
 Dim ws As Worksheet, sh As Worksheet
 Dim wbMain As Workbook
 Dim lCol As Long
 Set wbMain = ThisWorkbook
 Set ws = wbMain.Sheets("Sheet1")
 Txt = InputBox("What Organization do you want to search for?")
 MyPath = "C:\users\DKane\My Documents\"
 MyWB = "EVHC Master Hiring Spreadsheet range find.xlsx"
 Application.ScreenUpdating = False
 Dim wbSearch As Workbook
 Set wbSearch = Workbooks.Open(Filename:=MyPath & MyWB)
 Set sh = wbSearch.Sheets("Sheet1")
 Set GCell = sh.Cells.Find(Txt)
     If Not GCell Is Nothing Then 
         lCol = sh.Range("A1").End(xlToRight).Column
         sh.Range(Range("A1"), Cells(1, lCol)).Copy ws.Range("A1")
         sh.Range(Cells(GCell.Row, 1), Cells(GCell.Row, lCol)).Copy ws.Range("B1")
         ws.Columns.AutoFit
     Else

Notice I moved all the Dim statements to top of the code. That makes the code easier to read and has all the memory reserved up front. Using the variable for the worksheet also reduces the length of the code statements.

Strange it is not displaying anything but org not found even if the org is on the spreadsheet...hmmmThanks again for all of your assistance, no one in my office will attempt this, so I thought I would try. you rock!!!

lCol = wbSearch.Sheets("Sheet1").Range("A1").End(xlToRight).Column 

 wbSearch.Sheets("Sheet1").Range(Range(Range("A1"), Cells(1, lCol))).Copy ws.Range("A1")

 wbSearch.Sheets("Sheet1").Range(Range(Cells(GCell.Row, 1), Cells(GCell.Row, lCol))).Copy ws.Range("B1")

Excel is built on a parent/child object model. If you use one of the parents, you cannot skip over that parent's child to the grandchild or VBA will throw an error. One way to avoid these common code writing errors is to use variariables, like the ws variable you have in the code. That variable includes the family ancestry back to the workbook level, so the workbook does not have to be specified each time you want to reference the sheet or a range on the sheet.
[/Code]