Copy formula above current cell

[Bakamoo]

I know copying the formula have been posted here lots of times, but they're always copying below, I need one that copies to the top

table is like this
A1-label B1-label C1-label
A2-data B2-data C2-formula(B2)
A3-data B3-data C3-formula(B3)
A4-data B4-data C4-formula(B4)

now, the problem is i want to add on top so latest data is only on top, once i insert it becomes like this

table is like this
A1-label B1-label C1-label
A2-blank B2-blank C2-blank
A3-data B3-data C3-formula(B3)
A4-data B4-data C4-formula(B4)
A5-data B5-data C5-formula(B5)

Now I want to C2 to have the formula and the rest will adjust.
? = current row
C? formula points to B? so once i create the formula, everything below has to move too

 

[mikerickson]

Putting this in the sheet's code module is a rather brute force approach, but effective.

Private Sub Worksheet_Change(ByVal Target As Range)
    Range("C2").FormulaR1C1 = Range("C3").FormulaR1C1
End Sub

 

[Bakamoo]

Any chance you can give me the whole code?

I'm really a noob at vba and i know what i know from using the record macro and trying to decipher what each line does

 

[mikerickson]

That is the whole code.
If you copy paste that into the sheet's code module, it should do what you want.
Every time you insert a whole row at 2, it will put the proper formula in the newly inserted cell in column C.

 

[Bakamoo]

Doesn't work for me, excel crashes. I think it might not work if theres too much data? I'm upto the point where the data is too long that it becomes #VALUE error. I'm not sure if this is normal, because i convert links and other words to their hex %20 %2F bla bla value.

Aside from that there's 1 more problem, i just gave my simple table because i thought there would be a longer code lol, guess that proves how wrong i am

After inserting in that worksheet, im going to insert on the top row of worksheet2 the same thing also moving everything down with their formulas and having the top row filled with formulas too


Here's what happens before crash
Excel will insert 1 new row
Copy the now B3 data into the new B2
then it crashes

 

[Bakamoo]

If anyone has a longer code, for just the 1st table, i think it would do.

Right now im thinking of creating a string that has the formula.
Then place that formula into C2 then find the last cell then in a loop copy till the last cell down. Not sure if there would be problems here aside from the " and ' characters.

 

[Bakamoo]

Nevermind, problem solved.
For reference, here's what i did

There are 2 sheets here
Record macro
Insert 1 row on sheet 1 //the 2 inserts has to be done first before copying formulas so the formulas will point correctly
Insert 1 row on sheet 2
Copy the row just below the insert
Paste on the new row
Go back to sheet 1
Copy the row just below the insert
Paste on the new row
Delete contents you don't need
Stop recording