Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing (Excel VBA 2003)

    I'm trying to import data from a bunch of excel files into one main spreadsheet. I need to find the value stored in the location database_arrayA(i) = Cells(labCodeRowIndex, aCodeColumnIndex).Value

    I use the following code to find the column and row numbers for the value I need.
    aCodeColumnIndex = Rows(1).Find(aCode).Column
    labCodeRowIndex = Columns(1).Find(labCode).Row

    The problem is not all the smaller excel files have the aCode and labCode there so I get a error. Object variable or with block variable not set.

    I would like the value to be blank if not found and continue.

    Thanks,

    Deni

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Importing (Excel VBA 2003)

    You can use something like:
    <pre>Dim rngFound as Range
    Set rngFound = Rows(1).Find(aCode)
    If not rngFound Is Nothing Then aCodeColumnIndex = rngFound.Column
    Set rngFound = Nothing
    Set rngFound = Columns(1).Find(labCode)
    If Not rngFound is Nothing Then labCodeRowIndex = rngFound.Row
    </pre>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing (Excel VBA 2003)

    Thanks, that worked perfect.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing (Excel VBA 2003)

    Found a problem for the code below. When I'm searching for labCode 42 it finds labCode 142. Is there a way around this?

    Set rgnFound = Columns(1).Find(labCode)
    If Not rgnFound Is Nothing Then labCodeRowIndex = rgnFound.Row

    Thanks in advance

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing (Excel VBA 2003)

    Change the first line to

    Set rgnFound = Columns(1).Find(What:=labCode, LookAt:=xlWhole)

    The LookAt argument specifies that you're looking for complete matches, not partial matches.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •