Solved

Copy range if range>0

Posted on 2012-03-31
6
285 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

895 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

12 Experts available now in Live!

Get 1:1 Help Now