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

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

Hi,

The error is occuring because the code is trying to divide a range object by
1000 and not the values in that range. Using a single cell is OK because VBA
automatically uses the value when a single cell range is used.

If you want to use VBA here you would have to loop through each of the
values of the range. The following code works but not sure if it's THE best
method.

Dim rngStart As Range

Set rngStart = Range("C16:V16")


For Each rng In rngStart

rng.Offset(-12, 0) = (rng.Value) / 1000

Next rng

HTH,
Matt

"Monique" wrote:

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

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

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

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