Help Please

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
Click to expand...

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
 
Ron Rosenfeld said:
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.
Click to expand...

.... 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.
 
... 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.
Click to expand...

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