1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

check box, vba and active cell

Discussion in 'Business Applications' started by nomad, Jan 8, 2019.

Advertisement
  1. nomad

    nomad Thread Starter

    Joined:
    Sep 30, 1999
    Messages:
    386
    I would like to put ther result of a calculation in the active cell (cursor location) by checking a box. So what ever the active cell is i would like to go up 1 row and 3 columns to the left and multiply this value time 2 then go 1 column to the right of that and times that cell by two then take the sum of those 2 cells and divide them by 12 and have the answer put in the active cell when the check box is true. The check box and linked cell can be anywhere as i think i can extrapolate once it gets that far. Here are a couple of samples so as to make it more understandable.

    active cell D5 = ((B4*2)+(C4*2))/12
    active cell D6 = ((B5*2)+(C5*2))/12
    active cell D7 = ((B6*2)+(C6*2))/12

    I hope this is understandable. does anyone if this is possilbe to do cosidering the active cell may be different everytime.

    thanx
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    You can use a couple of variables to establish where the active cell is and then use the Cells(row,column) in your fomula.
    Something like
    Dim rownum as integer, colnum as integer
    rownum = activecell.row
    colnum = activecell.column
    activecell = (cell(rownum -1, colnum -3)*2+cell(rownum-1, colnum -2)*2)/12

    I haven''t tested this and have created it from memory, so you may tweek it a bit.
     
  3. nomad

    nomad Thread Starter

    Joined:
    Sep 30, 1999
    Messages:
    386
    this doesn't seem to work and i dont know how to fix it.... any ideas?
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    Sorry, mispelt cells as cell

    Try this instead

    Dim rownum As Integer, colnum As Integer
    On Error GoTo errorcatch
    rownum = ActiveCell.Row
    colnum = ActiveCell.Column
    MsgBox rownum & " " & colnum
    ActiveCell = (Cells(rownum - 1, colnum - 3) * 2 + Cells(rownum - 1, colnum - 2) * 2) / 12
    Exit Sub
    errorcatch:
    MsgBox Err.Description


    it should tell you what row & column you are on and then put the calculated value in.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...

Short URL to this thread: https://techguy.org/1221589

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice