+ Reply to Thread
Results 1 to 5 of 5

Currently using Indirect - want to replace with VBA

  1. #1
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    657

    Currently using Indirect - want to replace with VBA

    Please Login or Register  to view this content.
    This returns the correct range for my index. It basically sets the index range to look in the worksheet from the first instance of a value (that is in A142) to the last instance. So If A142 is on the worksheet 4 times, and the first time it shows up is row 100 - the range is set for AccesoriesCust!H100:I104

    BUT that gives thousands of Indirect() - need to have VBA .function
    Please Login or Register  to view this content.
    etc...
    If I could get the find to work that would give the start of the range, and add a count of the number of A142's in the range would give the end of the range.

    Any help would be appreciated.
    If this solves your issue, Mark thread as Solved & Add Reputation.

    Thanks!

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,109

    Re: Currently using Indirect - want to replace with VBA

    If you plan to go into custom vba function, it may be a good idea to prepare a final function, not a part to be used with index.

    By the way, it would be probably much easier to propose something with a sample file available
    Best Regards,

    Kaper

  3. #3
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    657

    Re: Currently using Indirect - want to replace with VBA

    Thank you. I am attaching. If you run the macro (only macro in sheet) It does exactly what I want. EXCEPT on the last worksheet Column E & F instead of
    Please Login or Register  to view this content.
    I would like the formula to read
    Please Login or Register  to view this content.
    Using different range for each group
    Attached Files Attached Files
    Last edited by CRIMEDOG; 06-23-2022 at 10:24 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    657

    Re: Currently using Indirect - want to replace with VBA

    This is what I have:
    Please Login or Register  to view this content.
    It is working perfectly within my Test spreadsheet but copied into my whow worksheet I am getting object variable not set on this row:
    Please Login or Register  to view this content.
    I have Dim VarRow As Long and Dim VarRange As Range

  5. #5
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    657

    Re: Currently using Indirect - want to replace with VBA

    Got it working.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replace INDIRECT in formula
    By tjebme in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 02-06-2021, 02:32 PM
  2. Replace indirect formula
    By helpmeplz_ in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-10-2018, 06:13 PM
  3. Replace Indirect() in formula
    By CRIMEDOG in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-30-2018, 04:52 PM
  4. find and replace for indirect
    By ammartino44 in forum Excel General
    Replies: 6
    Last Post: 06-14-2015, 02:05 AM
  5. Replace SUMIFS with INDIRECT
    By MrPeterified in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2013, 05:19 PM
  6. [SOLVED] Use indirect to replace tab name.
    By narrowgate88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-02-2013, 04:17 PM
  7. Replace =INDIRECT
    By BRISBANEBOB in forum Excel General
    Replies: 4
    Last Post: 12-27-2011, 04:38 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1