Using formula in code....Part 2!

[Alan M]

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?

 

[glenton]

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

Regards

 

[Alan M]

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.

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

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?

 

[Bob Phillips]

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]

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)"



--
---
HTH

Bob

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

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?

 

[Alan M]

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)"



--
---
HTH

Bob

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

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?

 

[Bob Phillips]

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)

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)"



--
---
HTH

Bob

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

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?

 

[Alan M]

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)"
.Value = .Value
End With


--
---
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
resulting value in the destination range.

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)



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?