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

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
968
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?



Code:
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! :biggrin:
 
Last edited:

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

tushiroda

Board Regular
Joined
Mar 21, 2005
Messages
156
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

Active Member
Joined
Mar 14, 2002
Messages
469
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

Well-known Member
Joined
Jul 6, 2009
Messages
968
Okay,

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


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

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

Marc

Active Member
Joined
Feb 21, 2002
Messages
388

ADVERTISEMENT

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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
You don't need to select cells to write formulae to them. Something ilke this (untested)....

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

Well-known Member
Joined
Jul 6, 2009
Messages
968
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,200
Members
414,513
Latest member
junbuggle

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