Using variable cell references

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • if1467
    New Member
    • Feb 2008
    • 25

    Using variable cell references

    I am trying to place a formula in a variable cell using referances that are also variable. Here is what I have so far:

    Dim ShrCst As String

    Code:
    ShrCst = "=" & Cells(rownum, 6) & "/" & Cells(rownum, 4) & "*100"
    ActiveSheet.Cells(rownum, 5) = ShrCst
    rownum is a previously set variable.
    My problem is that it is outputing the value of the cells (ex. Cells(rownum,6) ). I need it to output the referance like F7.

    Thanks in advance for any help
  • kadghar
    Recognized Expert Top Contributor
    • Apr 2007
    • 1302

    #2
    Originally posted by if1467
    I am trying to place a formula in a variable cell using referances that are also variable. Here is what I have so far:

    Dim ShrCst As String

    Code:
    ShrCst = "=" & Cells(rownum, 6) & "/" & Cells(rownum, 4) & "*100"
    ActiveSheet.Cells(rownum, 5) = ShrCst
    rownum is a previously set variable.
    My problem is that it is outputing the value of the cells (ex. Cells(rownum,6) ). I need it to output the referance like F7.

    Thanks in advance for any help
    Try using FormulaRC, it doesnt have that kind of issues.

    or create a function that changes the Row,Col, into an Excel's cell:

    [CODE=vb]public function MyCells(byval a as long, byval b as integer) as string
    dim i as integer
    while b > 26
    b=b-26: i = i+1
    wend
    if i > 0 then mycells= chr(i+64)
    mycells=mycells & chr(b + 64) & a
    end function[/CODE]

    this way, your code will be:
    [CODE=vb]ShrCst = "=" & MyCells(rownum, 6) & "/" & MyCells(rownum, 4) & "*100"
    ActiveSheet.Cel ls(rownum, 5) = ShrCst[/CODE]

    HTH

    Comment

    • Killer42
      Recognized Expert Expert
      • Oct 2006
      • 8429

      #3
      Just a quick note - the word is reference, not referance.

      Comment

      • if1467
        New Member
        • Feb 2008
        • 25

        #4
        Originally posted by kadghar
        Try using FormulaRC, it doesnt have that kind of issues.

        or create a function that changes the Row,Col, into an Excel's cell:

        [CODE=vb]public function MyCells(byval a as long, byval b as integer) as string
        dim i as integer
        while b > 26
        b=b-26: i = i+1
        wend
        if i > 0 then mycells= chr(i+64)
        mycells=mycells & chr(b + 64) & a
        end function[/CODE]

        this way, your code will be:
        [CODE=vb]ShrCst = "=" & MyCells(rownum, 6) & "/" & MyCells(rownum, 4) & "*100"
        ActiveSheet.Cel ls(rownum, 5) = ShrCst[/CODE]

        HTH

        Any way I could get this so that the in the formula bar it shows the referEnce instead of the value?

        Comment

        • kadghar
          Recognized Expert Top Contributor
          • Apr 2007
          • 1302

          #5
          Originally posted by if1467
          Any way I could get this so that the in the formula bar it shows the referEnce instead of the value?
          yes, write the function i gave you at the begining or at the end of your code, so you can use it

          and then replace the line you had, with the one i gave you (as you can see, it doesnt use CELLS it uses MyCells, the function we added).

          And yes, it'll write the references in the formula instead of the values.

          ^.^

          And im glad you appreciate the grammar corrections. I like them too, since English is not my first language and i need to learn and practice it more.

          HTH

          Comment

          Working...