Inserting Column then inserting Formula

[joes2580]

For example, I want to Insert a column to the Left Of column A first.

Next I want to take the data from Column B and do a =left on the first part and a =Mid on the second part (because the data is separated by a "/"; Part number on the left and description on the right.)

I was trying to do it like this:

Sub Inventory_Setup()


Worksheets(1).Range("a7:4000").Value = "=LEFT(B7,FIND(" / ",B7)-2)"

But I get a "Type Mismatch" error.


Any thoughts?

 

[revcanon]

Try this.

Test on a backup copy.

    Sheets(1).Range("A7:A4000").FormulaR1C1 = "=LEFT(RC[1],FIND("" / "",RC[1])-2)"


End Sub

 

[joes2580]

And what does the "RC" do?

 

[joes2580]

Running this code doesn't do anything. I forgot to mention I have it linked to a command button; not sure if it makes a difference.

 

[revcanon]

ADVERTISEMENT

Try this:

Sub Inventory_Setup()

    Columns("A:A").Insert Shift:=xlToRight
    Sheets(1).Range("A7:A4000").FormulaR1C1 = "=LEFT(RC[1],FIND("" / "",RC[1])-2)"


End Sub

The RC is a reference to the rows and columns in relation to the cell with the formula.

So when cell A7 is populated it is looking for RC[1] which is one column to the right (B7)

 

[joes2580]

That one doesn't do anything either.

 

[joes2580]

Let me give you the example:

Column A Column B
(Empty) 0078 / 63-80 TOYOTA LANDCRUISER FJ40 FRT/REAR 1 1/2" SHCKLE LIFT



I'm going to insert an additional Column to the Left of Column B also but the gist is I want the 0078 in Column A.

the info on the right side of the "/" in Column B. And I want do all of it in VBA.