Copy formula where there is data to left of it

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,636
Office Version
  1. 2019
Platform
  1. Windows
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]
 

Some videos you may like

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
Joined
Jun 20, 2006
Messages
203
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
Joined
Aug 31, 2005
Messages
5,828
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
Joined
Jun 26, 2006
Messages
5,636
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 26, 2006
Messages
5,636
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 31, 2005
Messages
5,828
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
Joined
Feb 1, 2003
Messages
17,832
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
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top