application defined or obj defined error for formula

[ces005]

I am trying to apply a formula to a range:

Rw = Range("A65536").End(xlUp).Row
With Range(Cells(2, 17), Cells(Rw, 17))
.FormulaR1C1 = "=offset($A$1,row(),14,1,1)"
end with

Why am I getting an error with the above? After I get the above to work, I want to modify the formulat to the following:

.FormulaR1C1 = "=text(offset($A$1,row(),14,1,1),""mmm-yy"")"

(convert the date in column 14 into "mmm-yy" format and put this in column 17)

Thanks.
ew

 

[venkat1926]

It is not clear what you want to do. if you want to format dates in column 14 i.e. column N, and if they are entered as dates - highlight that column and click
format-cells=custom
and in the small window type
mmm-yy
and click ok.

If you require something else please clarify and give one or two examples.

 

[ces005]

Here is the code which is not working. The error occurs in the .Formula line.

Sub Macro2()
'
'
   Dim wsStrrDates As Worksheet
   Set wsCrData = ThisWorkbook.Worksheets("CRData")
   Dim Rw, TempRow, CrDataWsRow As Long
   Application.Sheets("CRData").Activate
   Rw = Range("A65536").End(xlUp).Row
   With Range(wsCrData.Cells(2, 17), wsCrData.Cells(Rw, 17))
      .FormulaR1C1 = "=text(offset($A$1,row(),14,1,1),""mmm-yy"")"

   End With
'
End Sub

I am using a visual basic script to take the date in column 14, reformat it, and put it into column 17. I am trying to apply a formula to column 17 using a visual basic script.

 

[venkat1926]

I have slightly modified your macro see whether it works
please examine whether the line below in the code is necessary.
Dim wscrData As Worksheet
As you have activated the sheet CRData you need not repeat it. that is why in my line
set rng=....... I did not use CRData
===================

Sub testone()

Dim wscrData As Worksheet  'is this necessary
   Dim wsStrrDates As Worksheet
   Set wscrData = ThisWorkbook.Worksheets("CRData")
   Dim Rw, TempRow, CrDataWsRow As Long
   Application.Sheets("CRData").Activate
   Rw = Range("A65536").End(xlUp).Row
'   With Range(wsCrData.Cells(2, 17), wsCrData.Cells(Rw, 17))
'      .FormulaR1C1 = "=text(offset($A$1,row(),14,1,1),""mmm-yy"")"
'
'   End With
'MsgBox Rw
Dim rng As Range
Set rng = Range(Cells(2, 14), Cells(Rw, 14))
rng.Copy rng.Offset(0, 3)
Columns(rng.Column + 3).NumberFormat = "mmm-yy"

End Sub

 

[Richard Schollar]

Hi

Without going into any detail on the workings of the code, the one glaring problem you have is that you are accessing the FormulaR1C1 property but then applying an A1-style formula. So, try amending this line:

.FormulaR1C1 = "=text(offset($A$1,row(),14,1,1),""mmm-yy"")"

to this:

.FormulaR1C1 = "=text(offset(R1C1,row(),14,1,1),""mmm-yy"")"

 

[ces005]

value in offset call is a variable -- how to evaluate it?

The below formula is what I am using since "17" is a number determined at run-time and may need to change depending on the input file.

     .FormulaR1C1 = "=text(offset(R1C1,row(),EndedOnCol,1,1),""mmm-yy"")"

How do I change the above so that it works? EndedOnCol is a variable containing the column number.

Thanks.

 

[ces005]

The below works. I am creating a formula in column 17 which takes the value in the column specified by the EndedOnCol variable and sets the format to mmm-yy.

   With Range(wsCrData.Cells(2, 17), wsCrData.Cells(Rw, 17))

     .FormulaR1C1 = "=text(offset(R1C1,row()-1," & CStr(EndedOnCol - 1) & ",1,1),""mmm-yy"")"

   End With

Is the line .FormulaR1C1 the correct way to specify this kind of logic?

-ew