Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA Formula (2000)

    My Workbook consists of WorkSheets F, R, C and A.
    Sheet("F").B2 has a formula like ='C'!AH2*100/SUM(A'!AK$28:AK$29). Selecting F!B2 Copy and Paste in any Sheet R Cell works fine. It automatically 'adjusts' for the row and column positions. I repeatedly do this via a VBA LOOP code to a Range of Cells, say from R!Z12 to R!Z20, i.e. 9 loops. After each Paste, I also Copy and PasteSpecial to get a Value instead of a Formula in Sheet R. But this step removes the Formula from the clipboard. This requires a 'visit' to F!B2 to recopy the Formula, switching Sheets at each loop, a 'slow downer'.
    I want to assign this formula to a variable and use this variable to achieve the same effect by coding the variable in the LOOP instead of the 'Absolute' addressing I now use to re-access F!B2. Your help will be greatly appreciated.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Formula (2000)

    Does th efollowing do what you want ?: <pre> Sheets("R").Range("Z12:Z20") = Sheets("F").Range("B2").Formula
    Sheets("R").Range("Z12:Z20") = Sheets("R").Range("Z12:Z20").Value</pre>

    You could use variable names for the Sheetnames and Range names, if you want to make it more general purpose. Th first line copies the formula and the second changes the result to the static value.

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel VBA Formula (2000)

    As Andrew implied, there's no need to use a loop, Excel almost reads your mind. I prefer to use the Copy method to emphasize that the formula will be relocated:
    <pre>Option Explicit
    Sub Macro1()
    Dim s As Range, d As Range
    Set s = Sheets("F").Range("B2")
    Set d = Sheets("R").Range("Z12:Z20")
    s.Copy d
    d = d.Value
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Formula (2000)

    <P ID="edit" class=small>Edited by Selva on 21-Dec-01 09:40.</P>Tks a million!. Can you help to fine tune. Say range R!Z12:Z20 could change in number of rows. How can I cater for this? I suppose it requires use of range names.
    I also need to repeat this operation for next 16 columns.

  5. #5
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Formula (2000)

    <P ID="edit" class=small>Edited by Selva on 21-Dec-01 09:41.</P>Tks a million!. Can you help to fine tune. Say range R!Z12:Z20 could change in number of rows. How can I cater for this? I suppose it requires use of range names.
    (This request is also sent to Andrew). The 'cleaniness' of your code is amazing to me.
    I also need to repeat tis operation for the next 16 columns.

  6. #6
    New Lounger
    Join Date
    Nov 2001
    Location
    Amsterdam
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Formula (2000)

    "I also need to repeat this operation for the next 16 columns. "
    Something like the following?

    dim rngRangeTemp as range
    dim i as integer
    for i=26 to 42'numeric equivalent of column z to 16 cols further along
    with sheets("R")
    set rngRangeTemp=.Range(.Cells(12,i),.Cells(20,i))
    rngRangeTemp=Sheets("F").Range("B2").Formula
    rngRangeTemp = rngRangeTemp.Value
    next
    set rngRangeTemp=nothing
    end with

    Mark

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Formula (2000)

    There is noo reason why you cannot increment th erow number to the required value. Z12:Z1000 should wwork just as good. Also there should be no problem replacing the actuall address of the range with a name, e.g<pre> Sheets("R").Range("Destination") = Sheets("F").Range("Source").Formula
    Sheets("R").Range("Destination") = Sheets("R").Range("Destination").Value</pre>

    where Destination and Source are named ranges.

    You can use a sort of shorthand with Sheets("R").[Destination]. If your range names are not repeated on other worksheets the above code could be written as <pre> [Destination] = [Source].Formula
    [Destination] = [Destination].Value</pre>

    You can apply the same approach to SammyB's nice snippet.

    Andrew

  8. #8
    New Lounger
    Join Date
    May 2001
    Location
    New Jersey, New Jersey, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Formula (2000)

    An easier way to do this would be to use FormulaR1C1
    eg. ActiveCell.FormulaR1C1 = Range("d10").FormulaR1C1

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel VBA Formula (2000)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > need to repeat for the next 16 columns
    <img src=/w3timages/blackline.gif width=33% height=2>
    Excel still reads your mind:
    <pre>Sub Macro1()
    Dim s As Range, d As Range
    Set s = Sheets("F").Range("B2")
    Set d = Sheets("R").Range("Z12:AO20")
    s.Copy d
    d = d.Value
    End Sub</pre>


    <img src=/w3timages/blackline.gif width=33% height=2>
    > change number of rows
    <img src=/w3timages/blackline.gif width=33% height=2>
    Then, it is best to use the Cells method, like Chorley:
    <pre>Sub Macro2()
    Dim s As Range, d As Range
    Dim ir As Integer, ic As Integer
    ir = 9
    ic = 16
    Set s = Sheets("F").Range("B2")
    With Sheets("R")
    Set d = .Range(.Cells(12, 26), .Cells(11 + ir, 25 + ic))
    End With
    s.Copy d
    d.Select
    d = d.Value
    End Sub</pre>


    <img src=/w3timages/blackline.gif width=33% height=2>
    > 'cleaniness' of your code is amazing
    <img src=/w3timages/blackline.gif width=33% height=2>
    Self defense: just fewer lines of code to debug <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Formula (2000)

    Thanks for your help. Excellent solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •