Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Excel VBA: isna procedure & smart range selection
Hello experts,
I have the procedure reporting at the end of my message that I use from my personal.xlsb in order to compare values from different columns.
I would like to enhance the procedure by modifying the following:
1. Instead of selecting the various range from top to down I would like to enter the column letter and the procedure should be able to display the range from initial top range till the last of used range and display input box “Range involved is equal to :”. This process should be applied for both columns
2. Finish the procedure with the procedure by a filtering cells based on Interior.ColorIndex = "3"
3. Display a Msgbox if the user cancels the procedure in of the range selection.
If you have questions, please contact me
I have the procedure reporting at the end of my message that I use from my personal.xlsb in order to compare values from different columns.
I would like to enhance the procedure by modifying the following:
1. Instead of selecting the various range from top to down I would like to enter the column letter and the procedure should be able to display the range from initial top range till the last of used range and display input box “Range involved is equal to :”. This process should be applied for both columns
2. Finish the procedure with the procedure by a filtering cells based on Interior.ColorIndex = "3"
3. Display a Msgbox if the user cancels the procedure in of the range selection.
If you have questions, please contact me
Sub Is_Na()
Application.DisplayAlerts = False
Dim c As Range
On Error GoTo exit_routine_isna
Set UsedRange = Application.InputBox(prompt:="Please Select your range source, in this range you will find the cells wich are not in your range to compare", Title:="Range Select", Type:=8)
Set ComparisonRange = Application.InputBox(prompt:="Please Select Range to compare", Title:="Range Select", Type:=8)
For Each c In UsedRange
If IsNumeric(Application.Match(c, ComparisonRange, 0)) Then
Else: c.EntireRow.
End If
Next c Interior.ColorIndex = "3"
exit_routine_isna:
End Sub
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I guess you posted the incorrect code.
In the code below if the value from Source Range is found in the Range To Compare, the entire row will be highlighted as green else it will be highlighted as red. If this logic is not correct, change the color index in the for loop.
You may try something like this...
In the code below if the value from Source Range is found in the Range To Compare, the entire row will be highlighted as green else it will be highlighted as red. If this logic is not correct, change the color index in the for loop.
You may try something like this...
Sub Is_Na()
Dim RngToCompare As Range, RngToComapreWith As Range, c As Range
Dim colLetterSource As String, colLetterTarget As String
Dim lr As Long
Application.ScreenUpdating = False
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
colLetterSource = Application.InputBox(prompt:="Please input the column letter of your source range. In this range you will find the cells wich are not in your range to compare", Type:=2)
If colLetterSource = "" Then
MsgBox "You didn't input the Column Letter for the Source Range.", vbExclamation
Exit Sub
Else
If ValidColumnLetter(colLetterSource) Then
lr = Cells(Rows.Count, colLetterSource).End(xlUp).Row
Set RngToCompare = Range(Cells(2, colLetterSource), Cells(lr, colLetterSource))
If MsgBox("The Source Range involved is " & RngToCompare.Address(0, 0) & vbNewLine & " Is this correct?", vbQuestion + vbYesNo, "Range To Compare!") = vbNo Then
MsgBox "You cancelled the action.", vbExclamation
Exit Sub
End If
Else
MsgBox "You entered an invalid column letter.", vbCritical
Exit Sub
End If
End If
colLetterTarget = Application.InputBox(prompt:="Please input the column letter of the Range to compare with.", Type:=2)
If colLetterTarget = "" Then
MsgBox "You didn't input the Column Letter for the Range to compare with.", vbExclamation
Exit Sub
Else
If ValidColumnLetter(colLetterTarget) Then
lr = Cells(Rows.Count, colLetterTarget).End(xlUp).Row
Set RngToComapreWith = Range(Cells(2, colLetterTarget), Cells(lr, colLetterTarget))
If MsgBox("The Source Range involved is " & RngToComapreWith.Address(0, 0) & vbNewLine & " Is this correct?", vbQuestion + vbYesNo, "Range To Compare!") = vbNo Then
MsgBox "You cancelled the action.", vbExclamation
Exit Sub
End If
Else
MsgBox "You entered an invalid column letter.", vbCritical
Exit Sub
End If
End If
For Each c In RngToCompare
If IsNumeric(Application.Match(c, RngToComapreWith, 0)) Then
c.EntireRow.Interior.ColorIndex = 3
Else
c.EntireRow.Interior.ColorIndex = 4
End If
Next c
With Range(Cells(1, colLetterSource), Cells(lr, colLetterSource))
.AutoFilter field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
If .SpecialCells(xlCellTypeVisible).Cells.Count <= 1 Then
.AutoFilter field:=1
End If
End With
Application.ScreenUpdating = True
End Sub
Function ValidColumnLetter(colLetter As String) As Boolean
Dim rng As Range
On Error Resume Next
Set rng = Range(colLetter & 1)
On Error GoTo 0
If Not rng Is Nothing Then ValidColumnLetter = True
End Function
Great! Instead of using isna and isin in different procedures, we can have just one procedure with your proposal! Do you think it is possible to apply an order by colored filter to have green at the top and red at the button?
Like this?
Sub Is_Na()
Dim RngToCompare As Range, RngToComapreWith As Range, c As Range
Dim colLetterSource As String, colLetterTarget As String
Dim lr As Long
Application.ScreenUpdating = False
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
colLetterSource = Application.InputBox(prompt:="Please input the column letter of your source range. In this range you will find the cells wich are not in your range to compare", Type:=2)
If colLetterSource = "" Then
MsgBox "You didn't input the Column Letter for the Source Range.", vbExclamation
Exit Sub
Else
If ValidColumnLetter(colLetterSource) Then
lr = Cells(Rows.Count, colLetterSource).End(xlUp).Row
Set RngToCompare = Range(Cells(2, colLetterSource), Cells(lr, colLetterSource))
If MsgBox("The Source Range involved is " & RngToCompare.Address(0, 0) & vbNewLine & " Is this correct?", vbQuestion + vbYesNo, "Range To Compare!") = vbNo Then
MsgBox "You cancelled the action.", vbExclamation
Exit Sub
End If
Else
MsgBox "You entered an invalid column letter.", vbCritical
Exit Sub
End If
End If
colLetterTarget = Application.InputBox(prompt:="Please input the column letter of the Range to compare with.", Type:=2)
If colLetterTarget = "" Then
MsgBox "You didn't input the Column Letter for the Range to compare with.", vbExclamation
Exit Sub
Else
If ValidColumnLetter(colLetterTarget) Then
lr = Cells(Rows.Count, colLetterTarget).End(xlUp).Row
Set RngToComapreWith = Range(Cells(2, colLetterTarget), Cells(lr, colLetterTarget))
If MsgBox("The Source Range involved is " & RngToComapreWith.Address(0, 0) & vbNewLine & " Is this correct?", vbQuestion + vbYesNo, "Range To Compare!") = vbNo Then
MsgBox "You cancelled the action.", vbExclamation
Exit Sub
End If
Else
MsgBox "You entered an invalid column letter.", vbCritical
Exit Sub
End If
End If
For Each c In RngToCompare
If IsNumeric(Application.Match(c, RngToComapreWith, 0)) Then
c.EntireRow.Interior.ColorIndex = 3
Else
c.EntireRow.Interior.ColorIndex = 4
End If
Next c
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add(RngToCompare.Cells(1), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 255, 0)
ActiveSheet.Sort.SortFields.Add(RngToCompare.Cells(1), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0 _
, 0)
With ActiveSheet.Sort
.SetRange Range("A1").CurrentRegion
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub
Function ValidColumnLetter(colLetter As String) As Boolean
Dim rng As Range
On Error Resume Next
Set rng = Range(colLetter & 1)
On Error GoTo 0
If Not rng Is Nothing Then ValidColumnLetter = True
End Function
Thank you very much for your this proposal.
I am having a problem when source range an comparison range is not in the same sheet.
Is it possible to set up source and comparison range in such a way to select them in different sheets?
I attached dummy file.
Thank you in advance for your help.
Is_In_Is_Na.xlsm
I am having a problem when source range an comparison range is not in the same sheet.
Is it possible to set up source and comparison range in such a way to select them in different sheets?
I attached dummy file.
Thank you in advance for your help.
Is_In_Is_Na.xlsm
Happy New Year! :)
Please find the attached and click the button on Source or TEST Sheet to invoke a self explanatory UserForm and select either Source or TEST Sheet as the source sheet and hit the button Compare.
Is_In_Is_Na.xlsm
Please find the attached and click the button on Source or TEST Sheet to invoke a self explanatory UserForm and select either Source or TEST Sheet as the source sheet and hit the button Compare.
Is_In_Is_Na.xlsm
Happy New year! :)
Unable to test it right now. I will keep you posted!
Thank you again for your help.
Unable to test it right now. I will keep you posted!
Thank you again for your help.
This proposal is great however I would like not to use the user form and directly calling the procedure through a ui custom button which I use in my personal.xlsb. Do you think it is possible just to review the inputbox for source and comparison range in order to be able to move from one sheet to another to select the sheet. If this is not possible I can report the sheet name in the input box of each of it?
Thank you in advance for your help.
Thank you in advance for your help.
Why are you asking user to type column letter and the sheet name as that will be more work required from the user and would be prone to errors? Why not just asking them to select any cell in the source range and the target range? That way, they can activate the desired sheet and select any cell in the source and target ranges and the source sheet and the target sheet will be correctly picked by the code?
You are right, I think the best will be to ask column letter or select the initial range for both source and comparison range and display msgbox after the selection to see initial to end range.
I remember having the same problem with a previous questions related to sheet comparison:
https://www.experts-exchan
And it was solved with the following code
https://www.experts-exchange.com/viewCodeSnippet.jsp?codeSnippetId=20-42757251-1
Thank you in advance for your help.
I remember having the same problem with a previous questions related to sheet comparison:
https://www.experts-exchan
And it was solved with the following code
https://www.experts-exchange.com/viewCodeSnippet.jsp?codeSnippetId=20-42757251-1
Thank you in advance for your help.
Tested and it works, the full requirement was covered! Just one question I took a look related to the following variables:
wsTarget.Cells(2, Col) and wsSource.Cells(2, Col)
This means that inital range will start at row 2 no matter if there is blank space in row 2, is not a way to set up those variables to take into account the first no empty cell which will be the header + 1?
Thank you again for your help.
wsTarget.Cells(2, Col) and wsSource.Cells(2, Col)
This means that inital range will start at row 2 no matter if there is blank space in row 2, is not a way to set up those variables to take into account the first no empty cell which will be the header + 1?
Thank you again for your help.
That assumes the row1 is the header row and the actual data starts from row2 for both the source and the target ranges.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial