Row index variable used with 1 in ListObject-CodePudding

I run the following:

For r = 2 To lastRowSan
    useSan.Activate
    searchCATID = Cells(r, LONameSan.ListColumns("CATID").Range.Column)
    useOfCat = Cells(r, LONameSan.ListColumns("Total 6 months").Range.Column)
    compList.Activate
    searchVal = WorksheetFunction.XLookup(searchCATID, compList.ListObjects("completeList").ListColumns("CATID").Range, compList.ListObjects("completeList").ListColumns("CATID").Range, "NOK")
    
    If searchVal <> "NOK" Then
        compList.Activate
        Set rowCatSearch = [completeList].Rows(WorksheetFunction.Match(searchCATID, [completeList[CATID]], 0))
        rowCatID = rowCatSearch.Row
        colNameUSe = "Number of uses - " & market
        compList.ListObjects("completeList").ListColumns(colNameUSe).DataBodyRange(rowCatID) = useOfCat
    End If
Next r

where useSan and compList are worksheets, LONameSan is a ListObject defined dynamically. When I run the script, the matching values are found at the correct row index (the rowCatID = rowCatSearch.row is correct (I check step by step in Degug mode). However, the line compList.ListObjects("completeList").ListColumns(colNameUSe).DataBodyRange(rowCatID) = useOfCat will put the useOfCat valmue in the next row, ie. in rowCatID 1^

I have a second similar loop after this one which gives the exact same result.

Is this normal and due to the fact that I put the value in the databodyrange and that in this case, the row 1 is the first row of the databodyrange and does not count the real row 1 which is the Header?

CodePudding user response：

Edit: My answer is mostly valid, upon further inspection, the reason that your Match is providing the Worksheet Row is not because of the Match, but because of the range provided by the Table:

Set rowCatSearch = [completeList].Rows(WorksheetFunction.Match(searchCATID, [completeList[CATID]], 0))

The Match is actually giving the proper Row number you want, however because you have the match enclosed with the Table.Rows, it changes the Row.

Instead if you use:

rowCatID = WorksheetFunction.Match(searchCATID, [completeList[CATID]], 0)

that should give out the proper Row # you are looking for. Notice that I jumped directly to rowCatID, becasue the Set rowCatSearch is no longer needed by only using the Match function with the Table Column in it.

Original Answer:

This is because you are trying to assign a value using the "worksheet row" returned by the MATCH function. However the DataBodyRange is only requesting the row number of the DATABODY, in this case, Row 1 in the DataBodyRange is the First Row of the Data and not the first row of the Worksheet.

Example: in a table that starts with Headers on A1 and data from A2 to A5, Worksheet Row 5 is actually Row 4 in the DataBodyRange, but your MATCH Function is giving you the worksheet row because that's where it found the value. As long as the Table starts at Row 1 you can do a -1 on rowCatID and it should work.