+ Reply to Thread
Results 1 to 4 of 4

Can Range("xxx") be dynamic instead of static valuel?

  1. #1
    Registered User
    Join Date
    06-09-2016
    Location
    Warsaw
    MS-Off Ver
    2013
    Posts
    8

    Unhappy Can Range("xxx") be dynamic instead of static valuel?

    Hi everyone,

    Im trying to solve this puzzle, and because im not that skilled with VBA, i do not know if it is even possible.

    What im trying to achieve is to find a way how could i modify below code, in order it can be dynamic and not static, when it comes to final destination to which value is copied to.

    For example:


    Please Login or Register  to view this content.
    This code is copying H27 in to B352 located on different tab. Thing is, that this process is based on assumption that elements located on "Hardware Data Base" tab are always in the same place. But if someone would remove or add 1 line to this list, then original location of B352 cell will be moved.

    So im using MATCH function next to each cell at "Bundle Selections" to show what is the cell number on the other tab, this is some kind of control check. As You can see my "destination" cell to which im copying H27 is static - written in the code, but what im looking for is to find a way to make it dynamic.

    Instead of B352 i need to have there something like :

    Range("BMATCH(F27;'Hardware Data Base'!D:D;0)").Select so ech time that Match will return new row number, it will automatically include it in to VBA code with column B.

    Is that even possible? : /
    Last edited by BOYAR; 11-25-2016 at 09:58 AM. Reason: solved :)

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Can Range("xxx") be dynamic instead of static valuel?


    Hi !

    MATCH Excel worksheet function can be used in VBA via Application.Match and checked via IsError or IsNumeric functions …

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Can Range("xxx") be dynamic instead of static valuel?

    Perhaps.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    06-09-2016
    Location
    Warsaw
    MS-Off Ver
    2013
    Posts
    8

    Re: Can Range("xxx") be dynamic instead of static valuel?

    Ok i have found solution

    Please Login or Register  to view this content.
    So now Range is dynamic and its taking row number from automated-check value in the column M which is looking for Serial number match on another tab and returning its number so VBA code knows where to copy Ixx information (meanwhile i have changed H to I)

    Edit:


    Zmienna = Application.Match([F8].Value, Worksheets("Hardware Data Base").Columns(4), 0)

    So now i can remove reference to M column for excel MATCH function and i can do it directly in VBA with this line for each new "zmienna" just need to change Fx cell number but this one is always static and will not be changed after
    Last edited by BOYAR; 11-25-2016 at 10:16 AM.

+ 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. Replies: 1
    Last Post: 03-09-2016, 12:17 PM
  2. [SOLVED] Calculating a percentage of "Y" and "N" in dynamic cell range
    By InkyDrinky in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-30-2016, 01:35 PM
  3. Replies: 5
    Last Post: 01-23-2014, 11:02 AM
  4. How to change "From" & to have a dynamic "Subject" line in Excel Mailing
    By andy_iyeng in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-25-2012, 03:04 AM
  5. "Static" Behavior without Static Defn
    By Steve Drenker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-01-2006, 09:35 PM
  6. use variable in Workbooks("book1").Worksheets("sheet1").Range("a1"
    By Luc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2005, 04:05 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