cameron213
12-05-2013, 01:24 PM
I am creating a macro enable spreadsheet to help me keep track of support tickets that I am currently working on in my engineering position.
I have 16 columns that provide information about each support ticket, such as Date, Serial Number of the device, Device location and so forth. A new row is a new support ticket.
Let's say we are working with row 50.
Column E represents the status of the device for which the ticket has been opened. Such as Deployed, Configure, and Floor Install.
Column F represents the device type such as Exalogic, and Exadata
Column K represents whether or not I need to create a special ticket depending on the value of E "OR" the value of F. Column K's values can be either yes or no.
This is what I have thus far and it does work with the column E range.
Dim rng As Range
Dim cellE
Dim cellF
If Not Intersect(Range("E12:E100"), Target) Is Nothing Then
Application.EnableEvents = False
For Each rng In Intersect(Range("E12:E100"), Target)
Set cellE = Range("E" & rng.Row)
Set cellK = Range("K" & rng.Row)
If Range("E" & rng.Row) = "Configure" Then
Range("K" & rng.Row) = "No"
ElseIf Range("E" & rng.Row).Value Like "*Floor*" Then
Range("K" & rng.Row) = "No"
Else
Range("K" & rng.Row) = ""
End If
Next rng
Application.EnableEvents = True
End If
However, if I try adding in the F range, nothing works:
If Not Intersect(Range("E12:E100"), Range("F12:F100"), Target) Is Nothing Then
Application.EnableEvents = False
For Each rng In Intersect(Range("E12:E100"), Range("F12:F100"), Target)
Set cellE = Range("E" & rng.Row)
Set cellF = Range("F" & rng.Row)
Set cellK = Range("K" & rng.Row)
If Range("E" & rng.Row) = "Configure" Then
Range("K" & rng.Row) = "No"
ElseIf Range("E" & rng.Row).Value Like "*Floor*" Then
Range("K" & rng.Row) = "No"
ElseIf Range("F" & rng.Row).Value Like "*Exa*" Then
'Range("K" & rng.Row) = "Not Needed"
Else
Range("K" & rng.Row) = ""
End If
Next rng
Application.EnableEvents = True
End If
Any help is greatly appreciated
I have 16 columns that provide information about each support ticket, such as Date, Serial Number of the device, Device location and so forth. A new row is a new support ticket.
Let's say we are working with row 50.
Column E represents the status of the device for which the ticket has been opened. Such as Deployed, Configure, and Floor Install.
Column F represents the device type such as Exalogic, and Exadata
Column K represents whether or not I need to create a special ticket depending on the value of E "OR" the value of F. Column K's values can be either yes or no.
This is what I have thus far and it does work with the column E range.
Dim rng As Range
Dim cellE
Dim cellF
If Not Intersect(Range("E12:E100"), Target) Is Nothing Then
Application.EnableEvents = False
For Each rng In Intersect(Range("E12:E100"), Target)
Set cellE = Range("E" & rng.Row)
Set cellK = Range("K" & rng.Row)
If Range("E" & rng.Row) = "Configure" Then
Range("K" & rng.Row) = "No"
ElseIf Range("E" & rng.Row).Value Like "*Floor*" Then
Range("K" & rng.Row) = "No"
Else
Range("K" & rng.Row) = ""
End If
Next rng
Application.EnableEvents = True
End If
However, if I try adding in the F range, nothing works:
If Not Intersect(Range("E12:E100"), Range("F12:F100"), Target) Is Nothing Then
Application.EnableEvents = False
For Each rng In Intersect(Range("E12:E100"), Range("F12:F100"), Target)
Set cellE = Range("E" & rng.Row)
Set cellF = Range("F" & rng.Row)
Set cellK = Range("K" & rng.Row)
If Range("E" & rng.Row) = "Configure" Then
Range("K" & rng.Row) = "No"
ElseIf Range("E" & rng.Row).Value Like "*Floor*" Then
Range("K" & rng.Row) = "No"
ElseIf Range("F" & rng.Row).Value Like "*Exa*" Then
'Range("K" & rng.Row) = "Not Needed"
Else
Range("K" & rng.Row) = ""
End If
Next rng
Application.EnableEvents = True
End If
Any help is greatly appreciated