Using formula in code....Part 2!
 

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 he

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?

Using formula in code....Part 2!
 

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

Regards
--
Glenton
www.leviqqio.com
Quality financial modelling


"Alan M" wrote:

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 he

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?

Using formula in code....Part 2!
 

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

"glenton" wrote:

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

Regards
--
Glenton
www.leviqqio.com
Quality financial modelling


"Alan M" wrote:

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 he

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?

Using formula in code....Part 2!
 

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



--
---
HTH

Bob

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



"Alan M" wrote in message
...
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 he

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?

Using formula in code....Part 2!
 

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.



"Bob Phillips" wrote:

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



--
---
HTH

Bob

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



"Alan M" wrote in message
...
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 he

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?

Using formula in code....Part 2!
 

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

"Bob Phillips" wrote:

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



--
---
HTH

Bob

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



"Alan M" wrote in message
...
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 he

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?

Using formula in code....Part 2!
 

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


--
---
HTH

Bob

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



"Alan M" wrote in message
...
I tried it again and the code runs......however it places the FORMULA not
the
resulting value in the destination range.

"Bob Phillips" wrote:

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



--
---
HTH

Bob

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



"Alan M" wrote in message
...
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 he

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?

Using formula in code....Part 2!
 

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

"Bob Phillips" wrote:

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


--
---
HTH

Bob

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



"Alan M" wrote in message
...
I tried it again and the code runs......however it places the FORMULA not
the
resulting value in the destination range.

"Bob Phillips" wrote:

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



--
---
HTH

Bob

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



"Alan M" wrote in message
...
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 he

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?