# Thread: Combining cells in Excel (Excel 2000)

1. ## Combining cells in Excel (Excel 2000)

Hello all,

This is probably an easy one but my feeble brain is having trouble so here goes...

I have a row of data that describes the cost to purchase or manufacture a part. The data is organized into columns such as quantity, material cost, manufacturing hours, etc. The contents of these cells are simple number, not formulas. What I need to do is convert the numbers in the material cost and other columns to be formulas that are the present value times the quantity. The objective is that when the quantity is changed all of the other cells in the row will change accordingly.

As their are hundreds of rows that need to be updated I am hoping for some enlightenment on a macro to automate the process.

All help is gratefully acknowledged.

John Davis

2. ## Re: Combining cells in Excel (Excel 2000)

John

OK here is an example, if you had given us the columns where the data is, very usefull, we could have given you the exact formula.

Say you have hours worked in column A and Salary Rate in Column B in Column C you want the formula to calculate gross pay which is Hours worked X Salary rate per hour

=A2*B2 will multiply A2 by B2, then you can fill down this formula and you are done. To fill down you look at the lower right hand corner of the cell when it is selected and you will see a black square. Grab this black square and pull down as far as you have data. Make sure you hover the mouse pointer until the cursor changes into a black Plus sign.

A macro is not needed here, unless you have something in mind that I missed, very possible.

Wassim

3. ## Re: Combining cells in Excel (Excel 2000)

Thanks for the reply; but it's not quite what I'm looking for.

In the attached sample file you can see what I have to work with. What I need is to be able to enter a new value in the Quantity column (Column [img]/forums/images/smilies/cool.gif[/img] and have all of the other columns update accordingly. So if the quantity becomes 2 all of the values in the other columns double.

Put another way, cell E5 is currenlty "80". What I would like it to be is "=80*E5". I'm looking for a way to do this automagically as there are hundreds of rows that need to be updated in this way.

John Davis

4. ## Re: Combining cells in Excel (Excel 2000)

John

Check the Red Cells for the formula.

Then Check the Fill handle behavior. This will let you do this "hundreds of rows" in a jiffy. Just grab it and pull down as much as you can, work it slow first so that you see what is going on.

Wassim

5. ## Re: Combining cells in Excel (Excel 2000)

Thanks again Wassim. Maybe I'm not explaining this clearly. I want to REPLACE the numbers that are currently in the cells with the formula.

John

6. ## Re: Combining cells in Excel (Excel 2000)

The VBA routine below will take all of the cells in the current selection and replace ther contents with a formula that multiplies the current contents by the cell in column B in the same row. Is that what you are looking for?

<pre>Public Sub InsertFormula()
Dim oCell As Range
For Each oCell In Selection
oCell.Formula = "=" & oCell.Value & "*B" & oCell.Row
Next oCell
End Sub
</pre>

7. ## Re: Combining cells in Excel (Excel 2000)

John

Now that I have a sample, how about telling me what numbers in what cells do you want to replace.
For example, cell D5 = 200 what happens.

Did you check the effect of the Fill Handle?

Why do you have 3 blank rows on the top?

Wassim

8. ## Re: Combining cells in Excel (Excel 2000)

John

Now that I have a sample, how about telling me what numbers in what cells do you want to replace.
For example, cell D5 = 200 what happens to that cell?

Did you check the effect of the Fill Handle?

Why do you have 3 blank rows on the top?

Wassim

9. ## Re: Combining cells in Excel (Excel 2000)

Yep. This is what I am trying to do. The code that you sent works as long as there are values in every cell; which is MOSTLY, but not alwyas true. I can stick zeros into the empty cells and it will work great.

Thanks!

John

10. ## Re: Combining cells in Excel (Excel 2000)

If you have empty cells in the range, then you could change the macro to this:

<pre>Public Sub InsertFormula()
Dim oCell As Range
For Each oCell In Selection
If oCell.Value <> "" Then
oCell.Formula = "=" & oCell.Value & "*B" & oCell.Row
End If
Next oCell
End Sub
</pre>

11. ## Re: Combining cells in Excel (Excel 2000)

AWESOME!! Thank you!

John

12. ## Re: Combining cells in Excel (Excel 2000)

This little macro has been working great. I need to take it to the next level and automate it so that it executes whenever the content of a cell in a defined range changes. How might I do this?

Thanks again.

John

13. ## Re: Combining cells in Excel (Excel 2000)

If the "defined range" is named DefinedRange, then something like this should do what you want:

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Worksheets("Sheet1").Range("DefinedRange")) Is Nothing Then
For Each oCell In Intersect(Target, Range("DefinedRange"))
If oCell.Value <> "" Then
Application.EnableEvents = False
oCell.Formula = "=" & oCell.Value & "*B" & oCell.Row
Application.EnableEvents = True
End If
Next oCell
End If
End Sub
</pre>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•