Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Copy range if range>0

Posted on 2012-03-31
6
Medium Priority
?
297 Views
Last Modified: 2012-06-27
Hello Experts,

Once again I turn to you for your expert help.  I need to have my worksheet populate values from another worksheet if the Range.Value >0 and to add a "+" character in front of the value.

I will always be working in the same workbook.

Sheet2 is destination worksheet
Sheet1 is source worksheet

There are 23 Lines to calculate

The formulas are as such:
Sheet2.Range("L7") = Sheet1.Range(K5+K7+K8)
Sheet2.Range("L8") = Sheet1.Range(M5+M7+M8)
Sheet2.Range("L9") = Sheet1.Range(O5+O7+O8)
...... continues on for a total of 23 lines of data

Sheet2 is Column L (12) + (row 7 to 29)

Sheet1 has variable columns that increase by 2. Starting at Column K (11) then + 2 for the next.
Sheet 1 will always be rows 5, 7, 8 for the calculation.

I am having issues trying to estabish the Range.Cells equation to get this to populate correctly. Then the issue if Sheet1.Range.Value = 0 then it does nothing, or if Sheet1.Range.value>0 then character "+" followed by the total of Sheet1.Range.value.

I hope explained myself correctly.  If you need additional information - please let me know.

Thank You!
0
Comment
Question by:mike637
  • 3
  • 3
6 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37792071
Try this:

Sub doWork()
Dim wkb As Workbook
Dim wksSrc As Worksheet
Dim wksDest As Worksheet
Dim rFirst As Range
Dim rSecond As Range
Dim rThird As Range
Dim i As Long
Dim rng As Range
Dim r As Range
Dim dResult As Double

    Set wkb = ThisWorkbook
    Set wksSrc = wkb.Sheets("Sheet1")
    Set wksDest = wkb.Sheets("Sheet2")
    
    Set rng = wksDest.Range("L7:L" & 7 + 22)
    
    Set rFirst = wksSrc.Range("K5")
    Set rSecond = wksSrc.Range("K7")
    Set rThird = wksSrc.Range("K8")
    
    For Each r In rng
        dResult = rFirst.Offset(, i).Value + rSecond.Offset(, i).Value + rThird.Offset(, i).Value
        
        If dResult > 0 Then
            r.Formula = "'+" & dResult
        Else
            r.Value = vbNullString
        End If
        
        i = i + 2
    Next r
    
    
End Sub

Open in new window


Is this what you're looking for?

Cheers,

Dave
0
 

Author Comment

by:mike637
ID: 37792103
Hi Dave,

This does everything it needs to do - but I do need it to not populate if the value equals "0",  I tried to set the conditional formatting to hide it - but no luck.

Is there anything that can be added to the code work around this issue?

Thanks,
Michael
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37792107
I uploaded a mod that should have fixed that probably after you copied and tested

Can you check to see if you are using the current code posted?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:mike637
ID: 37792147
Hi Dave,

I am almost there.  No, I was not using your modifed version.  I am now.  

However, on the first and modified version - I get the first value for sheet2.L7 - but the remaining values come in as zero even though there is data in the source.cells. (In your modified version, all the remaining cells are blank - so I know that part of the code is working properly).

Michael
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37792151
You may need to supply some data.  Here's an example where its working properly on my end.

Dave
example-r1.xls
0
 

Author Closing Comment

by:mike637
ID: 37792185
Hi Dave,

I see what my problem was - it was due to merged cells in Sheet1 on my side.  I needed to unmerge these then it was fine.

Sorry for the ignorance on my part.

Michael
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question