Sub Improved_Index_Match()
Dim SrcWs As Worksheet, TrgtWs As Worksheet
Dim IndexRng As Range, MatchRng As Range, MatchValue As Range, MatchValRng As Range
Dim iRow As Long, mRow As Long, iiRow As Long, mmRow As Long, iCol As Long, mCol As Long, mvRow As Long, mvCol As Long, mvvRow As Long

On Error Resume Next
Set IndexRng = Application.InputBox(Prompt:="Please select INDEX RANGE, range that you want to lookup in your Source range: ", Title:="Index Range Select", Type:=8)
On Error GoTo 0
If IndexRng Is Nothing Then
    MsgBox "You didn't select any cell in the Index Range.", vbExclamation
    Exit Sub
End If
    
Set SrcWs = IndexRng.Parent
iCol = IndexRng.Column
If SrcWs.Cells(1, iCol).Value <> "" Then
    iRow = 1
Else
    iRow = SrcWs.Cells(1, iCol).End(xlDown).Row
End If
iiRow = SrcWs.Cells(Rows.Count, iCol).End(xlUp).Row
Set IndexRng = SrcWs.Range(SrcWs.Cells(iRow + 1, iCol), SrcWs.Cells(iiRow, iCol))

On Error Resume Next
Set MatchRng = Application.InputBox(Prompt:="Please Select MATCH RANGE which is located in the same sheet of your Source range", Title:="Match Range Select", Type:=8)
On Error GoTo 0
If MatchRng Is Nothing Then
    MsgBox "You didn't select any cell in the Match Range to compare with.", 

Select all Open in new window

Sub Index_Match()

Dim lCol As Long
Dim matchvalue As Range
Dim n1, n2, n3 As Long

On Error GoTo exit_sub

MsgBox ("WARNING: In order to properly run this procedure Check that:" & vbNewLine & _
"1-Select the initial range that is going to receive the index range" & vbNewLine & _
"2-Three ranges should have the same initial range")


Set target = Application.InputBox(prompt:="Select index range, range that you want to lookup in your output range: ", Title:="Range Select", Type:=8)
n1 = target.Parent.UsedRange.Row + target.Parent.UsedRange.Rows.Count - 1
MsgBox (n1)
Set target = Range(target.Cells(1, 1), target.EntireColumn.Cells(n1, 1))

Set matchvalue = Application.InputBox(prompt:="Select match range which is located in the same sheet of your output range", Title:="Range Select", Type:=8)
n2 = matchvalue.Parent.UsedRange.Row + matchvalue.Parent.UsedRange.Rows.Count - 1
MsgBox (n2)
Set matchvalue = Range(matchvalue.Cells(1, 1), matchvalue.EntireColumn.Cells(n2, 1))

Set comparedvalue = Application.InputBox(prompt:="Select compared range, this range is located in the same sheet of your index range", Title:="Range Select", Type:=8)
n3 = comparedvalue.Parent.UsedRange.Row + comparedvalue.Parent.Rows.Count - 1
MsgBox (n3)
Set comparedvalue = Range(comparedvalue.Cells(1, 1), comparedvalue.EntireColumn.Cells(n3, 1))
lCol = ActiveCell.Column - 

Select all Open in new window

• Exchange Server - we are hybrid exchange 2010 with ALL mailboxes in 365.  Do we need to licence exchange 2019 or 2016 or do MS offer a free key still?
• Exchange cals - as above - i'm assuming the exchange online plan 1 covers the cals for users/devices?
• Windows server cals - we have bought 60 windows 10 licences - one supplier says we dont need cals, the other two say we do..?
• SQL server - we will be using two standalone SQL servers, of which one is production servicing live clients and data, the other SQ lserver is online but for DR/BC usage only.  Do we need two server licences or one?

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

Select all Open in new window