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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

tushiroda

Board Regular
Joined
Mar 21, 2005
Messages
159
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
980
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,772
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
980
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,673
Messages
5,838,710
Members
430,566
Latest member
ChanchalSingh

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