Enter formula if values present

[ali32j]

Hi All
I have a spreadsheet with list of contacts, i m looking to make it easier for clients to update spreadsheet, so after they paste contacts in i would like to add a command button that when pressed will add a formula in cell if the adjacent cell has a value, going down the spreadsheet until it reaches the last record at which point it stops

Can anyone help?

Thanks

Ali

 

[SkipVought]

Hi,

Is the objective to add formulas to a row, as now values are added? If so, no button or VBA is necessary. Use [ b]Data > List > Create LIST[/b]. Your formulas will be propogated as you start the next row.

Also, use the TAB key, rather than ENTER, after each value is entered.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ali32j]

Hi Skip

Users are pasting data of between 1000 and 5000 lines into a template spreadsheet, once pasted i need the command button to ensure each of the 5000lines has the formula in column X, alot of the users are nt excel savvy so trying to simplify the process with just 2 actions paste and click button

Ali

 

[xlbo]

Sub EnterFormulae()
Const stRow = 2
Dim lRow as long

lRow = cells(65536,23).end(xlup).row

with Range("X" & stRow & ":X" & lrow)
    .formula = "Your Formula Here"
    .copy
    .pastespecial xlpastevalues
    application.cutcopymode = false
End With
End sub

Assumes that column W is fully populated down to the end of the data set

Enter the formula in code as it would be for row 2 - if there is any "text" in the formulae, it will need to be added as ""text""

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question