Fix code - help!

[redspanna]

Hi all

I'm trying to paste the value of B3 in weekly perf sheet into next available row in column x of graphs sheet.
The pasted value should also be pasted with the following formula

=RIGHT('weekly perf'!B3,FIND("-",'weekly perf'!B3)-2)

but not being any good at VB I am stuck!

The code I have tried to write so far is as follows, but it doesn't do the job..

Sub Ant()
    Sheets("weekly perf").Select
    Range("B3").Select
    Sheets("graphs").Select
    ' Find the last row of data
    FinalRow = Range("X65536").End(xlUp).Row

Selection.Formula "=RIGHT('weekly perf'!B3,FIND(" - ",'weekly perf'!B3)-2)"
End Sub

any help to fix this would be apreciated

thanks

 

[JackDanIce]

Try:

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

 

[Weaver]

Which sheet/range do you need the formula in?

You select a cell in 'weekly perf' and then select the 'graphs' sheet

try

Sheets("weekly perf").Range("B3")
  .formula = "=RIGHT('weekly perf'!B3,FIND(" - ",'weekly perf'!B3)-2)"
  .value = .value
end with

 

[Ruddles]

Try:-

 .formula = "=RIGHT('weekly perf'!B3,FIND(" - ",'weekly perf'!B3)-2)"

Those quotes need doubling up.

 

[redspanna]

Hi guys

Thanks for all your replies, however, I'm still a little confused as code still doesn't quite work

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

this gives error on the

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

as I said originally, I want the value of cell B3 from weekly perfs sheet copied and then pasted into next available row of column X on the graphs sheet.
When it is pasted the followinf formula is added...

=RIGHT('weekly perf'!B3,FIND("-",'weekly perf'!B3)-2)

sorry if I've confused anyone and again hope you can help

regards

 

[Weaver]

Try:-

Those quotes need doubling up.

Good catch.