application defined or obj defined error for formula

ces005

Board Regular
Joined
Mar 13, 2006
Messages
189
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

Board Regular
Joined
Mar 13, 2006
Messages
189
Here is the code which is not working. The error occurs in the .Formula line.

Code:
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
===================
Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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:

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

to this:

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

ces005

Board Regular
Joined
Mar 13, 2006
Messages
189
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.

Code:
     .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

Board Regular
Joined
Mar 13, 2006
Messages
189
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.

Code:
   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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,921
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top