HLookup VBA Question

[Sjoewie]

Hi,

I am struggling with getting code for the following. In short I would like to use HLOOKUP to find a value in Sheet 2 and then copy and paste all the data below into Sheet 1.

So simple example

Sheet 2 (Upper left cell is A1):

Jan	Feb	Mar	Apr
44	6	7	10
21	17	35	1
3	19	31	17
45	1	12
	15	11
		46

<tbody>
</tbody>

Sheet 1 (assume upper left is A1 and B1 is the input variable.

Month	Feb

<tbody>
</tbody>

The issue I am having is that I struggle how to cope with the different amount of rows that I need to copy.

Any help would be greatly appreciated.

Thanks!

 

[bbott]

Welcome to the board.

This solution doesn't use HLOOKUP, but it should work for you. It pastes the info below B1 in sheet 1.

Sub CpyMnth()

    Dim x As Long
    Application.ScreenUpdating = False
    
    Range("B2:B1000").ClearContents
    x = WorksheetFunction.Match(Sheets(1).Range("B1"), Sheets(2).Range("A1:L1"), 0)
    
    Sheets(2).Select
    Range(Cells(2, x), Cells(Sheets(2).UsedRange.Rows.Count, x)).Copy Sheets(1).Range("B2")
    Sheets(1).Select
    
    Application.ScreenUpdating = True
    
End Sub

 

[Sjoewie]

Great it worked!! If I am correct u used Match instead of HLookup right?

 

[bbott]

Great it worked!! If I am correct u used Match instead of HLookup right?

That is correct.