# check box, vba and active cell

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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

#### OBP

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.

this doesn't seem to work and i dont know how to fix it.... any ideas?

#### OBP

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.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On