Help - Formula applied to column A1:a4000 using VBA

[dwpfreak]

Hi all,

I have tool I use to manage staff performance and I would like to use vba to run formulas if possible.

for example column A50 to A5000 will hold the value 1, and then
column b50 to b5000 holds the value 1,

then column GA50 to GA5000 will return the Value 1.

so A50 and B50 = 2 then GA50 = 1 or the Value "YES".

I would like the VBA code to apply to that range 50-5000 on the press of a button. The button already exists I just need the code to paste into it.

The normal formula i would apply is =if(a50:b50,1,2) OR =IF(A50+B50,2,"yes")

But as this is one of many of lots of formulas applied to the range 50-5000 the memory size is jacked up. Hence slowing spreadsheet the speed.

I have used F2 function to succesful place one formula into a code, but found i had to F2 and Enter on every cell I wanted.

Is there a short cut?

Hope that made sense.

thanks

The Freak

PS - I know that this places the formula into the cell, but i intend to copy values back to another cell (say c50:c5000) and clear the formulas in ga50:ga5000) so they are not retained anywhere except in the VBA code.

 

[xld]

    With Range("G50:G500")
        .Formula = "=IF(A50+B50,2,""yes"")"
        .Value = .Value
    End With

 

[dwpfreak]

Wow thank you, adds all the way down but..

when i change value a50 to 0, it still when run shows 2 in g50, can it show 0 or No instead?

also can i change it to return the text "yes" not 2.

but brillant stuff though.

 

[xld]

I thought you didn't want formulae as it slowed the spreadsheet down.

If you want formulae, remove the .Value = .Value line.

As for the other bit, modify the formula.

 

[dwpfreak]

hi, thank you for replying again.

Yes amended, great stuff, I amended it to

With Range("c50:c5000")
.Formula = "=IF(A50+B50,2,""YES"")"
.Value = .Value
End With
'
End Sub

if it is not 2 it shows "false", i tried adding ,""No"") after the yes section but it debugged.

Also sorry to be a pain but I want it to return "No" if it doesn't equal 2, I tried using <> but it again debugged.

Sorry to be such a pain on Sunday.

thanks

 

[dwpfreak]

sorry i pasted the original i replaced the comma with an =.

 

[xld]

Do you mean

    With Range("C50:C500")
        .Formula = "=IF(AND(A50=1,B50=1),""yes"",""no"")"
        .Value = .Value
    End With

 

[dwpfreak]

great, thankyou, i am slowly learning vba and formulas but its clear i have a way to go..

thanks for your help