Solved

Copy range if range>0

Posted on 2012-03-31
6
284 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 41

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 41

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 41

Accepted Solution

by:
dlmille earned 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now