將資料自動分類功能 - Excel程式區

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

42^#

發表於 2014-2-10 10:08 | 只看該作者

回復 41# iceandy6150

Private Sub CommandButton2_Click()
Sheets("參照表").UsedRange.Columns(1).CreateNames True
With Sheets("Sheet1").Range("G2:G150").Validation
.Delete '加上這行如還有錯誤,請上傳檔案
'.Delete 2003版可不用.
.Add Type:=xlValidateList, Formula1:="=" & Sheets("參照表").UsedRange.Cells(1)
End With
End Sub

複製代碼

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限, 一天1元的贊助,人人有能力.

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

43^#

發表於 2014-2-10 12:45 | 只看該作者

回復 42# GBKEE

G大可以了耶
這也太神奇了，為什麼會這樣呢? 可以指導說明一下嗎?謝謝

哈囉~大家好呀

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

44^#

發表於 2014-2-10 12:59 | 只看該作者

回復 43# iceandy6150
OFFICE的版本不同

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限, 一天1元的贊助,人人有能力.

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

45^#

發表於 2014-2-10 22:41 | 只看該作者

回復 44# GBKEE

我可不可以在請問一下 UsedRange的用法

我發現如果一個工作表的A1~A10有畫框線 (或稱為表格也行)

然後只有A1~A5填入資料，A6~A10是空格

這樣情況下使用with sheets("工作表")

.UsedRange.Rows.count會算到10，而不是5

若針對.UsedRange這個範圍去使用CountA會數出5個

但如果我不想計算框線，也不是要算這個範圍中有數值的是幾個
只想找到有填入數值的最後一個格子
而且中間可能會有空格，該怎麼辦呢?

如下圖，我想找G欄的最後一筆資料，目前是G55
可是每個月增加資料後，可能會變G58、G63等等的

哈囉~大家好呀

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

46^#

發表於 2014-2-11 09:42 | 只看該作者

回復 45# iceandy6150

End 屬性該物件代表包含來源範圍之區域結尾處的儲存格。等於按 END+向上鍵、END+向下鍵、END+向左鍵或 END+向右鍵。唯讀 Range 物件。
expression.End (Direction)
Direction 必選的 XlDirection 資料類型。要移往的方向。
XlDirection 可以是這些 XlDirection 常數之一。
xlDown
xlToRight
xlToLeft
xlUp

複製代碼

Sub Ex()
With ActiveSheet.Range("G:G")
MsgBox .Cells(.Count).End(xlUp).Address
End With
With ActiveSheet
MsgBox .Range("G" & .Rows.Count).End(xlUp).Address
End With
With ActiveSheet
MsgBox .Cells(.Rows.Count, "G").End(xlUp).Address
End With
With ActiveSheet
MsgBox .Cells(.Rows.Count, 7).End(xlUp).Address
End With
End Sub

複製代碼

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限, 一天1元的贊助,人人有能力.

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

47^#

發表於 2014-2-11 23:52 | 只看該作者

本帖最後由 iceandy6150 於 2014-2-11 23:54 編輯

回復 46# GBKEE

大大，您寫得程式正常運作無誤，感謝
只是我有點好奇 .Range("G" & .Rows.Count).End(xlUp).Address
我是改成.Range("G" & .Rows.Count).End(xlUp).Value
因為我需要那一格的值
好奇的地方是為什麼您用 xlup ?
我們不是要找某範圍最下面一格儲存格，應該是xldown?

另外想在請問一個頭痛的問題
如下圖
是一個類似會計計帳的EXCEL表格
很令人傻眼的是，月份中間居然會有空白處 (資料不連續)
然後日期也只有打月、日
真正會用到的數字是在紅色格線的上面
以人眼看，是還算清楚，但是要用程式抓資料，很頭大捏
因為不是每一排都有年月日可以參考

還有更傻眼的如下圖

把EXCEL當作簿子使用，連頁數都有，但是102年只有一格
下面的就通通代表102年發生的了
然後不是每個月都有資料，本圖只有1、6、12月才有資料
而且本圖是要抓F欄位，紅色格線上方828,170那個數據

所需功能概述：
假設有A,B,C,D,E五種會計科目，分別是五張工作表
另外每個月結算需要一個結算工作表
需要到A,B,C,D,E去抓資料，到<結算>工作表，再去加加減減

那A,B,C工作表可能是像圖1，每個月一定有資料，但會有空格
我只要找G欄最下方一個數值，貼過去<結算>，就完成了
(這邊也使用G大教學，已可執行)

但D,E工作表可能是像圖2
資料不是每個月都有
比如我要結103年02月的資料
ABC是沒問題
DE如果有103年02月的資料才抓到<結算>
如果如圖2，沒有資料則不抓

要是我使用.Range("F" & .Rows.Count).End(xlUp).Value
勢必會抓錯，把102年12月31日的828170這筆資料誤貼過去<結算>
而那種怪異的年月日格式，如何讓程式不會誤判呢?

謝謝

哈囉~大家好呀

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

48^#

發表於 2014-2-12 07:19 | 只看該作者

回復 47# iceandy6150
.Rows.Count : 傳回物件列的總數
.Range("G" & .Rows.Count) : 這儲存格是位於G欄最底部的列號
為什麼是 xlup ? (往上),不是應該是xldown(往下)
Range("G" & .Rows.Count).End(xldown).Value :還是最底部列的儲存格

請將檔案的範例上傳看看

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限, 一天1元的贊助,人人有能力.

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

49^#

發表於 2014-2-12 17:11 | 只看該作者

回復 48# GBKEE

大大，感謝解說

檔案我刪掉一些東西後，上傳
裡面最右邊是<損益表>
藍色範圍是要去找的相關類別工作表
我有設一個按鈕，寫了部分的程式
橘色是要填入資料的地方

其餘沒顏色的部分就暫不處理
事後我再修就好

我想是不是要設一個輸入區
詢問使用者想產生哪一個月的損益表
例如103年01月

那程式就去各類工作表，如果有103年01月的資料
就放到<損益表>相關欄位去
如果沒有，就不放

只是各類工作表真的長得很怪....

總分類帳-上傳用.rar (101.29 KB)

哈囉~大家好呀

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

50^#

發表於 2014-2-13 10:08 | 只看該作者

回復 49# iceandy6150

'費用項目中 "津貼",有空格,工作表名稱"津貼59-60"中沒空格
'所有費用項目需與工作表名稱(費用項目??_??)一致
'否則 Sh = Filter(Ar, Trim(Rng(1).Cells(i)), True) 會不正確'
Option Explicit
Sub Ex()
Dim xlMon As Integer, xlYear As String, E As Variant
Dim Rng(1 To 2) As Range, Rng_Ar(), Ar(), i As Integer, Sh As Variant
With Sheets("損益表")
xlYear = Mid(.[a3], InStrRev(.[a3], "至") + 1, InStrRev(.[a3], "年") - InStrRev(.[a3], "至"))
'xlYear : 損益表的年度
xlMon = Mid(.[a3], InStrRev(.[a3], "年") + 1, InStrRev(.[a3], "月") - InStrRev(.[a3], "年") - 1)
'xlMon : 損益表的月份
Set Rng(1) = .[A18:A30] '費用項目
ReDim Rng_Ar(1 To Rng(1).Count) '陣列:元素數 = 費用項目數
End With
ReDim Ar(1 To Sheets.Count) '陣列:元素數 = Sheets.Count
For i = 1 To Sheets.Count
Ar(i) = Sheets(i).Name '陣列:元素導入 Sheets.Name
Next
For i = 1 To Rng(1).Count
Sh = Filter(Ar, Trim(Rng(1).Cells(i)), True)
'Filter 函數傳回一個從零開始的陣列，該陣列包含基於指定篩選準則的一個字串陣列的子集。
For Each E In Sh
With Sheets(E) '有"費用項目"名稱的工作表
Set Rng(2) = .[A:B].Find(xlYear, lookat:=xlWhole, LookIn:=xlValues) '核對年度
If Not Rng(2) Is Nothing Then
Set Rng(2) = .[a:a].Find(xlMon, lookat:=xlWhole) '搜尋月份
If Not Rng(2) Is Nothing Then
Rng_Ar(i) = Rng_Ar(i) + Rng(2).Range("F1") 'Range("F1"):金額位置
End If
End If
End With
Next
Next
Rng(1).Offset(, 1) = Application.WorksheetFunction.Transpose(Rng_Ar)
'Transpose(轉置) : 一維陣列(橫式) 轉換為二維陣列(這裡變一列直式)
End Sub

複製代碼

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限, 一天1元的贊助,人人有能力.