Current Value

nogoodnames · #1 (**permalink**) 06-09-10, 14:11

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?

Colin Legg · #2 (**permalink**) 06-09-10, 16:03

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...

nogoodnames · #3 (**permalink**) 06-09-10, 16:47

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?

Colin Legg · #4 (**permalink**) 06-09-10, 16:57

Hi,

Quote:

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...

Quote:

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).

nogoodnames · #5 (**permalink**) 06-09-10, 17:09

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)

Colin Legg · #6 (**permalink**) 06-09-10, 17:30

Hi,

Quote:

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.

Quote:

And does Value2 just take current value I presume?

Yes, Range.Value2 is very similar to Range.Value.

Quote:

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!

nogoodnames · #7 (**permalink**) 06-09-10, 17:38

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!

Colin Legg · #8 (**permalink**) 06-09-10, 17:39

Hi,

Quote:

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

Yes, it would.

Quote:

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
?
?

nogoodnames · #9 (**permalink**) 06-09-10, 17:45

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.

Colin Legg · #10 (**permalink**) 06-09-10, 18:03

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

Quote:

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.

nogoodnames · #11 (**permalink**) 06-09-10, 18:08

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech