To Use Hlookup with formula and VBA code

Hlookup formula in Microsoft Excel is to find something in table or range by columns. In this example we refer to table monthly score in Sheet1.

Table in Sheet1: Base on monthly we need to find score in

• Range("B2:M3)
  
• Row index = 2

Formula = HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).

In Sheet2 we have another table without score, now we have to lookup this value from Sheet1 table by using formula.

Fill this formula into Range("C2") = HLOOKUP(B2,Sheet1!$B$2:$M$3,2,FALSE).

• B2 is lookup_value
• Sheet1!$B$2:$M$3 is table_array but $ sign to fix the table when drag down.
• 2 is row_index_num
• FALSE is range_lookup (FALSE - Exact Match, TRUE - Approximate match)

VBA code:

Option Explicit
Sub HLookUPExample()

    Dim i As Integer
    With ActiveSheet
        i = 2
        Do
            .Range("C" & i) = Application.HLookup(.Range("B" & i), ActiveWorkbook.Sheets("Sheet3").Range("$B$2:$M$3"), 2, False)
        i = i + 1
        Loop While .Range("A" & i) <> ""
    End With
    
End Sub

Note:

• Make sure Sheet2 is selected or activated.
• If your table is located in another workbook then change ActiveWorkbook to Workbooks("MyBookName.xls") but ensure this workbook is open.

Microsoft Reference - Hlookup function

Done.

To Use Vlookup with formula and VBA code

Vlookup formula in Microsoft Excel is to find something in table or range by rows. In this example we refer to table monthly score in Sheet1.

Table in Sheet1 and range = Range("B2:C13)  and base on month we need to find score in column index = 2. 

Formula = VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).

In Sheet2 we have another table without score, now we have to lookup this value from Sheet1 table by using formula.

Fill this formula into Range("C2") = VLOOKUP(B2,Sheet1!$B$2:$C$13,2,FALSE).

• B2 is lookup_value
• Sheet1!$B$2:$C$13 is table_array but $ sign to fix the table when drag down.
• 2 is column_index_num
• FALSE is range_lookup (FALSE - Exact Match, TRUE - Approximate match)

VBA code:

Option Explicit
Sub VLookUPExample()

    Dim i As Integer
    With ActiveSheet
        i = 2
        Do
            .Range("C" & i) = Application.VLookup(.Range("B" & i), ActiveWorkbook.Sheets("Sheet1").Range("$B$2:$C$13"), 2, False)
        i = i + 1
        Loop While .Range("A" & i) <> ""
    End With
    
End Sub

Note:

• Make sure Sheet2 is selected or activated.
• If your table is located in another workbook then change ActiveWorkbook to Workbooks("MyBookName.xls") but ensure this workbook is open.

Microsoft Reference - Vlookup function

Done.

To Go Specific Cells or Range

Below code to is To Go Specific Cells and can be use to scroll any direction.

Option Explicit
Sub ToGoSpecficCells()

    Application.Goto ActiveSheet.Range("A1"), True
    
End Sub

Note: Change cells location in bracket A1-> any cell and Go cells or range will be at top left corner of excel sheet.

To Scroll up entire rows and Scroll left entire columns

Below code to is To Scroll up entire rows and Scroll left entire columns.

Option Explicit
Sub ToScrollUPcompletely()

    With ActiveWindow
        .ScrollColumn = 1
        .ScrollRow = 1
    End With
    
End Sub

Note: Wherever your location in excel sheet it will return to top left.

To Sort Data Ascending Or Descending without Header

Below code to sort data with header ascending or descending. For example data with 3 columns.

Option Explicit
Sub ToSortWithOutHeader()

    Range("A2:C6").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
    Key2:=Range("B2"), Order2:=xlAscending, _
    Key3:=Range("C2"), Order3:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
    DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
    Range("A1").Select
    
End Sub

Note: Selection must include header and for Descending just change xlAscending to xlDescending.