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.

nomad

Thread Starter
Joined
Sep 30, 1999
Messages
397
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

Joined
Mar 8, 2005
Messages
19,895
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.
 

nomad

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

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
 
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top