Using Macros To Divide Cells In A Table

[GovNewbi]

Ok so I am working in excel 2007 and I am trying to write a macro that reads a certain cell, if the cell is true it will divide all cells in a table I have created by a number found in another cell. There is one line in the code that will not run for me. I keep getting error 13. It looks like this...

Range("FetchTable").Value = Range("FetchTable").Value / Range("DivVal").Value

I have tried several variations of this, removing the .Value and such. Nothing seems to work and I can't find the proper way to write it. Help!

 

[SkipVought]

Hi,

At the point of error...

Are these objects all on the active sheet?

What reference is Range("FetchTable")?

What reference is Range("DivVal")?

What VALUE(s) are in Range("FetchTable").Value?

What VALUE is Range("DivVal").Value?

Please answer each of these questions.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

I am 99% sure they are on the active sheet. There is another sheet that is opened but it is closed before this line.

FetchTable is a name I gave to the range within the table (where I want the values to be divided if the user inputs true). It is all of a table minus the first row and column which are the titles.

DivVal is also a named cell where there will be a number I want the values in the table divided by.

FetchTable values are currency.

DivVal values are General.

 

[SkipVought]

Range("DivVal").Copy
Range("FetchTable").PasteSpecial , xlPasteSpecialOperationDivide

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You could discover the code by turning on your macro recorder and recording the COPY and Edit > Paste Special operations.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

I had no idea that would be inder a copy and paste. Thanks

 

[GovNewbi]

What about in this case...

wb.Worksheets("Sheet1").Range(CopyVal).Copy

If I wanted the CopyVal piece to be the sum of a range of numbers. How would I set that?

 

[SkipVought]

You can answer your own question.

Try it first manually on a sheet, using a test range and a test value to COPY to use the ADD operation in Paste Special.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

That isn't working. When I use the .PasteSpecial , xlPasteSpecialOperationAdd I am just getting the entire list pasted. Same story with the testers I am running.

 

[SkipVought]

Are you just trying to SUM the values in the CopyVal range?

Or are you tryning to ADD a value to the CopyVal range?

Or is it something else?

The SUM is simply...

MsgBox Application.SUM(wb.Worksheets("Sheet1").Range(CopyVal))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

There is a list of values in another workbook. I want to take these values... sum them... and have that sum = CopyVal so that it is the total that is being pasted into the active workbook.

 

[SkipVought]

So have you got it figured out?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

No. I'm completely confused

 

[SkipVought]

Use the SUM function to aggregate the value in the appropriate cell ON THE SHEET. You do not need VBA to do that.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

Yes I know that. The problem is that won't work in this case. It's difficult to explain because there is a lot to it but I will give it a go...

I have a table called FetchTable. It is full of values imported from other workbooks (I cannot touch these other workbooks or change a thing in them). I had a code that would input values in the table depending on user input… i.e. depending on what the user puts in, the code looks at different cells in the untouchable workbooks and copies the appropriate value). I then realized that one of the rows I am copying from these workbooks does not copy correctly because it isn’t a value it is a formula. This formula does not work in the workbook I am copying to because the cells are blank. This will only happen in about 5 of 30 instances so I cannot change the FetchTable or it will be wrong for the other 25 inputs. I guess what I am looking for is a way to copy the VALUE in a cell instead of the formula. Is that possible?

 

[GovNewbi]

I think I might have solved it actually. Just writting that out made me realize something.

 

[SkipVought]

I guess what I am looking for is a way to copy the VALUE in a cell instead of the formula. Is that possible?

When you COPY, EVERYTHING associated with the cell is copied!

When you PASTE, you can elect to NOT paste everything associated with each cell, using Edit > Paste Special and selecting the appropriate Option(s).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

Ya as soon as I typed that out and sent it a little light bulb went on in my head haha. Thanks tho

 

[SkipVought]

Wunderful!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}