VB code error

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,490
Office Version
  1. 2016
Platform
  1. Windows
Hi all

Code:
Sub Ant()
Dim FinalRow As Long
Dim myValue As Variant
 
myValue = Sheets("weekly perf").Range("B3")
Sheets("graphs").Select
' Find the last row of data
FinalRow = Range("X" & Rows.Count).End(xlUp).Row
With Range("X" & FinalRow)
  .Formula = "=RIGHT(" & myValue & ",FIND("" - ""," & myValue & ")-2)"
End With
End Sub

I'm trying to get the above code to copy the value of cell B3 in weekly perf tab into next available row of column X in graphs tab.

When the value is pasted it is done so by using the formula as shown.

However the code sticks with an error "application defined or object defined error" on the
Code:
.Formula = "=RIGHT(" & myValue & ",FIND("" - ""," & myValue & ")-2)"

any ideas to fix

many thanks
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I think you are missing a comma

Rich (BB code):
.Formula = "=RIGHT(" & myValue & ",FIND("" - ""," & myValue & "),-2)"
 

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,490
Office Version
  1. 2016
Platform
  1. Windows
Hi

VoG - I added a comma as suggested and error still appears

Andrew - the myvalue cell (B3) contains a formula as shown here
Code:
=TEXT(B8,"ddMmmyy")&" - "&TEXT(B14,"ddMmmyy")

B14 holds a date formatted as ddd ddmmm

any ideas?

thanks guys
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is myvalue a Range object?

Rich (BB code):
.Formula = "=RIGHT(" & myvalue.Address & ",FIND("" - ""," & myvalue.Address & ")-2)"
 

Watch MrExcel Video

Forum statistics

Threads
1,130,046
Messages
5,639,758
Members
417,109
Latest member
996

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