none
Vlookup and object required error RRS feed

  • Question

  • I hope someone can assist.

    I am trying to return a value to a cell from a lookup in a different workbook.  With my current code (below) I am getting an object required error.  It doesn't matter how I reference the required cell it, including defining and setting it as a range, it still insists that an object is required.  The problem is that this code is going to be used within a loop structure and will be different every run through. 

    Sub OptusCalls_PrepareData()

    Dim strCallType As String
    Dim lngPhoneNo As Long
    Dim strAllocatedTo As String
    Dim rngTemp As Range

    'Range("B:C").Insert
    Range("A1").Select
    'Loop until first call type of c is located'
    Do Until strCallType = "C"
    ActiveCell.Offset(1, 0).Select
    strCallType = Trim(ActiveCell.Text)
    Loop

    'record phone number into variable
    lngPhoneNo = Trim(ActiveCell.Offset(-2, 5).Value)
    'Record phone number in next column
    ActiveCell.Offset(0, 1).Value = lngPhoneNo
    'Record lookup value in 3rd column
    ActiveCell.Offset(0, 2).Text = Application.VLookup(lngPhoneNo, [OptusPhoneNumbers.xls]!Numbers, 2, False)

    End Sub

    The following formula works in the excel spreadsheet so I think that the vlookup is functioning correctly

    =VLOOKUP(TRIM(F220),OptusPhoneNumbers.xls!Numbers,2,FALSE) the reference of F220 is fed into the macro via an offset which will remain consistent.

    Therefore it is my missing object which is the matter but I can't work out what it wants.

    Wednesday, May 16, 2012 3:52 AM

Answers

  • Try the following. (Single quotes around workbook name only really required if spaces exist in workbook name but can be used anyway. Also Text does not work for me but Value does work but can be left out as per the example.)

    ActiveCell.Offset(0, 2) = Application.VLookup(lngPhoneNo, Range("'OptusPhoneNumbers.xls'!Numbers"), 2, False)


    Regards, OssieMac




    • Edited by OssieMac Wednesday, May 16, 2012 5:46 AM Change file type from .xlsx ro .xls to match OP's question
    • Marked as answer by JC- SpeedCast Wednesday, May 16, 2012 11:45 PM
    Wednesday, May 16, 2012 5:19 AM
  • A long explanation but I am trying to give you all the options so you don't have to get back to me but feel free to do so if still having problems.

    Not sure what your expertise is so firstly let me confirm with you that the column with the phone numbers in the Vlookup Table_Array is the first column of the array. (If not, it must be for Vlookup to work.)

    The code that I posted is tested so I don't believe that is the problem and therefore we need to look at the data.

    My guess is that you have either leading or trailing spaces in the phone numbers in the Table_Array that is being looked up or the column is text and is displaying the leading zero.

    You have trimmed the value where you assigned it to lngPhoneNo so that should not be a problem.

    How are the Optus phone numbers appearing? Are the leading zeros displayed? If so, are they Text or are they real numbers formatted to 10 digits so that they display the leading zero? If they are Text displaying the leading zero then they will not match a number that you have assigned to  long variable.

    I assume that there are no intermediate spaces in the phone numbers. Is this correct? If so, try selecting the column and then open Replace and insert a space in the "Find what:" field and nothing in the "Replace with:" field and click replace all and that will remove any leading and trailing spaces.

    If above does not work then you could have other non printable characters in which case insert a helper column immediately to right of phone numbers and in there insert the following formula (assuming C2 is the first cell with a phone number):

    =TRIM(CLEAN(C2))

    Copy the formula down and then select the column and Copy -> Paste Special -> Values and then copy and paste the column over the original phone numbers.

    If the Phone numbers are Text then to convert to numeric, Select the column then Text To Columns -> Fixed Width and when you get to the screen to select the format type select general and finish. The phone numbers should now be real numbers. Select the column and then set a custom number format to "0000000000" (10 zeros) and it should display the leading zero and the long value for the lookup reference should work.


    Regards, OssieMac

    • Marked as answer by JC- SpeedCast Wednesday, May 16, 2012 11:45 PM
    Wednesday, May 16, 2012 7:01 AM
  • Thank you.  Changing the code to this has stopped the object required error.  The code runs all the way through but returns a #N/A value into the cell where I am expecting the name of the customer retrieved by the lookup.

    The lookup file is structured:

    Phone           Customer

    55555555      ASC

    66666666     ASC

    This is the reason I was trying to return text not a value to this cell.  I am trying to match the variable lngPhoneNumber with a number in the the lookup data range which exists in another workbook and return the customer name.

    Ideally, perhaps, I would like it to be read into a string variable strAllocatedTo - or should I declare it as a variant data type?  Anyway I still get the object required message when I try to do this.

    If you have any further ideas on this it certainly would be appreciated. At this stage part of this code will be split off into functions but I am just trying to get the lookup working in the vba code first. 

    • Marked as answer by JC- SpeedCast Thursday, May 17, 2012 11:16 PM
    Wednesday, May 16, 2012 5:55 AM
  • Thank you again.  Skill level relatively neophyte.  I just don't do enough of this often enough.  It can be a year between being required to write in VBA and then mix it up with SQL and SSRS in between.

    I came to the conclusion at the end of yesterday that my lookup range was at fault as you have.  I really appreciate your suggestions with making sure the values are in the correct format in Excel.  I had attempted to do this prior to writing the code but I am sure that Excel still basically believes the values to be text (the little green triangle a big hint I would say).  I had toyed with the idea of CStr when passing the variable to the lookup table, but it would be better to have the lookup range in the correct format.

    Regards Judi

    • Marked as answer by JC- SpeedCast Wednesday, May 16, 2012 11:45 PM
    • Unmarked as answer by JC- SpeedCast Wednesday, May 16, 2012 11:45 PM
    • Marked as answer by JC- SpeedCast Thursday, May 17, 2012 11:16 PM
    Wednesday, May 16, 2012 11:44 PM

All replies

  • Try the following. (Single quotes around workbook name only really required if spaces exist in workbook name but can be used anyway. Also Text does not work for me but Value does work but can be left out as per the example.)

    ActiveCell.Offset(0, 2) = Application.VLookup(lngPhoneNo, Range("'OptusPhoneNumbers.xls'!Numbers"), 2, False)


    Regards, OssieMac




    • Edited by OssieMac Wednesday, May 16, 2012 5:46 AM Change file type from .xlsx ro .xls to match OP's question
    • Marked as answer by JC- SpeedCast Wednesday, May 16, 2012 11:45 PM
    Wednesday, May 16, 2012 5:19 AM
  • Thank you.  Changing the code to this has stopped the object required error.  The code runs all the way through but returns a #N/A value into the cell where I am expecting the name of the customer retrieved by the lookup.

    The lookup file is structured:

    Phone           Customer

    55555555      ASC

    66666666     ASC

    This is the reason I was trying to return text not a value to this cell.  I am trying to match the variable lngPhoneNumber with a number in the the lookup data range which exists in another workbook and return the customer name.

    Ideally, perhaps, I would like it to be read into a string variable strAllocatedTo - or should I declare it as a variant data type?  Anyway I still get the object required message when I try to do this.

    If you have any further ideas on this it certainly would be appreciated. At this stage part of this code will be split off into functions but I am just trying to get the lookup working in the vba code first. 

    • Marked as answer by JC- SpeedCast Thursday, May 17, 2012 11:16 PM
    Wednesday, May 16, 2012 5:55 AM
  • A long explanation but I am trying to give you all the options so you don't have to get back to me but feel free to do so if still having problems.

    Not sure what your expertise is so firstly let me confirm with you that the column with the phone numbers in the Vlookup Table_Array is the first column of the array. (If not, it must be for Vlookup to work.)

    The code that I posted is tested so I don't believe that is the problem and therefore we need to look at the data.

    My guess is that you have either leading or trailing spaces in the phone numbers in the Table_Array that is being looked up or the column is text and is displaying the leading zero.

    You have trimmed the value where you assigned it to lngPhoneNo so that should not be a problem.

    How are the Optus phone numbers appearing? Are the leading zeros displayed? If so, are they Text or are they real numbers formatted to 10 digits so that they display the leading zero? If they are Text displaying the leading zero then they will not match a number that you have assigned to  long variable.

    I assume that there are no intermediate spaces in the phone numbers. Is this correct? If so, try selecting the column and then open Replace and insert a space in the "Find what:" field and nothing in the "Replace with:" field and click replace all and that will remove any leading and trailing spaces.

    If above does not work then you could have other non printable characters in which case insert a helper column immediately to right of phone numbers and in there insert the following formula (assuming C2 is the first cell with a phone number):

    =TRIM(CLEAN(C2))

    Copy the formula down and then select the column and Copy -> Paste Special -> Values and then copy and paste the column over the original phone numbers.

    If the Phone numbers are Text then to convert to numeric, Select the column then Text To Columns -> Fixed Width and when you get to the screen to select the format type select general and finish. The phone numbers should now be real numbers. Select the column and then set a custom number format to "0000000000" (10 zeros) and it should display the leading zero and the long value for the lookup reference should work.


    Regards, OssieMac

    • Marked as answer by JC- SpeedCast Wednesday, May 16, 2012 11:45 PM
    Wednesday, May 16, 2012 7:01 AM
  • Thank you again.  Skill level relatively neophyte.  I just don't do enough of this often enough.  It can be a year between being required to write in VBA and then mix it up with SQL and SSRS in between.

    I came to the conclusion at the end of yesterday that my lookup range was at fault as you have.  I really appreciate your suggestions with making sure the values are in the correct format in Excel.  I had attempted to do this prior to writing the code but I am sure that Excel still basically believes the values to be text (the little green triangle a big hint I would say).  I had toyed with the idea of CStr when passing the variable to the lookup table, but it would be better to have the lookup range in the correct format.

    Regards Judi

    • Marked as answer by JC- SpeedCast Wednesday, May 16, 2012 11:45 PM
    • Unmarked as answer by JC- SpeedCast Wednesday, May 16, 2012 11:45 PM
    • Marked as answer by JC- SpeedCast Thursday, May 17, 2012 11:16 PM
    Wednesday, May 16, 2012 11:44 PM
  •  but I am sure that Excel still basically believes the values to be text (the little green triangle a big hint I would say).

    Have you managed to change the format to numeric? If not, if you have gone through the process of removing leading and trailing spaces and/or other non printable characters with the following

    =TRIM(CLEAN(C2)).    Copy the formula down and then select the column and Copy -> Paste Special -> Values and then copy and paste the column over the original phone numbers. The column with the formula can then be deleted.

    It will probably still be in text format so select the column and format Custom and set custom format to "0000000000" (10 zeros). This won't change to numeric immediately but then follow the next step.

    Select any BLANK CELL on the worksheet and enter 1 (numeric one). Copy the cell and then select the data (not the entire column) in the phone number column. Select Paste Special and then select multiply and OK. This should convert the data to numeric. (Multiply by one does not change the values but it will force the change into numeric format due to the maths operation on the data.)

    Hope this fixes the problem.



    Regards, OssieMac

    Thursday, May 17, 2012 5:18 AM
  • Again, thanks.  In the end I had to apply =VALUE to the numbers and then Paste Special - Values worked as it should.  Originally it would only paste them back as text and no application of any numerical formats would work. I also did as you suggested and have added the clean function to the code and applied it to the lookup values.

    So thanks to your assistance the lookup is working fantastically and I now only have a minor problem with the looping - another story :)

    Thursday, May 17, 2012 11:26 PM