Another option (much slower on large ranges, but OK for the ones in the
example):

Function CopyArray(rngCopy As Range, _
cellDest As Range, Divider As Double)
myarr = rngCopy.Value
For i = 1 To UBound(myarr, 1)
For j = 1 To UBound(myarr, 2)
myarr(i, j) = myarr(i, j) / Divider
Next j
Next i
cellDest.Resize(UBound(myarr, 1), _
UBound(myarr, 2)).Value = myarr
End Function

'And call it like this:

Sub test()
CopyArray _
Sheets("Sheet1").Range("B4:U4"), _
Sheets("Sheet2").Range("C16"), _
1000
End Sub

Regards,
KL


"Bob Phillips" wrote in message
...
Function SetValue(rng As Range, num)
With rng
.Value = num
With Range("IV65536")
.Value=1000
.Copy
End With
.PasteSpecial Paste:=xlValues, Operation:=xlDivide
Range("IV65536").ClearContents
End With
End Function

and call with

SetValue Range("C16:V16"), Sheets(gstrCMCMCost).Range("B4:U4")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Monique" wrote in message
...
How would i go about creating a function that could do this for any
range?

"KL" wrote:

Hi Minique,

How about this:

With Range("C16:V16")
.Value = Sheets(gstrCMCMCost).Range("B4:U4")
With Cells(65536,256)
.Value=1000
.Copy
End With
.PasteSpecial Paste:=xlValues, Operation:=xlDivide
End With
Cells(65536,256).ClearContents
Regard,
KL


"Monique" wrote in message
...
I am programming in VB. I want to divide a range of cells by one
number,
i.e.

the code i have is:

Range("C16:V16") = (Sheets(gstrCMCMCost).Range("B4:U4") / 1000)

This produces an error - A Type mismatch error.

This way works when I do one cell at a time. However I need to be
able
to
do
all ranges at once. Is this possible?

Thanks