The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.
Adjustment to Macro when Clearing a Case
I have a great piece of work that Glenn Ray (EE) did for me to help me with a change to a fairly advanced macro. I need one additional capability built into the macro on the attached WB.
When you open it you will see a set of rows with the ability to click on the black box (E8......First Tab) and a checkmark will appear. All selections will show up on the following tab automatically as you select rows. What Glenn did for me is give me the ability to reverse the selection. In other words, if I de-selected a row by clicking on a box already checked, it would remove it from the next tab.
What I need help with is if a selection is removed, the other selections need to move up (so there are no gaps) and the 3 columns on the second tab are cleared/erased if a particular row on the first tab is deselected. -- That's it!
Your assistance is appreciated and "thank you" in advance.
B.
Copy-of-EE-Fix-Check-Marks-v4.xlsm
When you open it you will see a set of rows with the ability to click on the black box (E8......First Tab) and a checkmark will appear. All selections will show up on the following tab automatically as you select rows. What Glenn did for me is give me the ability to reverse the selection. In other words, if I de-selected a row by clicking on a box already checked, it would remove it from the next tab.
What I need help with is if a selection is removed, the other selections need to move up (so there are no gaps) and the 3 columns on the second tab are cleared/erased if a particular row on the first tab is deselected. -- That's it!
Your assistance is appreciated and "thank you" in advance.
B.
Copy-of-EE-Fix-Check-Marks-v4.xlsm
Asked:
3-
2
1 Solution
try adding
Copy-of-EE-Fix-Check-Marks-v5.xlsm
'now fix the Use_Priority sheet
Dim ws As Worksheet, wscell As Range
Set ws = ActiveWorkbook.Sheets("Use_Case_Priority")
For Each wscell In ws.Range("B5..B10")
If wscell.Value = "" Then
'its empty so empty cells to right
wscell.Offset(0, 1).Resize(1, 3).ClearContents
End If
Next wscell
Copy-of-EE-Fix-Check-Marks-v5.xlsm
Like this? I tried this but couldn't get it to work.............
Sub sumup()
Const Input_Range = 5
'3
Const Output_Range = 800
'17
Const Output_CS = 7
'18
Const Output_CE = 1
Const Input_CM = 5
Dim O_R As Long
'Output Row
Dim I_R As Long
'Input Row
Dim Total As Double
'Total
'**** Clear the previous output ****
With Worksheets("Validation_DB"
.Range("G800:G812") = ""
End With
O_R = Cells(Rows.Count, Output_CS).End(xlUp).Row
If O_R >= Output_Range Then
'Column and Row Output
Range(Cells(Output_Range, Output_CS), Cells(O_R, Output_CE)).ClearContents
End If
'Column Explanation
O_R = Output_Range
'Where the checkmark is
I_R = Cells(Rows.Count, Input_CM).End(xlUp).Row
If I_R >= Input_Range Then
'Input row
For I = Input_Range To I_R
With Cells(I, Input_CM)
If .Value = "P" Then
'Output results here
Sheets("Validation_DB").Ce
Sheets("Validation_DB").Ce
Sheets("Validation_DB").Ce
"_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
If Not IsError(.Offset(0, 5).Value) Then Total = Total + .Offset(0, 5).Value
O_R = O_R + 1
End If
End With
Next
Cells(Output_Range - 1, Output_CS).Value = "Total"
With Cells(Output_Range - 1, Output_CE)
.FormulaR1C1 = "=SUM(R[1]C:R[" & O_R & "]C)"
.NumberFormatLocal = _
"_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
End With
End If
'now fix the Use_Priority sheet
Dim ws As Worksheet, wscell As Range
Set ws = ActiveWorkbook.Sheets("Use
For Each wscell In ws.Range("B5..B10")
If wscell.Value = "" Then
'its empty so empty cells to right
wscell.Offset(0, 1).Resize(1, 3).ClearContents
End If
Next wscell
End Sub
Sub sumup()
Const Input_Range = 5
'3
Const Output_Range = 800
'17
Const Output_CS = 7
'18
Const Output_CE = 1
Const Input_CM = 5
Dim O_R As Long
'Output Row
Dim I_R As Long
'Input Row
Dim Total As Double
'Total
'**** Clear the previous output ****
With Worksheets("Validation_DB"
.Range("G800:G812") = ""
End With
O_R = Cells(Rows.Count, Output_CS).End(xlUp).Row
If O_R >= Output_Range Then
'Column and Row Output
Range(Cells(Output_Range, Output_CS), Cells(O_R, Output_CE)).ClearContents
End If
'Column Explanation
O_R = Output_Range
'Where the checkmark is
I_R = Cells(Rows.Count, Input_CM).End(xlUp).Row
If I_R >= Input_Range Then
'Input row
For I = Input_Range To I_R
With Cells(I, Input_CM)
If .Value = "P" Then
'Output results here
Sheets("Validation_DB").Ce
Sheets("Validation_DB").Ce
Sheets("Validation_DB").Ce
"_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
If Not IsError(.Offset(0, 5).Value) Then Total = Total + .Offset(0, 5).Value
O_R = O_R + 1
End If
End With
Next
Cells(Output_Range - 1, Output_CS).Value = "Total"
With Cells(Output_Range - 1, Output_CE)
.FormulaR1C1 = "=SUM(R[1]C:R[" & O_R & "]C)"
.NumberFormatLocal = _
"_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
End With
End If
'now fix the Use_Priority sheet
Dim ws As Worksheet, wscell As Range
Set ws = ActiveWorkbook.Sheets("Use
For Each wscell In ws.Range("B5..B10")
If wscell.Value = "" Then
'its empty so empty cells to right
wscell.Offset(0, 1).Resize(1, 3).ClearContents
End If
Next wscell
End Sub
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
yet the Factor Cells are hard coded. I will see if amending the 'sumup' routine is possible