帖子: 8
主題: 3
精華: 0
積分: 61
點名: 0
作業系統: Microsoft Windows
軟體版本: office 2010
閱讀權限: 20
註冊時間: 2018-12-26
最後登錄: 2023-6-13

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2023-3-16 11:05 | 只看該作者

自動向下填滿會超出資料範圍

在處理學生報名資料的時候，學生人數是變數，有時10人，有時50多人。
因為學生資料龐大，所以設計公式找出學生性別，並設計自動填滿VBA。
但不知是什麼原因，向下填滿會超出範圍，出現好幾個#N/A。
想請問各位VBA大師能否解答疑惑，是什麼原因呢? 是程式編寫有問題還是格式有問題? 感謝解答~

樓主熱帖

minarabbit

中學生 Rank: 2

帖子: 8
主題: 3
精華: 0
積分: 61
點名: 0
作業系統: Microsoft Windows
軟體版本: office 2010
閱讀權限: 20
註冊時間: 2018-12-26
最後登錄: 2023-6-13

2^#

發表於 2023-3-16 11:20 | 只看該作者

附上Excel程式檔，感謝解惑。

課程表單回應程式.rar (105.11 KB)

程式檔

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

3^#

發表於 2023-3-16 12:33 | 只看該作者

回復 2# minarabbit

謝謝前輩發表此主題與範例
請前輩試試看
F2公式=IFERROR(VLOOKUP($A2,'C:\每學期更新\[常用資料(學生+導師).xlsx]111-2學生資料'!$A$1:$G$2100,5,FALSE),"")

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

4^#

發表於 2023-3-16 13:41 | 只看該作者

本帖最後由 Andy2483 於 2023-3-16 14:04 編輯

回復 2# minarabbit

謝謝前輩
後學建議VBA的解決方案如下,請參考

Option Explicit
Sub 刪除()
Sheets("表單回應輸入區").UsedRange.Offset(1, 0).EntireRow.Delete
'↑工作表中有使用儲存格範圍往下偏移1列的範圍所在的列整列刪除
End Sub
Sub 向下填滿()
Dim C&, A$
'↑宣告變數:C是長整數變數,A是字串變數
C = [表單回應輸入區!C65536].End(3).Row
'↑令C這長整數變數是工作表裡C欄最後一個有內容儲存格列號
If C < 2 Then Exit Sub
'↑如果C變數小於 2(代表C欄沒有填入學生資料)!就結束程式執行
A = "'C:\每學期更新\[常用資料(學生+導師).xlsx]111-2學生資料'"
'↑令A這字串變數是"~~~"字串
Range([表單回應輸入區!A2], Cells(C, "A")).Formula = "=C2&D2"
'↑令工作表裡[A2]到 A欄第C變數列儲存格的公式是 "=C2&D2"
Range([表單回應輸入區!F2], Cells(C, "F")).Formula = _
"=VLOOKUP($A2," & A & "!$A$1:$G$2100,5,FALSE)"
'↑令工作表裡[F2]到 F欄第C變數列儲存格的公式是 "~~~~~"
End Sub
===================================
按刪除鈕結果:

手動填入學生資料:

按向下填滿鈕執行結果:

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

minarabbit

中學生 Rank: 2

帖子: 8
主題: 3
精華: 0
積分: 61
點名: 0
作業系統: Microsoft Windows
軟體版本: office 2010
閱讀權限: 20
註冊時間: 2018-12-26
最後登錄: 2023-6-13

5^#

發表於 2023-3-16 19:51 | 只看該作者

回復 4# Andy2483

您太謙虛了，我只是小小後輩而已，VBA還在研究學習中。
您寫的程式不僅簡短且優化許多，讓資料可以對齊，真是太感謝您了!!

利用變數A設定資料位置，用Range設定起始儲存格，用Cells設定變數C和向下填滿欄位所在，
用Formula套入各種Excel函數程式，【" & A & "!】利用變數A精簡地縮短了Vlookup函數。
就算不小心刪除原本位在第二列的公式，VBA向下填滿已經有內建公式了，所以畫面也變得很乾淨。

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

6^#

發表於 2023-3-17 07:20 | 只看該作者

回復 5# minarabbit

謝謝前輩回復
滿招損,謙受益
請前輩常上論壇一起學習

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP