HLOOKUP with increasing row index number

Question

Currently working on a way to export data from one program and import the data to a CRM using VBA.

Using HLOOKUP, I'm able to consolidate several columns into one "Contact Notes" section for each row of data.

The HLOOKUP formula (below) is what works to consolidate the information:

=$AU$3&": "&
  HLOOKUP($AU$3,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$4&": "&
  HLOOKUP($AU$4,listing_info,2+AS2,FALSE )&
  CHAR(10)&$AU$5&": "&
  TEXT(HLOOKUP($AU$5,listing_info,2+AS2,FALSE ),"mmmm dd, yyyy")&
  CHAR(10)&$AU$6&": "&
  TEXT(HLOOKUP($AU$6,listing_info,2+AS2,FALSE),"$0,000")&
  CHAR(10)&$AU$7&": "&
  HLOOKUP($AU$7,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$8&": "&
  TEXT(HLOOKUP($AU$8,listing_info,2+AS2,FALSE ),"mmmm dd, yyyy")&
  CHAR(10)&$AU$9&": "&
  TEXT(HLOOKUP($AU$9,listing_info,2+AS2,FALSE ),"mmmm dd, yyyy")&
  CHAR(10)&$AU$10&": "&
  TEXT(HLOOKUP($AU$10,listing_info,2+AS2,FALSE),"mmmm dd, yyyy")&
  CHAR(10)&$AU$11&": "&
  TEXT(HLOOKUP($AU$11,listing_info,2+AS2,FALSE ),"mmmm dd, yyyy")&
  CHAR(10)&$AU$12&": "&
  HLOOKUP($AU$12,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$13&": "&HLOOKUP($AU$13,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$14&": "&HLOOKUP($AU$14,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$15&": "&HLOOKUP($AU$15,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$16&": "&HLOOKUP($AU$16,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$17&": "&HLOOKUP($AU$17,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$18&": "&HLOOKUP($AU$18,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$19&": "&HLOOKUP($AU$19,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$20&": "&HLOOKUP($AU$20,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$21&": "&HLOOKUP($AU$21,listing_info,2+AS2,FALSE)&
  CHAR(10)&$AU$22&": "&HLOOKUP($AU$22,listing_info,2+AS2,FALSE)

Column AS has an index row that increases by 1 and starts at zero.
Column AT is where the HLOOKUP formula is stored.
Column AU is where the lookup values are stored.

Recording the macro for the HLOOKUP resulted in the following code:

ActiveCell.FormulaR1C1 = "=HLOOKUP(RC[-2],listing_info,RC[-4],FALSE)"

How do I fill a column with the HLOOKUP code and increase the row index number?

@Jeeped Never heard of textjoin. I'll look into it as a solution. Would this remove the need to use hlookup? — Abraham Walker, Mar 27 '18 at 15:48
No but it should remove the need to use multiple hlookups if you code it correctly. — , Mar 27 '18 at 15:50
@Jeeped Would I need to use named ranges to ensure the proper information is retrieved? — Abraham Walker, Mar 27 '18 at 15:51
`TEXTJOIN` only became available in Excel 2016. What version of Excel are you running, @AbrahamWalker? — TotsieMae, Mar 27 '18 at 16:13
@TotsieMae I'm running the latest version through my Microsoft subscription. Thanks for letting me know that this is a relatively new feature. — Abraham Walker, Mar 27 '18 at 16:30
Not sure if I understand the issue, but look up "structured reference". Basically, format the range as table (form Home or Insert tab), and then you can refer to fields by their column name instead of cell location. Also, formulas can autocomplete, so that they are automatically added when new rows are added. https://support.office.com/en-us/article/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e — Slai, Mar 31 '18 at 18:07

Abraham Walker · Answer 1 · 2018-03-31T17:36:27.990

I'll warn you now, this is ugly and it works. Now that I look at this, I don't believe I needed to add the Lookup Values to the spreadsheet. I could have done this inside the code.

I also named the range I'm working on "listing_info". This step is kinda of important.

I'm still working towards a solution for data the exceeds 50 records but this will do for now.

`Sub HLookup_Process_Data_To_New_CRM()

    'This is where I Input the increasing row index numbers
    Range("AS2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("AS3").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("AS4").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("AS2:AS4").Select
    Selection.AutoFill Destination:=Range("AS2:AS51"), Type:=xlFillDefault

    'Adding my Lookup Values
    Range("AS2:AS51").Select
    Range("AU2").Select
    ActiveCell.FormulaR1C1 = "Address"
    Range("AU3").Select
    ActiveCell.FormulaR1C1 = "Address 2"
    Range("AU4").Select
    ActiveCell.FormulaR1C1 = "List Date"
    Range("AU5").Select
    ActiveCell.FormulaR1C1 = "List Price"
    Range("AU6").Select
    ActiveCell.FormulaR1C1 = "Days On Market"
    Range("AU7").Select
    ActiveCell.FormulaR1C1 = "Lead Date"
    Range("AU8").Select
    ActiveCell.FormulaR1C1 = "Expired Date"
    Range("AU9").Select
    ActiveCell.FormulaR1C1 = "Withdrawn Date"
    Range("AU10").Select
    ActiveCell.FormulaR1C1 = "Status Date"
    Range("AU11").Select
    ActiveCell.FormulaR1C1 = "Listing Agent"
    Range("AU12").Select
    ActiveCell.FormulaR1C1 = "Listing Broker"
    Range("AU13").Select
    ActiveCell.FormulaR1C1 = "MLS/FSBO ID"
    Range("AU14").Select
    ActiveCell.FormulaR1C1 = "Bedrooms"
    Range("AU15").Select
    ActiveCell.FormulaR1C1 = "Bathrooms"
    Range("AU16").Select
    ActiveCell.FormulaR1C1 = "Type"
    Range("AU17").Select
    ActiveCell.FormulaR1C1 = "Square Footage"
    Range("AU18").Select
    ActiveCell.FormulaR1C1 = "Year Built"
    Range("AU19").Select
    ActiveCell.FormulaR1C1 = "Remarks"
    Range("AU20").Select
    ActiveCell.FormulaR1C1 = "Notes"
    Range("AU21").Select
    ActiveCell.FormulaR1C1 = "Folders"


    'My original formula exceeded the VBA length and I divide it into six pieces and use Concat to glue the pieces back together.
    Range("AW2").Select
    ActiveCell.FormulaR1C1 = _
        "=R2C47&"": ""&HLOOKUP(R2C47,listing_info,2+RC[-4],FALSE )&CHAR(10)&R3C47&"": ""&HLOOKUP(R3C47,listing_info,2+RC[-4],FALSE )&CHAR(10)&R4C47&"": ""&TEXT(HLOOKUP(R4C47,listing_info,2+RC[-4],FALSE ),""mmmm dd, yyyy"")"

    Range("AX2").Select
    ActiveCell.FormulaR1C1 = _
    "=CHAR(10)&R5C47&"": ""&TEXT(HLOOKUP(R5C47,listing_info,2+RC[-5],FALSE ),""$0,000"")&CHAR(10)&R6C47&"": ""&HLOOKUP(R6C47,listing_info,2+RC[-5],FALSE )&CHAR(10)&R7C47&"": ""&TEXT(HLOOKUP(R7C47,listing_info,2+RC[-5],FALSE ),""mmmm dd, yyyy"")"

    Range("AY2").Select
    ActiveCell.FormulaR1C1 = _
    "=CHAR(10)&R8C47&"": ""&TEXT(HLOOKUP(R8C47,listing_info,2+RC[-6],FALSE ),""mmmm dd, yyyy"")&CHAR(10)&R9C47&"": ""&TEXT(HLOOKUP(R9C47,listing_info,2+RC[-6],FALSE ),""mmmm dd, yyyy"")&CHAR(10)&R10C47&"": ""&TEXT(HLOOKUP(R10C47,listing_info,2+RC[-6],FALSE ),""mmmm dd, yyyy"")"

    Range("AZ2").Select
    ActiveCell.FormulaR1C1 = _
    "=CHAR(10)&R11C47&"": ""&HLOOKUP(R11C47,listing_info,2+RC[-7],FALSE )&CHAR(10)&R12C47&"": ""&HLOOKUP(R12C47,listing_info,2+RC[-7],FALSE )&CHAR(10)&R13C47&"": ""&HLOOKUP(R13C47,listing_info,2+RC[-7],FALSE )&CHAR(10)&R14C47&"": ""&HLOOKUP(R14C47,listing_info,2+RC[-7],FALSE )"

    Range("BA2").Select
    ActiveCell.FormulaR1C1 = _
    "=CHAR(10)&R15C47&"": ""&HLOOKUP(R15C47,listing_info,2+RC[-8],FALSE )&CHAR(10)&R16C47&"": ""&HLOOKUP(R16C47,listing_info,2+RC[-8],FALSE )&CHAR(10)&R17C47&"": ""&HLOOKUP(R17C47,listing_info,2+RC[-8],FALSE )&CHAR(10)&R18C47&"": ""&HLOOKUP(R18C47,listing_info,2+RC[-8],FALSE )"

    Range("BB2").Select
    ActiveCell.FormulaR1C1 = _
    "=CHAR(10)&R19C47&"": ""&HLOOKUP(R19C47,listing_info,2+RC[-8],FALSE )&CHAR(10)&R20C47&"": ""&HLOOKUP(R20C47,listing_info,2+RC[-8],FALSE )&CHAR(10)&R21C47&"": ""&HLOOKUP(R21C47,listing_info,2+RC[-8],FALSE)"

    Range("BC1").FormulaR1C1 = "Contact Notes"
    Range("BC2").Select
    ActiveCell.FormulaR1C1 = "=CONCAT(RC[-6]:RC[-1])"

    'Auto fill down a predetermined distance. I will only add 50 records at a time. I still need to figure out how to do this with an unlimited number of records.
    Range("AW2:BC2").Select
    Selection.AutoFill Destination:=Range("AW2:BC51"), Type:=xlFillDefault

    'Copying and Pasting only the values    
    Range("BC2:BC51").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

    'Deleting everything I just did    
    Columns("AS:BB").Select
     Selection.Delete Shift:=xlToLeft


 End Sub

Update 1: I can shorten this code by passing a string to the formula versus having the code type the formula. This will cut down a few seconds on the processing of the code for this project and it could save time on larger data transfers.

This question shows how to pass a string to HLOOKUP:

Pass variables into VLOOKUP in VBA

Since this code requires a "listing_info" as a named range, I use the following links to create code that:

Name the Active.Worksheet (Andrew Poulsom's solution) https://www.mrexcel.com/forum/excel-questions/51686-vba-code-name-worksheet.html
Find the last row and column (Siddharth Rout's Answer) How can I find last row that contains data in the Excel sheet with a macro?
Used the active area to name a range (I modified example #4) https://powerspreadsheets.com/vba-create-named-range/

HLOOKUP with increasing row index number

1 Answers1