jmccafferty
New Member
- Joined
- Jun 24, 2013
- Messages
- 2
I have been asked to combine these two Worksheet_Change codes, but have no Idea how. I'm the department excel guy, but most of what I do is borrow from those willing to show they're code online. Well I have a problem, after researching the sites from Google, I've learned that I need to combine the two codes. But all the examples don't just combine them, they rewrite them. I have no knowledge of the structure, except to say I can usually make them do what I want. Can anyone help me out on combining the following code?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aTabOrd(1 To 120) As String
Dim i As Long
'Set the tab order of input cells
aTabOrd(1) = "C8": aTabOrd(2) = "C9": aTabOrd(3) = "C10": aTabOrd(4) = "D8"
aTabOrd(5) = "D9": aTabOrd(6) = "D10": aTabOrd(7) = "E8": aTabOrd(8) = "E9"
aTabOrd(9) = "E10": aTabOrd(10) = "F8": aTabOrd(11) = "F9": aTabOrd(12) = "F10"
aTabOrd(13) = "C12": aTabOrd(14) = "C13": aTabOrd(15) = "C14": aTabOrd(16) = "D12"
aTabOrd(17) = "D13": aTabOrd(18) = "D14": aTabOrd(19) = "E12": aTabOrd(20) = "E13"
aTabOrd(21) = "E14": aTabOrd(22) = "F12": aTabOrd(23) = "F13": aTabOrd(24) = "F14"
aTabOrd(25) = "C16": aTabOrd(26) = "C17": aTabOrd(27) = "C18": aTabOrd(28) = "D16"
aTabOrd(29) = "D17": aTabOrd(30) = "D18": aTabOrd(31) = "E16": aTabOrd(32) = "E17"
aTabOrd(33) = "E18": aTabOrd(34) = "F16": aTabOrd(35) = "F17": aTabOrd(36) = "F18"
aTabOrd(37) = "C20": aTabOrd(38) = "C21": aTabOrd(39) = "C22": aTabOrd(40) = "D20"
aTabOrd(41) = "D21": aTabOrd(42) = "D22": aTabOrd(43) = "E20": aTabOrd(44) = "E21"
aTabOrd(45) = "E22": aTabOrd(46) = "F20": aTabOrd(47) = "F21": aTabOrd(48) = "F22"
aTabOrd(49) = "C24": aTabOrd(50) = "C25": aTabOrd(51) = "C26": aTabOrd(52) = "D24"
aTabOrd(53) = "D25": aTabOrd(54) = "D26": aTabOrd(55) = "E24": aTabOrd(56) = "E25"
aTabOrd(57) = "E26": aTabOrd(58) = "F24": aTabOrd(59) = "F25": aTabOrd(60) = "F26"
aTabOrd(61) = "C28": aTabOrd(62) = "C29": aTabOrd(63) = "C30": aTabOrd(64) = "D28"
aTabOrd(65) = "D29": aTabOrd(66) = "D30": aTabOrd(67) = "E28": aTabOrd(68) = "E29"
aTabOrd(69) = "E30": aTabOrd(70) = "F28": aTabOrd(71) = "F29": aTabOrd(72) = "F30"
aTabOrd(73) = "C32": aTabOrd(74) = "C33": aTabOrd(75) = "C34": aTabOrd(76) = "D32"
aTabOrd(77) = "D33": aTabOrd(78) = "D34": aTabOrd(79) = "E32": aTabOrd(80) = "E33"
aTabOrd(81) = "E34": aTabOrd(82) = "F32": aTabOrd(83) = "F33": aTabOrd(84) = "F34"
aTabOrd(85) = "C36": aTabOrd(86) = "C37": aTabOrd(87) = "C38": aTabOrd(88) = "D36"
aTabOrd(89) = "D37": aTabOrd(90) = "D38": aTabOrd(91) = "E36": aTabOrd(92) = "E37"
aTabOrd(93) = "E38": aTabOrd(94) = "F36": aTabOrd(95) = "F37": aTabOrd(96) = "F38"
aTabOrd(97) = "C40": aTabOrd(98) = "C41": aTabOrd(99) = "C42": aTabOrd(100) = "D40"
aTabOrd(101) = "D41": aTabOrd(102) = "D42": aTabOrd(103) = "E40": aTabOrd(104) = "E41"
aTabOrd(105) = "E42": aTabOrd(106) = "F40": aTabOrd(107) = "F41": aTabOrd(108) = "F42"
aTabOrd(109) = "C44": aTabOrd(110) = "C45": aTabOrd(111) = "C46": aTabOrd(112) = "D44"
aTabOrd(113) = "D45": aTabOrd(114) = "D46": aTabOrd(115) = "E44": aTabOrd(116) = "E45"
aTabOrd(117) = "E46": aTabOrd(118) = "F44": aTabOrd(119) = "F45": aTabOrd(120) = "F46"
'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that’s changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that’s changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i
End Sub
AND
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2:B6,C2:C6,D2:D6")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value < 0 Then Target = Target.Value * -1
End Sub
Thanks,
Jim
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aTabOrd(1 To 120) As String
Dim i As Long
'Set the tab order of input cells
aTabOrd(1) = "C8": aTabOrd(2) = "C9": aTabOrd(3) = "C10": aTabOrd(4) = "D8"
aTabOrd(5) = "D9": aTabOrd(6) = "D10": aTabOrd(7) = "E8": aTabOrd(8) = "E9"
aTabOrd(9) = "E10": aTabOrd(10) = "F8": aTabOrd(11) = "F9": aTabOrd(12) = "F10"
aTabOrd(13) = "C12": aTabOrd(14) = "C13": aTabOrd(15) = "C14": aTabOrd(16) = "D12"
aTabOrd(17) = "D13": aTabOrd(18) = "D14": aTabOrd(19) = "E12": aTabOrd(20) = "E13"
aTabOrd(21) = "E14": aTabOrd(22) = "F12": aTabOrd(23) = "F13": aTabOrd(24) = "F14"
aTabOrd(25) = "C16": aTabOrd(26) = "C17": aTabOrd(27) = "C18": aTabOrd(28) = "D16"
aTabOrd(29) = "D17": aTabOrd(30) = "D18": aTabOrd(31) = "E16": aTabOrd(32) = "E17"
aTabOrd(33) = "E18": aTabOrd(34) = "F16": aTabOrd(35) = "F17": aTabOrd(36) = "F18"
aTabOrd(37) = "C20": aTabOrd(38) = "C21": aTabOrd(39) = "C22": aTabOrd(40) = "D20"
aTabOrd(41) = "D21": aTabOrd(42) = "D22": aTabOrd(43) = "E20": aTabOrd(44) = "E21"
aTabOrd(45) = "E22": aTabOrd(46) = "F20": aTabOrd(47) = "F21": aTabOrd(48) = "F22"
aTabOrd(49) = "C24": aTabOrd(50) = "C25": aTabOrd(51) = "C26": aTabOrd(52) = "D24"
aTabOrd(53) = "D25": aTabOrd(54) = "D26": aTabOrd(55) = "E24": aTabOrd(56) = "E25"
aTabOrd(57) = "E26": aTabOrd(58) = "F24": aTabOrd(59) = "F25": aTabOrd(60) = "F26"
aTabOrd(61) = "C28": aTabOrd(62) = "C29": aTabOrd(63) = "C30": aTabOrd(64) = "D28"
aTabOrd(65) = "D29": aTabOrd(66) = "D30": aTabOrd(67) = "E28": aTabOrd(68) = "E29"
aTabOrd(69) = "E30": aTabOrd(70) = "F28": aTabOrd(71) = "F29": aTabOrd(72) = "F30"
aTabOrd(73) = "C32": aTabOrd(74) = "C33": aTabOrd(75) = "C34": aTabOrd(76) = "D32"
aTabOrd(77) = "D33": aTabOrd(78) = "D34": aTabOrd(79) = "E32": aTabOrd(80) = "E33"
aTabOrd(81) = "E34": aTabOrd(82) = "F32": aTabOrd(83) = "F33": aTabOrd(84) = "F34"
aTabOrd(85) = "C36": aTabOrd(86) = "C37": aTabOrd(87) = "C38": aTabOrd(88) = "D36"
aTabOrd(89) = "D37": aTabOrd(90) = "D38": aTabOrd(91) = "E36": aTabOrd(92) = "E37"
aTabOrd(93) = "E38": aTabOrd(94) = "F36": aTabOrd(95) = "F37": aTabOrd(96) = "F38"
aTabOrd(97) = "C40": aTabOrd(98) = "C41": aTabOrd(99) = "C42": aTabOrd(100) = "D40"
aTabOrd(101) = "D41": aTabOrd(102) = "D42": aTabOrd(103) = "E40": aTabOrd(104) = "E41"
aTabOrd(105) = "E42": aTabOrd(106) = "F40": aTabOrd(107) = "F41": aTabOrd(108) = "F42"
aTabOrd(109) = "C44": aTabOrd(110) = "C45": aTabOrd(111) = "C46": aTabOrd(112) = "D44"
aTabOrd(113) = "D45": aTabOrd(114) = "D46": aTabOrd(115) = "E44": aTabOrd(116) = "E45"
aTabOrd(117) = "E46": aTabOrd(118) = "F44": aTabOrd(119) = "F45": aTabOrd(120) = "F46"
'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that’s changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that’s changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i
End Sub
AND
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2:B6,C2:C6,D2:D6")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value < 0 Then Target = Target.Value * -1
End Sub
Thanks,
Jim