Quoting MarEll:

Quote:
Anyone know how I can add the values one column another using VBA?

What I want to have are values in column B that never get touched manually and upon clicking a button the values in column C would be added to their adjacent cells in column B.

Did you try the Macro Recorder? My undersatnding is that if you turn it on, then do the steps manually, it will create the VBA for you. 

@xyrano: interesting. I thought by "added", he meant numerical addition (b+c = sum) ???

Quoting xyrano:

Quote:
Quoting mediadogg:

Quote:
@xyrano: interesting. I thought by "added", he meant numerical addition (b+c = sum) ???

Hihihi, I got into Macro since you mentioned it Anyway, if (b+c = sum) is what he is looking for then he don't need macros.

Simply, select the cell in column D and hit the Sum icon in the toolbar, then put the mouse cursor in the lower right corner of the same cell and double click to copy the function downwards.

Right, but I think he wants to do it with a button that executes VBA. My point about the Macro Editor, is that if you do the the steps you outlined, it will write the VBA code for you, which is what I think he was asking for???? (Obviously I don't know VBA or I would have done what you did - just write it for him.)

Generally speaking, if you know the number of rows, then you can simply write a for loop, using your counter as a double for the cell reference number.  In VBA, it might be a do...while loop, but the same principle applies.  If the number of rows will vary, then some condition up top will be required to determine the bottom of the data.  Kind of a do while not null sort of thing with an incrementing counter.

In VBA, it looks like:

Dim i As Integer

i = 1

Do While Worksheets("Sheet1").Cells(i, 1).Value <> 0

    Worksheets("Sheet1").Cells(i, 1).Value = Worksheets("Sheet1").Cells(i, 1).Value + Worksheets("Sheet1").Cells(i, 2).Value

    i = i + 1

Loop

This would add the contents of the cells in column A and the cells in column B and dump them in column A.  It ends when it encounters a 0 in column A, which is the equivalent of a blank cell.  Probably not perfect with regards to the end condition, but you can tweak it from there.  If you have a set number of rows, then it's simply do while i <> last row number+1.  Just attach the code to a button on the form.

In order to add a button to the spreadsheet, you have to check off the developer tab in excel options and then choose the insert tab which will give you a choice of controls.  Once you do that, the assign macro dialog pops up, with the choices of new or record macro.  Pick new and paste the code into the button click event.

If you get a type mismatch, that implies that you are trying to add two different data types, an obvious problem.  Try it first on a blank spreadsheet to confirm that it works in general.  If so, then go back and see what you column formats are to make sure there isn't some obvious mismatch.  Also note that "sheet1" is explicitely naming a sheet.  If you've renamed that sheet to be something more descriptive, then you'll have to modify the code accordingly.

I just simply created a new spreadsheet and entered the data:

1      2
3      4

and then added the button, then the code and ran it.  Works just fine for me.  I should probably also add that the worksheets line is a single line and the next new line is the i = i + 1.  Sometimes the formatting on my screen may not match the formatting on your screen due to screen resolution and the way the webpage displays.

Quoting mdnitoil:

Quote:
If you get a type mismatch, that implies that you are trying to add two different data types, an obvious problem.  Try it first on a blank spreadsheet to confirm that it works in general.  If so, then go back and see what you column formats are to make sure there isn't some obvious mismatch.  Also note that "sheet1" is explicitely naming a sheet.  If you've renamed that sheet to be something more descriptive, then you'll have to modify the code accordingly.

I just simply created a new spreadsheet and entered the data:

1      2
3      4

and then added the button, then the code and ran it.  Works just fine for me.  I should probably also add that the worksheets line is a single line and the next new line is the i = i + 1.  Sometimes the formatting on my screen may not match the formatting on your screen due to screen resolution and the way the webpage displays.

Yep.  Works in a blank workbook, must be something I did wrong.

Quoting MarEll:

Quote:
Quoting xyrano:

Quote:
I've got a working solution for this particullar problem using named ranges (no sheetnames or explicit cell references in VBA, also working for an entire column) which makes it dynamic.
Apart from addition, I also made it so a numreric or currency value can be subtracted, multiplied or divided.
I made it in an Macro Enabled Workbook for 2007-2010.

PM me your email and your Excel version (if other then 2007-2010) and I'll send it to you, if you are interested.

I've actually just noticed that in the paste special window you get the option to 'add' the values to the paste destination rather than just replace them.  Can't believe I've never spotted that before 

I'm still interested to see what you've cooked up though as I could use it for other things that a simple copy paste wouldn't work for.  YGPM

Yea, I've been doing stuff with it for x-years and I still find new stuff :D

YGM