Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
range only if A3=1
Hi hopefully someone can help me with this. The following is some VBA code that was written however when anything changes on row 3 it automatically drops down. I don't want anything to drop down unless A3 = 1, can anyone help me?Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range(Target.Address), Me.Range("A3:H3")) _ Is Nothing Then Me.Cells(Me.Range("A:A").Rows.Count, Target.Column).Clear Dim rgOldValues As Range Dim iLastRow As Long iLastRow = Me.Cells(Columns(Target.Column).Rows.Count, Target.Column) _ .End(xlUp).row Application.EnableEvents = False Select Case iLastRow Case 1 Case 2 Case 3 Range("A4:H4").Value = Range("A3:H3").Value Range("C4").Value = Now Cells(4, Target.Column).Value = Cells(3, Target.Column).Value Case Else vaOldValues = Me.Range("A4:H" & _ IIf(iLastRow = 4, 5, iLastRow)) Range("A5:H5").Resize(UBound(vaOldValues, 1), 6).Value = _ vaOldValues Range("A4:H4").Value = Range("A3:H3").Value Range("C4").Value = Now Set rgOldValues = Me.Range(Cells(Target.row + 2, Target.Column), _ Cells(iLastRow, Target.Column)) Cells(4, Target.Column).Value = Cells(3, Target.Column).Value End Select Application.EnableEvents = True End If End Sub -- mikespeck ------------------------------------------------------------------------ mikespeck's Profile: http://www.excelforum.com/member.php...o&userid=34946 View this thread: http://www.excelforum.com/showthread...hreadid=569464 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
range only if A3=1
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A3").Value <1 Then Exit Sub End If .. .. .. will cause the macro to do nothing unless A3=1 -- Gary's Student "mikespeck" wrote: Hi hopefully someone can help me with this. The following is some VBA code that was written however when anything changes on row 3 it automatically drops down. I don't want anything to drop down unless A3 = 1, can anyone help me?Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range(Target.Address), Me.Range("A3:H3")) _ Is Nothing Then Me.Cells(Me.Range("A:A").Rows.Count, Target.Column).Clear Dim rgOldValues As Range Dim iLastRow As Long iLastRow = Me.Cells(Columns(Target.Column).Rows.Count, Target.Column) _ .End(xlUp).row Application.EnableEvents = False Select Case iLastRow Case 1 Case 2 Case 3 Range("A4:H4").Value = Range("A3:H3").Value Range("C4").Value = Now Cells(4, Target.Column).Value = Cells(3, Target.Column).Value Case Else vaOldValues = Me.Range("A4:H" & _ IIf(iLastRow = 4, 5, iLastRow)) Range("A5:H5").Resize(UBound(vaOldValues, 1), 6).Value = _ vaOldValues Range("A4:H4").Value = Range("A3:H3").Value Range("C4").Value = Now Set rgOldValues = Me.Range(Cells(Target.row + 2, Target.Column), _ Cells(iLastRow, Target.Column)) Cells(4, Target.Column).Value = Cells(3, Target.Column).Value End Select Application.EnableEvents = True End If End Sub -- mikespeck ------------------------------------------------------------------------ mikespeck's Profile: http://www.excelforum.com/member.php...o&userid=34946 View this thread: http://www.excelforum.com/showthread...hreadid=569464 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
range only if A3=1 | Excel Worksheet Functions | |||
Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
Can a formula check for a certain value in a range? | Excel Discussion (Misc queries) |