iceandy6150

回復 40# GBKEE


    G大，有錯誤產生

01.Option Explicit

02.Private Sub CommandButton2_Click()

03.    Sheets("參照表").UsedRange.Columns(1).CreateNames True

04.    With Sheets("Sheet1").Range("G2:G150").Validation

05.        .Add Type:=xlValidateList, Formula1:="=" & Sheets("參照表").UsedRange.Cells(1)

06.    End With

07.End Sub


第05行，錯誤1004，應用程式或物件定義上的錯誤
不曉得為什麼會這樣
謝謝

GBKEE

回復 41# iceandy6150

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

複製代碼

iceandy6150

回復 42# GBKEE


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

GBKEE

回復 43# iceandy6150
OFFICE的版本不同

iceandy6150

回復 44# GBKEE

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

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

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

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

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

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

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

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

下載 (76.79 KB)

2014-2-10 22:41

GBKEE

回復 45# iceandy6150

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

複製代碼

1. Sub Ex()
   
2.     With ActiveSheet.Range("G:G")
   
3.        MsgBox .Cells(.Count).End(xlUp).Address
   
4.     End With
   
5.     With ActiveSheet
   
6.        MsgBox .Range("G" & .Rows.Count).End(xlUp).Address
   
7.     End With
   
8.     With ActiveSheet
   
9.        MsgBox .Cells(.Rows.Count, "G").End(xlUp).Address
   
10.     End With
    
11.     With ActiveSheet
    
12.        MsgBox .Cells(.Rows.Count, 7).End(xlUp).Address
    
13.     End With
    
14. End Sub

複製代碼

iceandy6150

回復 46# GBKEE

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

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

下載 (75.56 KB)
圖1

2014-2-11 23:38

還有更傻眼的如下圖

下載 (70.35 KB)
圖2

2014-2-11 23:38

把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這筆資料誤貼過去<結算>
而那種怪異的年月日格式，如何讓程式不會誤判呢?

謝謝

GBKEE

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

請將檔案的範例上傳看看

iceandy6150

回復 48# GBKEE

大大，感謝解說

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

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

下載 (218.76 KB)

2014-2-12 17:06

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

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

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

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

下載次數: 9

2014-2-12 17:11

GBKEE

回復 49# iceandy6150

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

複製代碼