Copy formula where there is data to left of it

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,992
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]
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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,992
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,992
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
 

Forum statistics

Threads
1,141,136
Messages
5,704,485
Members
421,353
Latest member
jekoxien15

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