Type mismatch: .Cells(i).Value = "=rc[-1]" / vl

  • Thread starter Thread starter Kobayashi
  • Start date Start date
K

Kobayashi

I'm still new to this which might explain why I'm making such a pig's
ear of the below!

I am trying to perform an equation that divides that value in one cell
by the value in another. 'Findrange' is the range of the cell that has
the absolute value that I wish to divide by. RC[-1] is the relative
position that contains the value that I wish to have divided by the
value in the Findrange variable.
However, I simply cannot get this to work!


My code is as follows:

For i = 1 To FTDRange.Cells.Rows.Count
FTDRange.Cells(i).Value = "=rc[-1]" / findrange
i = i + 1
Next i


Any ideas???

Adrian
 
Hi:

You are mixing formulas and values in the same expression. Try (untested):

FTDRange.Cells(i).Value = FTDRange.Cells(i).Offset(, -1).Value / findrange

Regards,

Vasant.
 
I can't combine "=rc" with addresses in the same formula. This works = "=4/"
& myaddress
But you could give your rc[-1] as an address (myaddress) and if findrange is
an address then your formula should be
= "=" & myaddress & "/" & findrange

Ole Michelsen
 
Kobayashi,

For i = 1 to FTDRange.Rows.Count
Cells(i, 3).Value = Cells(i, 3).Offset(0,-1)/ Range("findrange")
Next

or
For i = 1 to FTDRange.Rows.Count
Cells(i, 3).Value = Cells(i, 2)/ Range("findrange")
Next

Am a little unsure of how you are defining FTDRange.
"FTDRange.Cells" looks like a "double" reference to a range.

The code above assumes that FTDRange is a range starting in row 1 and
is in column C.

If this doesn't work. Post back and include how FTDRange is defined.
And what it is. Also need the same for findrange. Are they defined on the
worksheet or in the code. Makes a lot of difference.

Range defined in the worksheet use:
Range("myrange") = 5/2
Range("myrange").Value = ???
Range defined in code use:
Dim myrange as Range
Set myrange = Range("A1:A5")
myrange = 5/2
myrange.Value = ???
 
Vasant/Ole/Steve,

Many thanks for your replies! There's quite a lot to keep me going so I
shall test and let you know how I get on?

Many thanks for taking the trouble to reply!

Regards,

Adrian
 
Steve,

Here is my code. I have modified it slightly by using the Offset
function and the code now works.... .
However, whilst the code does work and returns a 'value' in the s/sheet
I have found that I actually need it to return a fomula so that when
the data in the s/sheet changes the 'value' produced by the formula
will adjust. Currently, by producing a value in the code this will not
happen.

When I change the current code from .value = .... to .formula =
"=............" I just get an error.

Any ideas???

Thanks,

Adrian
 
Steve,

Thanks for your help. Code as follows:

Dim avRange As Range
Dim findrange As Range
Dim testRange As Range
Dim FTDRange As Range
Dim FTD As Range
Dim i As Long
Dim LastColumn As Integer
Dim vl As Long
Dim ftd2range As Range
'Application.ScreenUpdating = False


'ActiveSheet.Range.SpecialCells(xlCellTypeLastCell).Select

Set FTD = Cells.Find("FTD Sum of Spread")
Set findrange = Cells.Find("FTD Sum of
Spread").End(xlToRight).End(xlToRight).End(xlToRight) _
.End(xlToRight).End(xlToLeft)
Set FTDRange = Range(findrange, findrange.End(xlUp))
FTDRange.Select
Selection.Copy
FTDRange.Offset(0, 1).PasteSpecial (xlPasteFormats)
Application.CutCopyMode = False
FTDRange(1, 2).Value = "%FTD"
Set FTDRange = Selection

With FTDRange
.Resize(.Rows.Count - 1).Offset(1, 0).Select
Set FTDRange = Selection
End With

For i = 1 To FTDRange.Cells.Rows.Count
FTDRange.Cells(i).Value = (FTDRange.Cells(i).Offset(0,
-1).Value / findrange.Value) * 100
i = i + 1
Next i


Regards,

Adrian
 
Ole,

Many thanks. I revisited your contribution below and I've now cracked
it, thanks!

I didn't have to create the 'myaddress' address in the end. The below
is the completed code which returns the formula in the s/sheet that I
was looking for. It seems that I just had to break everything down with
"", which was thanks to your help, and it done the trick!

For i = 1 To FTDRange.Cells.Rows.Count
FTDRange.Cells(i).Value = "=" & (FTDRange.Cells(i).Offset(0,
-1).Value & "/" & findrange) & "* 100"
i = i + 1
Next i

Many thanks,

Adrian
 
Back
Top