値入力のある最終行が取得できない

いつもこれで最終行を取得しているのですが、なぜか変数に１が入ってしまいます。
Functionプロシージャに移動してるから今まで使ってない変数を定義しても値は１のままです。
なぜだかわからず・・・

VBA
1Function Total(ByVal kouza As String)
2
3    Dim outputfile As String
4    
5    Dim sSQL As String
6    Dim rst  As ADODB.Recordset
7    Dim wbk As Workbook
8    Dim wks As Worksheet
9
10    Dim x As Long
11    Dim r As Long
12    Dim cnt As String
13    If access_mySQL_Server = False Then
14        GoTo ERR_RTN
15    End If
16    
17    Set wbk = ThisWorkbook
18    Set wks = wbk.ActiveSheet
19    With cmd
20'            wks.Cells(6, 2).Select
21            x = wks.Cells(Rows.Count, 2).End(xlUp).Row　　←最終行の取得
22            For r = x To 7 Step -1
23                If wks.Cells(r, 2) = "合計" Then
24                    r = wks.Cells(r - 1, 2).Row
25                    Exit For
26                ElseIf r = 7 Then
27                    r = 7
28                End If
29            Next
30            If wks.Cells(r, 6) = "<1>" Then cnt = 1
31            If wks.Cells(r, 6) = "<2>" Then cnt = 2
32            If wks.Cells(r, 6) = "<3>" Then cnt = 3
33            If wks.Cells(r, 6) = "<4>" Then cnt = 4
34            
35                    
36            '総合計と備考欄追加
37            sSQL = "SELECT count(ta_date4),count(ta_date2),count(ta_kzcode)"
38            sSQL = sSQL & " from ta"
39            sSQL = sSQL & " where ta_dtno = 5"
40            sSQL = sSQL & " and ta_kzcode = " & kouza
41            sSQL = sSQL & " and ta_count = " & cnt
42            sSQL = sSQL & " group by ta_kzcode,ta_count"
43
44            .ActiveConnection = cnn
45            .CommandText = sSQL
46            Set rst = .Execute
47            sSQL = ""
48
49            wks.Range("C" & x + 1) = "合計"
50            wks.Range("D" & x + 1) = "（添削依頼済数/提出者数/申込者数）" & rst(0) & "/" & rst(1) & "/" & rst(2)
51            rst.Close
52            Set rst = Nothing
53            wks.Range("D" & x + 1 & ":F" & x + 1).Merge
54            wks.Range("B" & x + 1 & ":C" & x + 1).Merge
55            wks.Range("G" & x + 1).Formula = "=SUBTOTAL(9,G" & r & ":G" & x & ")"
56            wks.Range("B" & x + 1 & ",G" & x + 1).Interior.Color = RGB(192, 192, 192)
57            wks.Range("B" & x + 2) = "総合計"
58            wks.Range("B" & x + 2 & ":F" & x + 2).Merge
59            wks.Range("G" & x + 2).Formula = "=SUBTOTAL(9,G7:G" & x + 1 & ")"
60            wks.Range("B" & x + 2 & ":G" & x + 2).Font.Bold = True
61            wks.Range("B" & r & ":G" & x + 2).Borders.LineStyle = xlContinuous
62            wks.Cells(x + 4, 2).Value = "備考欄"
63            wks.Cells(x + 5, "B").Resize(5, 6).Merge
64            wks.Cells(x + 5, 2).interor.Color = RGB(255, 255, 153)
65            wks.Cells(x + 5, 2).Borders.LineStyle = xlContinuous
66            wks.Cells(1, 1).Select
67            
68            
69    End With
70    
71
72
73    If close_mySQL_Server = False Then
74        GoTo ERR_RTN
75    End If
76    
77'
78'    Output_302 = outputfile
79'
80'Exit Function
81ERR_RTN:
82    If Len(sErr(0)) = 0 Then
83        sErr(0) = CStr(Err.Number)
84        sErr(1) = Err.Description
85        sErr(2) = "Output_302"
86        sErr(3) = sSQL
87    End If
88End Function

tatsu99

2019/11/28 09:28

取得しているのは、アクティブシートのB列最終行ですが、１．B列の最終行が求める結果ですか？２．アクティブシートが途中で切り替わっている可能性はないですか。シート名が判っているなら、 Set wks = ThisWorkbook.Worksheets("シート名") としてどうなるか確認してみてはいかがでしょうか。