# Copy formula where there is data to left of it

#### howard

##### Well-known Member
I have a spreadsheet that has a formula in column F "=+B4-E4" I need to insert this formula in column F in line with the data in column B. The formula in column F must then be copied down to last row containing data to the left of column F.

Your assistance will be most appreciated

Howard
[/url]

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### tamrob23

##### Board Regular
Goodmorning Howard,

I believe the formula you need to use to capture the information to the left will be =LEFT(B4,2).

B4, is the cell you want to capture and 2 is the number of characters you want to show. I hope this helps.

#### NBVC

##### Well-known Member
I have a spreadsheet that has a formula in column F "=+B4-E4" I need to insert this formula in column F in line with the data in column B. The formula in column F must then be copied down to last row containing data to the left of column F.

You'll need to restate your question, I think. I don't really understand what you are wanting to do. If the formula is in F already, then what do you mean you need to insert a formula in column F?

#### howard

##### Well-known Member
Hi Tamara

Thanks for responding to my question.

The formula in column F is B4 - E4. I need VBA code that will insert the formula in F4 and copy this down to the last row of column F where there is data to the left of it in Column E .In this instance the formula must be copied from F4 to F6 and E6 is the last row to the left of F7 containing data

See Samople data below

Row A B C D E F
4 PE 11 12 -1 7 4
5 Uitenhage 10 9 1 7 3
6 Newton 12 12 0 7 5

Your assistance will be most appreciated

Regards

Howard

#### howard

##### Well-known Member

A B C D E F
PE 11 12 -1 7 4
Uitenhage 10 9 1 7 3
NP 12 12 0 7 5

The formula in column F is B4-F4. I need VBA code that will insert this code into F4 and then copy the code down to the last row based on the last row in column E that has down i.e if E6 is the last row next the column F that has data, then the formula must be copied from F4 to F6

Hope this clarifies what i'm looking for

Regards

howard

#### NBVC

##### Well-known Member
Do you have Excel 2003?

If so you can you use the List feature. This will automatically insert the formula in F for any new info filled in other columns within the list.

Select the entire range and go to Data|List|Create List and accept the range suggested. When you need to add new rows, just click on the current last row and the list will be expanded marking the new row with an asterisk. Simply type of it and in other columns and the formula too will follow.

#### erik.van.geit

##### MrExcel MVP
I need VBA code that will insert the formula in F4 and copy this down to the last row of column F where there is data to the left of it in Column E
Hi,

try this
Code:
``````Sub test()
Dim LR As Long
LR = Cells(Rows.Count, "E").End(xlUp).Row
Range("F4:F" & LR).Formula = "=B4-E4"
End Sub``````
to display well-aligned sampledata on the Board, you can use tools like colo's HTMLmaker (see bottom page) or "Table-It" (see my signature)

kind regards,
Erik

Replies
8
Views
64
Replies
4
Views
95
Replies
4
Views
37
Replies
7
Views
61
Replies
11
Views
163

1,109,359
Messages
5,528,220
Members
409,809
Latest member
VICKRAM

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...