Help Please

[Guest]

I have a range of data from B2:H88 which contains formulas also. I want to
devide the cell values in the range with 41. Is there any way to edit the
all cells in the range in one go to devide it with 41 other than individually
edit each cell. If it can be done with a macro please provide.

regards

 

[Guest]

Sub divideval()
Dim V As Double
Set r = Range("B2:H88")
V = 41
For Each rr In r
If rr.HasFormula Or IsEmpty(rr) Then
Else
rr.Value = rr.Value / V
End If
Next
End Sub

Will divide each value by 41, but will leave the formulas alone.

 

[Ron Rosenfeld]

I have a range of data from B2:H88 which contains formulas also. I want to
devide the cell values in the range with 41. Is there any way to edit the
all cells in the range in one go to devide it with 41 other than individually
edit each cell. If it can be done with a macro please provide.

regards

Select a blank cell.
Enter the number 41
Edit/Copy

Select your target range.

Edit/Paste Special/Divide

Constants will be divided by 41
Formulas will be placed inside parentheses and have /41 appended, so as to
divide the formula result by 41.
--ron

 

[David Biddulph]

Select a blank cell.
Enter the number 41
Edit/Copy

Select your target range.

Edit/Paste Special/Divide

Constants will be divided by 41
Formulas will be placed inside parentheses and have /41 appended, so as to
divide the formula result by 41.

.... which of course means that if a formula refers to a constant within the
range, that term will be divided by 41^2, and if there are further
references from one cell to another, that term will be divided by increasing
poweres of 41.

 

[Ron Rosenfeld]

... which of course means that if a formula refers to a constant within the
range, that term will be divided by 41^2, and if there are further
references from one cell to another, that term will be divided by increasing
poweres of 41.

I think the only way to avoid that, if, indeed, the OP wants to avoid it, would
be to convert all to constants.
--ron