Excel VBA hlookup return 400-CodePudding

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.

CodePudding user response：

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