Copy range if range>0

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!
mike637Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
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
mike637Author Commented:
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
dlmilleCommented:
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
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

mike637Author Commented:
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
dlmilleCommented:
You may need to supply some data.  Here's an example where its working properly on my end.

Dave
example-r1.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mike637Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.