# check box, vba and active cell

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

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.

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

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

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

### Welcome to Tech Support Guy!

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

over 733,556 other people just like you!

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