'Excel VBA hlookup return 400

I have a sheet with score of the current round and a sheet with total scores. I need to save the final score of the current round to the sheet with all scores.

The first Hlookup works fine, but at the second Hlookup VBA crashes (400).

Dim TotSht As Worksheet
Dim NowSht As Worksheet
Set TotSht = Worksheets("Total")
Set NowSht = Worksheets("Current")

TotSht.Range("D1").EntireColumn.Insert
TotSht.Range("D1").Value = NowSht.Range("U1") 'Round number

TotSht.Range("D2").Value = WorksheetFunction.HLookup(TotSht.Range("A2"), NowSht.Range("C2:H3"), 2)
TotSht.Range("D3").Value = WorksheetFunction.HLookup(TotSht.Range("A3"), NowSht.Range("C2:H3"), 2)
TotSht.Range("D4").Value = WorksheetFunction.HLookup(TotSht.Range("A4"), NowSht.Range("C2:H3"), 2)
TotSht.Range("D5").Value = WorksheetFunction.HLookup(TotSht.Range("A5"), NowSht.Range("C2:H3"), 2)
TotSht.Range("D6").Value = WorksheetFunction.HLookup(TotSht.Range("A6"), NowSht.Range("C2:H3"), 2)
TotSht.Range("D7").Value = WorksheetFunction.HLookup(TotSht.Range("A7"), NowSht.Range("C2:H3"), 2)

What am I doing wrong?

Edit: If I use "on error resume next" Only the 1st and 3rd gets loaded. In my dataset, the first 3 Hlookups should return some value, and the 4th has no match.

excel vba vlookup

Solution 1:^[1]

The error is resolved. What I did was close excel and open it again.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Sathans

'Excel VBA hlookup return 400

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]