tatsu99 score 2652
2019/11/12 23:55 投稿
| VLOOKUPにこだわらず、Sheet2のA列を検索してはいかがでしょうか。 |
| Private Sub Test()内の |
| MainKey = ws_MainSheet.Range(Main_col & cnt).Value + "Total" |
| を |
| MainKey = ws_MainSheet.Range(Main_col & cnt).Value |
| に変えます。 |
| ret = WorksheetFunction.VLookup(MainKey, subTbl, 2, False) |
| を |
| ret = get_week(ws_SubSheet, strSub_Lastcol, MainKey) |
| に変えます。 |
| そして、以下の関数を追加します。 |
| ```VBA |
| Private Function get_week(ws As Worksheet, lastrow As String, key As String) As String |
| Dim wrow As Long |
| Dim result As Variant |
| For wrow = 2 To CLng(lastrow) |
| If InStr(ws.Cells(wrow, "A").Value, key) > 0 Then |
| result = Cells(wrow, "A").Value Like key & "*Total" |
| If result = True Then |
| get_week = ws.Cells(wrow, "B").Value |
| Exit Function |
| End If |
| Next |
| get_week = "該当なし" |
| End Function |
| ``` |
