Using formula in code....Part 2!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alan M - 20 Aug 2007 09:50 GMT

Hi,

I am using the following formula, kindly provided on this forum) to return
some code from a string in cell D1

=MID(D1,FIND(".",D1,1)-1,3)

However when I try to use this to set the formula in column F using VBA
code as shown here:

Range(Cells(1, 17), Cells(LastRow, 17)).FormulaR1C1 =
"=MID(RC[-2],FIND(".",RC[-2],1)-1,3)

... I get a syntax error message returned.

Anyone see what is wrong please?

Reply to this Message

glenton - 20 Aug 2007 11:16 GMT

Could it be that you're code is putting it in column 17 rather than 6 (=col
F). Perhaps column O is empty.

Regards

Signature

Glenton
www.leviqqio.com
Quality financial modelling

> Hi,
>
[quoted text clipped - 12 lines]
>
> Anyone see what is wrong please?

Reply to this Message

Alan M - 20 Aug 2007 12:14 GMT

The desitnation is actually column E so yes the formula should read
Cells(1,6) not 17 as you said...but that is irrelevant, the code is still
incorrect. It says:
Excecpted list seperator of ) and the point (.) is highlighted so the syntax
of the line is wrong somewhere

> Could it be that you're code is putting it in column 17 rather than 6 (=col
> F). Perhaps column O is empty.
[quoted text clipped - 17 lines]
> >
> > Anyone see what is wrong please?

Reply to this Message

Bob Phillips - 20 Aug 2007 12:20 GMT

Range(Cells(1, 17), Cells(LastRow, 17)).FormulaR1C1 = _
"=MID(RC[-2],FIND(""."",RC[-2],1)-1,3)"

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi,
>
[quoted text clipped - 12 lines]
>
> Anyone see what is wrong please?

Reply to this Message

Alan M - 20 Aug 2007 12:34 GMT

Hi Bob,

Thanks for the suggestion,
I spotted the double quote marks and included them but I am still receiving
the same message. ...."Expected list seperator or )" ...only this time the
cursor is at the end of the line of code rather than highlighting the point.

> Range(Cells(1, 17), Cells(LastRow, 17)).FormulaR1C1 = _
> "=MID(RC[-2],FIND(""."",RC[-2],1)-1,3)"
[quoted text clipped - 15 lines]
> >
> > Anyone see what is wrong please?

Reply to this Message

Alan M - 20 Aug 2007 12:40 GMT

I tried it again and the code runs......however it places the FORMULA not the
resulting value in the destination range.

> Range(Cells(1, 17), Cells(LastRow, 17)).FormulaR1C1 = _
> "=MID(RC[-2],FIND(""."",RC[-2],1)-1,3)"
[quoted text clipped - 15 lines]
> >
> > Anyone see what is wrong please?

Reply to this Message

Bob Phillips - 20 Aug 2007 13:42 GMT

With Range(Cells(1, 17), Cells(LastRow, 17))
.FormulaR1C1 = "=MID(RC[-2],FIND(""."",RC[-2],1)-1,3)"
.Value = .Value
End With

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I tried it again and the code runs......however it places the FORMULA not
>the
[quoted text clipped - 21 lines]
>> >
>> > Anyone see what is wrong please?

Reply to this Message

Alan M - 20 Aug 2007 13:52 GMT

Great. That one works a treat. Thanks Bob , please ignore the later question.
Problem solved.

> With Range(Cells(1, 17), Cells(LastRow, 17))
> .FormulaR1C1 = "=MID(RC[-2],FIND(""."",RC[-2],1)-1,3)"
[quoted text clipped - 26 lines]
> >> >
> >> > Anyone see what is wrong please?

Reply to this Message