Discussion Article

[ Home | Contents | Search | Reply | Previous | Next ]

Subject:
Recognising Cell Values
Name:
John R Spain
Email (spam proof):
Email the originator of this post
Date:
Thursday, October 19, 2006
Hits:
74
Discussion (Terms and Conditions):
'this code looks at the values in column 1 and then enters the values in columns 3 4 5. 'it works if a user enters "A" in the column 3 of the record to be copied - 'it needs to also recognise B C D E in column 3 of the record to be copied 'so instead of copying down A1C it would copy down B1C etc. Stuck on how to do it Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static myswitch ' this retains the value between calls If myswitch = 1 Then myswitch = 0 'reset the switch after activating Message = MsgBox("Is this repair a Cyclical repair", vbYesNo) If Message = vbYes Then PriorityYear = Cells(Target.Row, 1).Value MainYearID = Cells(Target.Row, 2).Value Condition = Cells(Target.Row, 3).Value AmpValue = Cells(Target.Row, 4).Value ConditionCode = Cells(Target.Row, 5).Value RepairRate = Cells(Target.Row, 6).Value Do response = InputBox("How many Cyclical Periods?" & vbCrLf & " " & vbCrLf & "2 = 10 years, 5 = Five Years, 9 = Three Years" & vbCrLf & " " & vbCrLf & "Enter 0 + Enter to cancel") Loop Until response = 2 Or response = 5 Or response = 9 Or response = 0 For I = 1 To response If response = 9 Then Cells(Target.Row + I, 1).Value = Cells(Target.Row + I - 1, 1).Value + 3 ' Cells(Target.Row + I, 2).Value = Cells(Target.Row + I - 1, 2).Value + 3 ' ElseIf response = 5 Then Cells(Target.Row + I, 1).Value = Cells(Target.Row + I - 1, 1).Value + 5 ' Cells(Target.Row + I, 2).Value = Cells(Target.Row + I - 1, 2).Value + 5 'This section increases years by response ElseIf response = 2 Then Cells(Target.Row + I, 1).Value = Cells(Target.Row + I - 1, 1).Value + 10 ' Cells(Target.Row + I, 2).Value = Cells(Target.Row + I - 1, 2).Value + 10 ' End If 'this code looks at the values in column 1 and then enters the values in columns 3 4 5. 'it works if a user enters "A" in the column 3 of the record to be copied - 'it needs to also recognise B C D E in column 3 of the record to copied 'so instead of copying down A1C it would copy down B1C etc. If Cells(Target.Row + I, 1).Value = 2006 Then Cells(Target.Row + I, 3).Value = "A" Cells(Target.Row + I, 4).Value = "1" Cells(Target.Row + I, 5).Value = "C" ElseIf Cells(Target.Row + I, 1).Value = 2007 Then Cells(Target.Row + I, 3).Value = "A" Cells(Target.Row + I, 4).Value = "2" Cells(Target.Row + I, 5).Value = "D" ElseIf Cells(Target.Row + I, 1).Value = 2008 Then Cells(Target.Row + I, 3).Value = "A" Cells(Target.Row + I, 4).Value = "2" Cells(Target.Row + I, 5).Value = "B" ElseIf Cells(Target.Row + I, 1).Value = 2009 Then Cells(Target.Row + I, 3).Value = "A" Cells(Target.Row + I, 4).Value = "3" Cells(Target.Row + I, 5).Value = "B" ElseIf Cells(Target.Row + I, 1).Value = 2010 Then Cells(Target.Row + I, 3).Value = "A" Cells(Target.Row + I, 4).Value = "3" Cells(Target.Row + I, 5).Value = "B" ElseIf Cells(Target.Row + I, 1).Value = 2011 Then Cells(Target.Row + I, 3).Value = "A" Cells(Target.Row + I, 4).Value = "4" Cells(Target.Row + I, 5).Value = "D" ElseIf Cells(Target.Row + I, 1).Value = 2012 Then Cells(Target.Row + I, 3).Value = "A" Cells(Target.Row + I, 4).Value = "4" Cells(Target.Row + I, 5).Value = "D" ElseIf Cells(Target.Row + I, 1).Value = 2013 Then Cells(Target.Row + I, 3).Value = "A" Cells(Target.Row + I, 4).Value = "4" Cells(Target.Row + I, 5).Value = "D" ElseIf Cells(Target.Row + I, 1).Value = 2014 Then Cells(Target.Row + I, 3).Value = "A" Cells(Target.Row + I, 4).Value = "4" Cells(Target.Row + I, 5).Value = "D" ElseIf Cells(Target.Row + I, 1).Value = 2015 Then Cells(Target.Row + I, 3).Value = "A" Cells(Target.Row + I, 4).Value = "4" Cells(Target.Row + I, 5).Value = "D" Else: Cells(Target.Row + I, 3).Value = "C" Cells(Target.Row + I, 4).Value = "3" Cells(Target.Row + I, 5).Value = "B" End If Cells(Target.Row + I, 6).Value = Cells(Target.Row + I - 1, 6) * 1.15 Next I Else: Message = vbNo End If End If If Target.Column = 7 Then myswitch = 1 ' set the switch after exiting cell Else: myswitch = 0 End If End Sub

Sponsored by the best FTP application on the internet: