Copy formula where there is data to left of it

[howard]

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]

 

[tamrob23]

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]

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]

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]

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]

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]

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

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