Help-Simple Sub
 

Hi,
After reading a book on VBA, I'm still having trouble creating a simple
Sub procedure. By pushing a button on sheet 2, I'm trying to have values
from the three cells next to it (A1,B1,C1) added to three cells
(A2,B2,C2) in sheet one. Each time the button is pushed on sheet 2,
those values will be added to the same location on sheet 1. I also want
to create an Undo button next to it. I can make this happen using cut
and paste, but I need to have it condensed into the simplest code. Any
help would be greatly appreciated!


--
New to VBA
------------------------------------------------------------------------
New to VBA's Profile: http://www.excelforum.com/member.php...o&userid=30032
View this thread: http://www.excelforum.com/showthread...hreadid=497191

Help-Simple Sub
 

Dim sh1 as Worksheet, sh2 as Worksheet
Dim cell as Range
set sh1 = Worksheets("Sheet1")
set sh2 = Worksheets("Sheet2")
for each cell in sh2.Range("A2:C2"_
cell.Value = cell.Value + sh1.Cells(1,cell.column).Value
Next

To Undo

set sh1 = Worksheets("Sheet1")
set sh2 = Worksheets("Sheet2")
for each cell in sh2.Range("A2:C2"_
cell.Value = cell.Value - sh1.Cells(1,cell.column).Value
Next

Tailor to fit your actual situation.

--
Regards,
Tom Ogilvy


"New to VBA" wrote
in message ...

Hi,
After reading a book on VBA, I'm still having trouble creating a simple
Sub procedure. By pushing a button on sheet 2, I'm trying to have values
from the three cells next to it (A1,B1,C1) added to three cells
(A2,B2,C2) in sheet one. Each time the button is pushed on sheet 2,
those values will be added to the same location on sheet 1. I also want
to create an Undo button next to it. I can make this happen using cut
and paste, but I need to have it condensed into the simplest code. Any
help would be greatly appreciated!


--
New to VBA
------------------------------------------------------------------------
New to VBA's Profile:
http://www.excelforum.com/member.php...o&userid=30032
View this thread: http://www.excelforum.com/showthread...hreadid=497191

Help-Simple Sub
 

Tom,
Thanks for the advice. I tried as shown below, but I receive a compile
error,syntax error message. Any thoughts? Thanks.

Sub AddCells1()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim cell As Range
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
for each cell in sh2.Range("A2:C2" _
cell.Value = cell.Value + sh1.Cells(1,cell.column).Value
Next
End Sub


--
New to VBA
------------------------------------------------------------------------
New to VBA's Profile: http://www.excelforum.com/member.php...o&userid=30032
View this thread: http://www.excelforum.com/showthread...hreadid=497191

Help-Simple Sub
 

Tom forgot a parenthesis in this line

for each cell in sh2.Range("A2:C2" _

should be

for each cell in sh2.Range("A2:C2") _

--
Regards,

Peo Sjoblom

(No private emails please)


"New to VBA" wrote
in message ...

Tom,
Thanks for the advice. I tried as shown below, but I receive a compile
error,syntax error message. Any thoughts? Thanks.

Sub AddCells1()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim cell As Range
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
for each cell in sh2.Range("A2:C2" _
cell.Value = cell.Value + sh1.Cells(1,cell.column).Value
Next
End Sub


--
New to VBA
------------------------------------------------------------------------
New to VBA's Profile:
http://www.excelforum.com/member.php...o&userid=30032
View this thread: http://www.excelforum.com/showthread...hreadid=497191

Help-Simple Sub
 

Peo,
I tried with the added ), still not working. I'm using Excel Version X.
maybe that is the problem?


--
New to VBA
------------------------------------------------------------------------
New to VBA's Profile: http://www.excelforum.com/member.php...o&userid=30032
View this thread: http://www.excelforum.com/showthread...hreadid=497191

Help-Simple Sub
 

I didn't forget a parenthesis. I had a typo.

Dim sh1 as Worksheet, sh2 as Worksheet
Dim cell as Range
set sh1 = Worksheets("Sheet1")
set sh2 = Worksheets("Sheet2")
for each cell in sh2.Range("A2:C2")
cell.Value = cell.Value + sh1.Cells(1,cell.column).Value
Next

Underline key is next to the right paren.

This is copied from a module where it compiled fine.

Option Explicit

Sub efg()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim cell As Range
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
For Each cell In sh2.Range("A2:C2")
cell.Value = cell.Value + _
sh1.Cells(1, cell.Column).Value
Next

End Sub

--
Regard

"New to VBA" wrote
in message ...

Peo,
I tried with the added ), still not working. I'm using Excel Version X.
maybe that is the problem?


--
New to VBA
------------------------------------------------------------------------
New to VBA's Profile:
http://www.excelforum.com/member.php...o&userid=30032
View this thread: http://www.excelforum.com/showthread...hreadid=497191