Calculating and not showing formula ,only values

  • Hello Guys

    i have a following line

    Code
    1. lr = Cells(Rows.Count, "B").End(xlUp).Row
    2. Range("U1:U" & lr).Formula = "=(K1*L1*M1*N1)/1000000000"


    how can i change this to worsksheet function kind of thing which will not show the formula and only show the desired answers


    Regards

  • sobi_256

    Changed the title of the thread from “Evaluate function for a dynamic range syntax” to “Calculating and not showing formula ,only values”.
  • Hello sobi_256,


    If I am not mistaken ... you had the same question ... not too long ago ... ;)


    You can use Evaluate ... to show the result ... instead to adding the formula to sheet ...


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • OK ... not extremely difficult ;)


    you could test following :


    Code
    1. Range("U1:U" & lr) = Evaluate("=(K1*L1*M1*N1)/1000000000")


    Let me have your comments

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • this gives an error #VALUE! :(, do evaluate work over a range?

    I cannot guess what your formula =(K1*L1*M1*N1)/1000000000 does produce in your worksheet ...


    And are you copying it down or not ... ????


    By the way posting only one line of your macro ... makes everything very obscure ...

    Do not hesitate to post the whole macro ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • thankyou i think i have got it, i used from your previous code



    Code
    1. Function Nb2Let(ColumnNumber As Long) As String
    2. Nb2Let = Split(Cells(1, ColumnNumber).Address, "$")(1)
    3. End Function
    4. For i = 2 To lr
    5. Cells(i, 15) = Evaluate("=(" & Nb2Let(11) & i & "*" & Nb2Let(12) & i & "*" & Nb2Let(13) & i & "*" & Nb2Let(14) & i & ")/1000000")
    6. Next i
  • It is very pleasant to see how quickly your are progressing ... !!! :)


    Thanks a lot for your Thanks ...AND for the LIke :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Alternative:


    Code
    1. With Range("U1:U" & lr)
    2. .Formula = "=(K1*L1*M1*N1)/1000000000"
    3. .Value2 = .Value2
    4. End With

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why