Solved

Copy range if range>0

Posted on 2012-03-31
6
288 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

679 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