Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 24th, 2013, 11:30 AM
Registered User
 
Join Date: Mar 2013
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Combine 2 worksheet_change functions into one

I have these 2 pieces of code that work great individually, but i need them both on the same worksheet and I cannot figure out how to merge them without errors..

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value Like "Moved to last planner" Then

Sheets("Last Planner").Range("A" & Rows.Count).Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value
Else
If Target.Value <> "Moved to last planner" Then
End If
End If
End Sub
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
For i = 1 To 30
If Target.Value Like "Not Implemented" Then

If Sheets("Last Planner").Range("A" & i) = Target.Offset(0, 2 - Target.Column()).Value Then
Sheets("Last Planner").Range("A" & i) = ""
Else

End If
End If
Next i
End Sub
I just need these merged into a single worksheet_change event...any help would be awesome. I'm losing my mind with this issue...
 
Old March 28th, 2013, 02:25 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Since the two pieces of code needs to be run based on Target. Value you can use something similar to the following:

Code:
If Target.Value Like "Moved to last planner" Then

Sheets("Last Planner").Range("A" & Rows.Count).Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value

ElseIf Target.Value =  "Not Implemented" Then

If Sheets("Last Planner").Range("A" & i) = Target.Offset(0, 2 - Target.Column()).Value Then
Sheets("Last Planner").Range("A" & i) = ""
End If

End If
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 WorkSheet_Change Armand Excel VBA 2 October 26th, 2010 07:14 PM
Please help!!!!!Private Sub Worksheet_Change VicM Excel VBA 1 March 6th, 2008 10:35 PM
Worksheet_Change executing selectively lfrench Excel VBA 3 April 3rd, 2007 03:59 PM
Excel Worksheet_Change option mtowle Excel VBA 1 October 21st, 2005 09:27 AM
How to step into Worksheet_Change Piblon Excel VBA 2 December 17th, 2003 04:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.