none
VBA Dynamic / Adaptive Cell Range RRS feed

  • Question

  • I'm trying to get it to adapt to the number of cells because I'm going to be using it with sales orders and will not have a specific range on a daily basis.  I only want it to work with cells that are used to improve efficiency. This is what I currently have for the cell and I'm unsure of where to go from here:

    Sub RenameItem()

        'need to detect and use only cells with text

    >>>For Each Item In Range(Range("H2"), Range("H2").End(xlDown)).Select


    If Left(Item.Value, 3) = "PCH" Then Item.Value = "Pouch"
    If Left(Item.Value, 2) = "MK" Then Item.Value = "Maverick"
    If Left(Item.Value, 2) = "BT" Then Item.Value = "Vision"
    If Left(Item.Value, 4) = "HLMH" Then Item.Value = "Helmet"
    If Left(Item.Value, 5) = "10022" Then Item.Value = "Plate"
    If Left(Item.Value, 2) = "DL" Then Item.Value = "Shield"

    Next Item


    End Sub

    Wednesday, May 13, 2015 2:35 PM

Answers

  • Hi A_Shannon828,

    Accoding to the description, you want to select the range dynamic. We dont't need to select the range to use for each, here is the correct code for your reference:

    For Each Item In Range(Range("H2"), Range("H2").End(xlDown))

    In addtion, to get the correct last row of specific column, I suggest that you select the last row in the column then use Range.End(xlUp) to reach the last row.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 15, 2015 3:21 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Thursday, May 14, 2015 7:14 AM
  • Hi A_Shannon828,

    Accoding to the description, you want to select the range dynamic. We dont't need to select the range to use for each, here is the correct code for your reference:

    For Each Item In Range(Range("H2"), Range("H2").End(xlDown))

    In addtion, to get the correct last row of specific column, I suggest that you select the last row in the column then use Range.End(xlUp) to reach the last row.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 15, 2015 3:21 AM
    Moderator