Visual Basic....Calculate Button

  • Thread starter Thread starter sowetoddid
  • Start date Start date
S

sowetoddid

What would be the appropriate VB command to create a command button tha
completes the following calculations. Next to each row (41, 42, 43...
there can be a button labelled calculate. If the button correspondin
to row 41 is clicked, then its values will be changed. Otherwise the
will remain the same.

For example...to make it simple

GasEvents!N41=ReleaseWorksheet!H26
GasEvents!O41=ReleaseWorksheet!H14
GasEvents!P41=ReleaseWorksheet!H16
GasEvents!Q41=ReleaseWorksheet!H13
GasEvents!W41=ReleaseWorksheet!H40
GasEvents!X41=ReleaseWorksheet!H42
GasEvents!Z41=ReleaseWorksheet!H41
GasEvents!AA41=ReleaseWorksheet!H43

When I click calculate next to row 41, the values in column H should b
populated into the corresponding row 41 cells

When I click calculate next to row 42, the same cells in column H (wit
different numbers) should be populated into 42, without changing wha
is already in 41.

But, if I go back later and correct a mistake for row 41 calculations
I should still be able to click calculate and refill the row.


Hopefully that helps. It seems that would work if I could get help wit
the Visual Basic.

Thanks
 
Another question...

How would I designate which cells to calculate, using this command?
am not too familiar with manipulating this formula.

x=activecell.row
cells(x,"n")=whatever




This is my attempt...

Sub EnterFormula()

Worksheets("GasEvents").Range("N41").Formula="=ReleaseWorksheet!H26"
End Sub

(repeated for each cell in the row 41)

If I am doing that corrects, it would still cause the formula to sta
in the cells...which would cause the values of 41 to change each tim
new data is entered for row 42, 43, etc. I want row 41 to maintain i
values even after new data is inputted for row 42, 43, etc
 
Something like this should help and you will need NO buttons of any kind.
Right click on the sheet tab>view code>copy/paste this.Modify to suit.
As written, any number entry in column D which is column 4 will add 3 to the
number in col f.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
On Error GoTo doagain
Cells(Target.Row, "f") = Target + 3
doagain:
MsgBox "error-Enter a number"
Exit Sub
End Sub
 
Oops.. Forgot a line
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
On Error GoTo doagain
Cells(Target.Row, "f") = Target + 3
end
doagain:
MsgBox "error"
Exit Sub
End Sub
 
Thanks, Don!

Unfortunately, I am not yet advanced in visual basic code. So, can yo
help me to come up with the actual code that I would use, based on m
sample eqtns.

Looking at the code you gave me...

The points cells in column 'D' are inputted with values while at th
same time column 'F' adds three to each value and places them in colum
'F'.

My situation is similar but I would only use cell D1, for example.
would input the value 5 into D1 and want F1 to be populated with 5. A
a later time I would input 9 into D1 and want F2 to be populated wit
9. Also, I may choose input a 4 into D1 and want F1 to be updated t
this number.

D1 = 5 ---> F1 = 5
D1 = 9 ---> F2 = 9
If I made a mistake, I would want to go back and do

D1 = 4 ---> F1 = 4

That is why it seems a calculate button would be the way to go.

Thanks
 
If I understand correctly, just remove the +3 from target+3.....

You may send me a SMALL example workbook to the address below and I will
take a look.
 
Back
Top