Code To Generate Data Table Based On Variables

  • Hello,
    I was trying to use the VBA code to generate a data table:


    and it kept giving me an input celll not valid error on this line:
    Selection.Table ColumnInput:=RefCell
    the thing is, I have defined RefCell as a range object, so it should be OK rite?
    Please help!


    Thanks!

  • Re: Code To Generate Data Table


    I think that "Selection" is activesheet dependent. So essentially, you are specifying a cell on a different sheet for your columninput argument. Perhaps you should define a variable of type range named TableRange and set it equal to the selection on sheet Calculator. Then you should be able to use TableRange.Table ColumnInput = RefCell. I've never dealt with a Range.Table object via VBA before so this is all a best guess on my part.


    hth,


    Mavyak

  • Re: Code To Generate Data Table


    I think these part also be changed.

    Code
    1. Set initialCell = ActiveSheet.Cells(initialRow, 1)
    2. Range(initialCell, initialCell.Offset(numRows, numCol)).Select '-------->make range
    3. Selection.Table ColumnInput:=RefCell '------->it seems that RefCell can't be other sheet range


    Regards/Junho Lee

  • If you wish to continue using this free service.


    Please be considerate of others who use the forum for searching. Your current Thread Title (which I will change) is non-reflective of what you are wanting to do.


    In future, please take 1 minute of your time to read the text on the New Thread page.


    [fa]*[/fa]


    REMEMBER: Your thread title should NEVER be what you THINK is your answer. 9 times out 10 it will wrong and prevent someone from finding a solution to a simliar issue.

  • Re: Code To Generate Data Table Based On Variables


    ok, tested and confirmed, there is no way a data table could take a reference cell on another sheet...
    And my work around is actually to reference the other way round, i.e., set up the formula so that the actual calculator sheet would find the ID number from the data table, which worked for me as well.
    Hope this helps for anyone who have similar problems. Thanks!