Why doesn't this very simple formula work in a sub routine but WILL work when insert manually?

[VBAProIWish]

Hello All,

I have been working on this one little problem for about 20 minutes and I give up. Would someone please tell me why this formula isn't working in a sub, but WILL work when inserted manually?

Sub Test   
 
 Sheets("Numbers").Select
 
    Range("F2").Select
    ActiveCell.FormulaR1C1 = Right(E2, 2) & " " & Left(E2, 3)
    Range("F3").Select
    ActiveCell.FormulaR1C1 = Right(E3, 2) & " " & Left(E3, 3)
    Range("F2:F3").Select
Selection.AutoFill Destination:=Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row)
 
 
End Sub

I run it and nothing appears in cell F2 or F3. But when I insert the same formula into F2 manually, it works! ARgggg.

Thanks much!

 

[tushiroda]

what in the world are you doing....

anyway, you need to wrap formulas with " and also put a " in front of each " you have in your formula. you're also missing the = in the formula that you are trying to put in there and you have unneeded select statements. an example of one of the lines is below. There may be other problems here, but that's what I picked up after just the first half.



Range("F2").FormulaR1C1 = "=Right(E2, 2) & "" "" & Left(E2, 3)"

 

[ammdumas]

Hey,

First off, I'd use the CONCATENATE formula with LEFT and RIGHT, instead of the & thingy, but that's just me.

Secondly, try putting quotes around the whole formula. I.e...

ActiveCell.FormulaR1C1 = "Right(E2, 2) & " " & Left(E2, 3)"

 

[VBAProIWish]

Okay,

I think I finally figured it out myself. Here's what I did...

     Range("F2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],2)&"" ""&LEFT(RC[-1],3)"
    Range("F2").Select
 
Selection.AutoFill Destination:=Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row)
    Range("F2").Select

So, problem solved...by me

ammdumas & tushiroda, thank you both for looking and your advice as well.

 

[Marc]

I got the first two formulas to drop with this, but I do not know what you are doing with the last two lines of code.

Sub Test()

Sheets("Numbers").Select

Range("F2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],2) & "" "" & LEFT(RC[-1],2)"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],2) & "" "" & LEFT(RC[-1],2)"
Range("F2:F3").Select
Selection.AutoFill Destination:=Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row)


End Sub

 

[njimack]

You don't need to select cells to write formulae to them. Something ilke this (untested)....

Sub Test()
 
 With Sheets("Numbers")

.Range("F2:F" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=RIGHT(E2, 2) & "" "" & LEFT(E2, 3)"
 
 End With
 
End Sub

 

[VBAProIWish]

Marc,

The last 2 lines of code are to make sure the formula fills down equal to the bottom most row of data in another column.


njimack,

That code is fantastic! I didn't realize that I could "tidy" up the code that much. I bet it will not only look better and easier to read, but might run faster as well.


Thanks to both