VB code error

[redspanna]

Hi all

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

.Formula = "=RIGHT(" & myValue & ",FIND("" - ""," & myValue & ")-2)"

any ideas to fix

many thanks

 

[VoG]

I think you are missing a comma

.Formula = "=RIGHT(" & myValue & ",FIND("" - ""," & myValue & "),-2)"

 

[Andrew Poulsom]

Your code worked for me. What does myValue contain?

 

[redspanna]

Hi

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

Andrew - the myvalue cell (B3) contains a formula as shown here

=TEXT(B8,"ddMmmyy")&" - "&TEXT(B14,"ddMmmyy")

B14 holds a date formatted as ddd ddmmm

any ideas?

thanks guys

 

[Andrew Poulsom]

Is myvalue a Range object?

.Formula = "=RIGHT(" & myvalue.Address & ",FIND("" - ""," & myvalue.Address & ")-2)"

 

[redspanna]

many thanks all!!