New feature! Upgrade and increase expert visibility of your issues with Priority Questions.
Solved
Adjustment to Macro when Clearing a Case
Posted on 2014-12-13
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
2
5 Comments
Active 3 days ago
the issue is that the 'UseCase' cells on Use_Case_Priority are references to Validation_DB.
yet the Factor Cells are hard coded. I will see if amending the 'sumup' routine is possible
yet the Factor Cells are hard coded. I will see if amending the 'sumup' routine is possible
Active 3 days ago
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
Active today
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Suggested Courses
Course of the Month6 days, 22 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.