Results 1 to 11 of 11

Thread: Current Value

  1. #1
    Join Date
    Mar 2010
    Posts
    27

    Unanswered: Current Value

    I have what I thought would be an easy problem to solve...and have failed miserably... hopefully somebody can help me out.

    I have 2 sheets in the same workbook, and want to copy from one sheet to another... kinda.

    If A1 has a value of 3 when I run the macro, I want the 2nd sheet's A1 to be: =Sheet1!A1-3.
    If A1 has a value of 4 when I run the macro, I want the 2nd sheet's A1 to be: =Sheet1!A1-4, etc

    However, if/when A1 increases to 10, the 2nd sheet's A1 should increase to 7 (using the first example there).

    All of my attempts have ended up just having a 0 (Sheet1!A1-Sheet1!A1 type thing).

    Any ideas?

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    A general example:
    Code:
    Sub foo()
        Dim rngSource As Range
        Dim rngTarget As Range
        Dim strFormula As String
        
        Set rngSource = Worksheets("Sheet1").Range("A1")
        Set rngTarget = Worksheets("Sheet2").Range("A1")
        
        strFormula = "=" & rngSource.Address(external:=True)
        If Not VBA.IsEmpty(rngSource) Then strFormula = strFormula & "-" & rngSource.Value2
        
        rngTarget.Formula = strFormula
        
    End Sub
    Hope that helps...

  3. #3
    Join Date
    Mar 2010
    Posts
    27
    That works perfectly. I'll be honest and say I don't understand it. lol. Well above my knowledge level, but it works and I can edit for the cells as needed.

    Thanks a ton!

    ***edit

    Works perfectly for one cell. I actually need it for 4 different cells with different values. I'll actually need to change the source each day, but I can just make six different ones, that's no big deal... but how do I make it work for 4 different cells?
    Last edited by nogoodnames; 06-09-10 at 17:57. Reason: I was wrong first time. :)

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    I'll be honest and say I don't understand it. lol.
    Which bits in particular are confusing? I'd be happy to discuss them in more detal for you...
    I actually need it for 4 different cells with different values. I'll actually need to change the source each day, but I can just make six different ones, that's no big deal... but how do I make it work for 4 different cells?
    Which four cells? (It makes a difference whether or not they are a contiguous or non-contiguous range).
    Last edited by Colin Legg; 06-09-10 at 18:01.

  5. #5
    Join Date
    Mar 2010
    Posts
    27
    This part:

    Code:
    strFormula = "=" & rngSource.Address(external:=True)
        If Not VBA.IsEmpty(rngSource) Then strFormula = strFormula & "-" & rngSource.Value2
    lol.

    I actually think I understand it now that I've looked at it a bunch more when trying to explain what I didn't understand. Haha.

    The .Address(external:=True) how does that work?

    And does Value2 just take current value I presume?


    ***edit (you're quick!)

    The 4 cells are not contiguous. They are B7,B21,B27 and B33 (I believe - The actual workbook is not with me right now)

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    The .Address(external:=True) how does that work?
    Range.Address(External:=True) returns the external reference of the range as a string. For example, Range("A1").Address(External:=False) would return "A1", whereas Range("A1").Address(External:=True) returns something like "[Book1]Sheet1!$A$1". External:=False is the default, so if you omit it you will just get "A1". Since we want to reference the range on another sheet within the formula I used External:=True.
    And does Value2 just take current value I presume?
    Yes, Range.Value2 is very similar to Range.Value.

    The 4 cells are not contiguous. They are B7,B21,B27 and B33 (I believe - The actual workbook is not with me right now)
    Okay, you can use a For Each.... Next loop to iterate through those cells, for example:
    Code:
    Sub foo()
        Dim rngSource As Range
        Dim rngCell As Range
        Dim strFormula As String
     
        Set rngSource = Worksheets("Sheet1").Range("B7,B21,B27,B33")
     
        For Each rngCell In rngSource.Cells
     
            strFormula = "=" & rngCell.Address(external:=True)
            If Not VBA.IsEmpty(rngCell) Then strFormula = strFormula & "-" & rngCell.Value2
     
            Worksheets("Sheet2").Range(rngCell.Address).Formula = strFormula
     
        Next rngCell
     
    End Sub
    Hope that makes sense!

  7. #7
    Join Date
    Mar 2010
    Posts
    27
    That does indeed make sense, and looks as though it'll work.

    But... will it only be filling in the same cell on sheet2?

    I need the macro to output onto different cells.

    Example:

    sheet1 has B7=4
    sheet1 has B21=9

    I need it in:
    sheet2 in F6 to be Sheet1!B7-4
    sheet2 in G30 to be Sheet1!B21-9

    I hope that makes sense... it looks confusing to me and I've just written it and know what I mean. lol

    I'm sorry to be a pain, but as always, you've been fantastic. Thanks in advance!

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    But... will it only be filling in the same cell on sheet2?
    Yes, it would.
    I need the macro to output onto different cells.
    Okay, can you give me all the mappings for the cells? Is there any sort of pattern or are they (effectively) random?
    ie.
    Sheet2!F6 --> Sheet1!B7-4
    Sheet2!G30 --> Sheet1!B21-9
    ?
    ?

  9. #9
    Join Date
    Mar 2010
    Posts
    27
    Yes. That I can do, although yes, effectively random. I've got a copy of the workbook from a colleague:

    Sheet1!B7 ----> Sheet2!D21
    Sheet1!B14 ---> Sheet2!B21
    Sheet1!B23 ---> Sheet2!D22
    Sheet1!B30 ---> Sheet2!B22

    I mentioned earlier that I'd need to make 6 different ones depending on the day, which is no problem. Those days, the source changes (by 2 columns), but the destination remains the same.

  10. #10
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    I think this can be improved upon, but let's use it as a starting point to see if it does the job?

    Code:
    Sub foo()
        Dim colSource As Collection
        Dim colTarget As Collection
        Dim rngCell As Range
        Dim rngArea As Range
     
        Dim l As Long
        Dim strFormula As String
     
        Set colSource = New Collection
        Set colTarget = New Collection
     
        For Each rngArea In Worksheets("Sheet1").Range("B7,B14,B23,B30").Areas
            For Each rngCell In rngArea.Cells
                colSource.Add rngCell
            Next rngCell
        Next rngArea
     
        For Each rngArea In Worksheets("Sheet2").Range("D21,B21,D22,B22").Areas
            For Each rngCell In rngArea.Cells
                colTarget.Add rngCell
            Next rngCell
        Next rngArea
     
     
        For l = 1 To colSource.Count
            strFormula = "=" & colSource(l).Address(external:=True)
            If Not VBA.IsEmpty(colSource(l)) Then strFormula = strFormula & "-" & colSource(l).Value2
     
            colTarget(l).Formula = strFormula
        Next l
    End Sub
    I mentioned earlier that I'd need to make 6 different ones depending on the day, which is no problem. Those days, the source changes (by 2 columns), but the destination remains the same.
    If there's a predefined logic then that could be accounted for automatically within the code too.
    I'm off to bed now but I'll take another look at this tomorrow.

  11. #11
    Join Date
    Mar 2010
    Posts
    27
    I'm getting 'Subscript out of Range' error.. highlighting this line:

    Code:
    For Each rngArea In Worksheets("Sheet2").Range("D21,B21,D22,B22").Areas
    ***edit 1

    Wait.. probably my mistake... hang on, not called 'sheet2'. lol. 2 secs


    ***edit 2

    Yep, changed the name and it worked perfectly. I can edit the 'source' range for each day and just have a different one for each day as I need it. That's perfect man. Thanks a ton for the help and sorry it took so long to get through my thick skull! I'm away to bed. Thanks again!
    Last edited by nogoodnames; 06-09-10 at 19:24.

Posting Permissions

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