To store the formula in a string

P

pol

Please help to store the followng formula in a string . I did the following
ways but not working . The purpose of the formula to convert string into
date format. The formlula is working independantly. But it didnot working
when I am trying to store in a variable.

myFormula =
"=IF(LEFT(CELL("format",RC[1]),1)="D",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"

Sub sizeonly()
Dim res As Variant
Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String
Set wks = ActiveSheet

With wks

res = Application.Match("DATE", .Rows(2), 0)

LastRow = .Cells(.Rows.Count, res).End(xlUp).Row

.Columns(res).Insert

'add header in row 1
.Cells(2, res).Value = "YEAR"

myFormula =
"=IF(LEFT(CELL("format",RC[1]),1)="D",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))" 'Here is not working pls help

.Range(.Cells(3, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula
.Columns(res).AutoFit


End With

End Sub

With thanks

Polachan Paily
 
B

Bob Phillips

Sub sizeonly()
Dim res As Variant
Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String
Set wks = ActiveSheet

With wks

res = Application.Match("DATE", .Rows(2), 0)

LastRow = .Cells(.Rows.Count, res).End(xlUp).Row

.Columns(res).Insert

'add header in row 1
.Cells(2, res).Value = "YEAR"

myFormula = _
"=IF(LEFT(CELL("format",RC[1]),1)=""D"",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"
'Here is not working pls help

.Range(.Cells(3, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula
.Columns(res).AutoFit


End With

End Sub
 
P

pol

Still it is not working , Syntax error is coming

Bob Phillips said:
Sub sizeonly()
Dim res As Variant
Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String
Set wks = ActiveSheet

With wks

res = Application.Match("DATE", .Rows(2), 0)

LastRow = .Cells(.Rows.Count, res).End(xlUp).Row

.Columns(res).Insert

'add header in row 1
.Cells(2, res).Value = "YEAR"

myFormula = _
"=IF(LEFT(CELL("format",RC[1]),1)=""D"",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"
'Here is not working pls help

.Range(.Cells(3, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula
.Columns(res).AutoFit


End With

End Sub


--
__________________________________
HTH

Bob

pol said:
Please help to store the followng formula in a string . I did the
following
ways but not working . The purpose of the formula to convert string into
date format. The formlula is working independantly. But it didnot working
when I am trying to store in a variable.

myFormula =
"=IF(LEFT(CELL("format",RC[1]),1)="D",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"

Sub sizeonly()
Dim res As Variant
Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String
Set wks = ActiveSheet

With wks

res = Application.Match("DATE", .Rows(2), 0)

LastRow = .Cells(.Rows.Count, res).End(xlUp).Row

.Columns(res).Insert

'add header in row 1
.Cells(2, res).Value = "YEAR"

myFormula =
"=IF(LEFT(CELL("format",RC[1]),1)="D",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"
'Here is not working pls help

.Range(.Cells(3, res), .Cells(LastRow, res)).FormulaR1C1 =
myFormula
.Columns(res).AutoFit


End With

End Sub

With thanks

Polachan Paily
Click to expand...
Click to expand...
 
B

Bob Phillips

I missed one

Sub sizeonly()
Dim res As Variant
Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String
Set wks = ActiveSheet

With wks

res = Application.Match("DATE", .Rows(2), 0)

LastRow = .Cells(.Rows.Count, res).End(xlUp).Row

.Columns(res).Insert

'add header in row 1
.Cells(2, res).Value = "YEAR"

myFormula = _
"=IF(LEFT(CELL(""format"",RC[1]),1)=""D"",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"
.Range(.Cells(3, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula
.Columns(res).AutoFit
End With

End Sub


--
__________________________________
HTH

Bob

pol said:
Still it is not working , Syntax error is coming

Bob Phillips said:
Sub sizeonly()
Dim res As Variant
Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String
Set wks = ActiveSheet

With wks

res = Application.Match("DATE", .Rows(2), 0)

LastRow = .Cells(.Rows.Count, res).End(xlUp).Row

.Columns(res).Insert

'add header in row 1
.Cells(2, res).Value = "YEAR"

myFormula = _
"=IF(LEFT(CELL("format",RC[1]),1)=""D"",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"
'Here is not working pls help

.Range(.Cells(3, res), .Cells(LastRow, res)).FormulaR1C1 =
myFormula
.Columns(res).AutoFit


End With

End Sub


--
__________________________________
HTH

Bob

pol said:
Please help to store the followng formula in a string . I did the
following
ways but not working . The purpose of the formula to convert string
into
date format. The formlula is working independantly. But it didnot
working
when I am trying to store in a variable.

myFormula =
"=IF(LEFT(CELL("format",RC[1]),1)="D",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"

Sub sizeonly()
Dim res As Variant
Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String
Set wks = ActiveSheet

With wks

res = Application.Match("DATE", .Rows(2), 0)

LastRow = .Cells(.Rows.Count, res).End(xlUp).Row

.Columns(res).Insert

'add header in row 1
.Cells(2, res).Value = "YEAR"

myFormula =
"=IF(LEFT(CELL("format",RC[1]),1)="D",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"
'Here is not working pls help

.Range(.Cells(3, res), .Cells(LastRow, res)).FormulaR1C1 =
myFormula
.Columns(res).AutoFit


End With

End Sub

With thanks

Polachan Paily
Click to expand...
Click to expand...
Click to expand...
 
P

pol

thanks a lot

Bob Phillips said:
I missed one

Sub sizeonly()
Dim res As Variant
Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String
Set wks = ActiveSheet

With wks

res = Application.Match("DATE", .Rows(2), 0)

LastRow = .Cells(.Rows.Count, res).End(xlUp).Row

.Columns(res).Insert

'add header in row 1
.Cells(2, res).Value = "YEAR"

myFormula = _
"=IF(LEFT(CELL(""format"",RC[1]),1)=""D"",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"
.Range(.Cells(3, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula
.Columns(res).AutoFit
End With

End Sub


--
__________________________________
HTH

Bob

pol said:
Still it is not working , Syntax error is coming

Bob Phillips said:
Sub sizeonly()
Dim res As Variant
Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String
Set wks = ActiveSheet

With wks

res = Application.Match("DATE", .Rows(2), 0)

LastRow = .Cells(.Rows.Count, res).End(xlUp).Row

.Columns(res).Insert

'add header in row 1
.Cells(2, res).Value = "YEAR"

myFormula = _
"=IF(LEFT(CELL("format",RC[1]),1)=""D"",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"
'Here is not working pls help

.Range(.Cells(3, res), .Cells(LastRow, res)).FormulaR1C1 =
myFormula
.Columns(res).AutoFit


End With

End Sub


--
__________________________________
HTH

Bob

Please help to store the followng formula in a string . I did the
following
ways but not working . The purpose of the formula to convert string
into
date format. The formlula is working independantly. But it didnot
working
when I am trying to store in a variable.

myFormula =
"=IF(LEFT(CELL("format",RC[1]),1)="D",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"

Sub sizeonly()
Dim res As Variant
Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String
Set wks = ActiveSheet

With wks

res = Application.Match("DATE", .Rows(2), 0)

LastRow = .Cells(.Rows.Count, res).End(xlUp).Row

.Columns(res).Insert

'add header in row 1
.Cells(2, res).Value = "YEAR"

myFormula =
"=IF(LEFT(CELL("format",RC[1]),1)="D",RC[1],DATE(LEFT(RC[1],4),MID(RC[1],5,2),RIGHT(RC[1],2)))"
'Here is not working pls help

.Range(.Cells(3, res), .Cells(LastRow, res)).FormulaR1C1 =
myFormula
.Columns(res).AutoFit


End With

End Sub

With thanks

Polachan Paily
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

To extract the number if there is niumeric in right most digits 4
Syntax error while applying a formula in a macro 3
How I can get monthname from the following formula 4
Creating Pivot Table with Macro using Multiple Sheets 0
macro to copy following cells in the same column 4
Please help to to derive a formula 1
Make VBA ComboBox_Change work in 3 sheets 1
To give paste special (value) in macros 1

Top