[發問] VBA 搜尋資料列對應的最大值或最小值

帖子: 16
主題: 4
精華: 0
積分: 20
點名: 0
作業系統: Win7
軟體版本: office2007
閱讀權限: 10
註冊時間: 2014-11-27
最後登錄: 2015-1-3

12^#

發表於 2014-12-16 23:35 | 只看該作者

回復 junkwei
"=EX" 如沒這公式傳回錯誤值 "#NAME?"

請詳看VBA 的說明
GBKEE 發表於 2014-12-15 07:10

感謝GBKEE 版主的說明
原來只要填入錯誤的公式讓他回傳錯誤值就可以了
如此可以在特殊的範圍裡作業
感覺這真的很實用
謝謝指導~~!

GBKEE

超級版主 Rank: 8 Rank: 8

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

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

11^#

發表於 2014-12-15 07:10 | 只看該作者

回復 10# junkwei
"=EX" 如沒這公式傳回錯誤值 "#NAME?"

請詳看VBA 的說明

SpecialCells 方法
expression.SpecialCells(Type, Value)
expression 必選。該運算式會傳回 [套用於] 清單中的其中一個物件。
Type 必選的 XlCellType。要包含的儲存格。
XlCellType 可以是這些 XlCellType 常數之一。
xlCellTypeAllFormatConditions。任何格式的儲存格
xlCellTypeAllValidation。具有驗證準則的儲存格
xlCellTypeBlanks。空儲存格
xlCellTypeComments。包含註解的儲存格
xlCellTypeConstants。包含常數的儲存格
xlCellTypeFormulas。包含公式的儲存格
xlCellTypeLastCell。已用範圍的最後一個儲存格
xlCellTypeSameFormatConditions。有相同格式的儲存格
xlCellTypeSameValidation。有相同驗證準則的儲存格
xlCellTypeVisible。所有可見儲存格
Value 選擇性的 Variant。如果 Type 為 xlCellTypeConstants 或 xlCellTypeFormulas 之一，此引數可用於確定結果中應包含哪幾類儲存格。將某幾個值相加可使此方法傳回多種型態的儲存格。預設情況下將指定所有常數或公式，對其型態則不加類型。可為下列 XlSpecialCellsValue 常數之一：
XlSpecialCellsValue 可以是這些 XlSpecialCellsValue 常數之一。
xlErrors
xlLogical
xlNumbers
xlTextValues

複製代碼

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

帖子: 16
主題: 4
精華: 0
積分: 20
點名: 0
作業系統: Win7
軟體版本: office2007
閱讀權限: 10
註冊時間: 2014-11-27
最後登錄: 2015-1-3

10^#

發表於 2014-12-14 20:51 | 只看該作者

回復 junkwei

Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
"a:a" -> A欄整欄
AdvancedF ...
GBKEE 發表於 2014-12-12 07:13

原來如此~~真的謝謝GBKEE版主的指導!!

關於您的code小弟尚有幾個問題想向您再確認一下:
1. 請問一開始的 Set Rng = Range("iv1") 是不是隨便指定一個range就可以了呢??
因為不是後面就被AdvancedFilter的結果取代掉了??

2.
         .Replace Rng.Cells(i), "=EX", xlWhole '各班級換置為錯誤值
         With .SpecialCells(xlCellTypeFormulas, xlErrors)
            .Value = Rng.Cells(i)             '錯誤值換置回為各班級
         End With
   這幾行的目的似乎是將各班級的年紀值挑出來後, 再對其取MAX跟MIN
   有爬到版主之前的文章對SpecialCells也都有類似的用法..
   但看了半天跟上網搜尋還是無法完全掌握正確的用法...
   想請問的第二個參數的 xlErrors是如何判定為錯誤的呢?  為什麼取代為"=EX"  就是錯誤值呢?
   xlCellTypeFormulas不是表示"使用公式的儲存格"嗎??

以上兩個問題再麻煩您撥空解惑了!!  謝謝~~~!

GBKEE

超級版主 Rank: 8 Rank: 8

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

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

9^#

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

回復 8# junkwei

Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
"a:a" -> A欄整欄
AdvancedFilter 方法 expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)
CriteriaRange 沒有指定範圍,為不設下篩選條件,
Ar([相對為工作表的欄數], [相對為工作表的列數]) ,與工作表的Cells(列,欄)顛倒
ReDim Preserve Ar(1 To 3, 1 To i)
選擇性引數。當改變原有陣列最後一維的大小時，仍然保有原來的資料的關鍵字。

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

帖子: 16
主題: 4
精華: 0
積分: 20
點名: 0
作業系統: Win7
軟體版本: office2007
閱讀權限: 10
註冊時間: 2014-11-27
最後登錄: 2015-1-3

8^#

發表於 2014-12-11 21:15 | 只看該作者

回復 junkwei
GBKEE 發表於 2014-12-4 07:01

真的很感謝GBKEE前輩的指導!!
這個code正是我要的功能!
vba果然功能強大...也看到好多沒看過的語法...0rz
但小弟研究前輩的code還是有些地方無法了解...上網查詢似乎也還是不太懂...
希望前輩可以不吝指導..
1. Set Rng = Range("iv1")
請問這樣定義的range是怎麼樣的range呢?? iv1 不是表示舊版excel的第一欄的最後一格嗎?

2. Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
請問a:a是怎麼樣的範圍呢??這行code的邏輯我想半天還是想不通...

3.請問為什麼code中要把Ar()矩陣先以轉置後的位置輸入篩選，再以轉置後的位置輸出呢???

不好意思如果有敘述不好的地方請多見諒
再麻煩前輩指導了~謝謝~!!

GBKEE

超級版主 Rank: 8 Rank: 8

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

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

7^#

發表於 2014-12-4 07:01 | 只看該作者

回復 5# junkwei

Option Explicit
Sub Ex()
Dim Rng As Range, i As Integer, Ar()
Set Rng = Range("iv1")
'進階篩選出"班級"欄不重複的值
Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
ReDim Ar(1 To 3, 1 To 1) '陣列重置
Ar(1, 1) = "班級"
Ar(2, 1) = "最大年紀"
Ar(3, 1) = "最小年紀"
i = 2
Do While Rng.Cells(i) <> "" '"班級"欄不重複的值(各班級)
With Range("a:a")
.Replace Rng.Cells(i), "=EX", xlWhole '各班級換置為錯誤值
With .SpecialCells(xlCellTypeFormulas, xlErrors)
ReDim Preserve Ar(1 To 3, 1 To i)
Ar(1, i) = Rng.Cells(i)
Ar(2, i) = Application.Max(.Cells.Offset(, 2))
Ar(3, i) = Application.Min(.Cells.Offset(, 2))
.Value = Rng.Cells(i) '錯誤值換置回為各班級
End With
i = i + 1
End With
Loop
Rng.EntireColumn.Clear '清除:進階篩選出"班級"欄不重複的值
Range("g1").Resize(UBound(Ar, 2), UBound(Ar, 1)) = Application.WorksheetFunction.Transpose(Ar)
'UBound(Ar, 2)陣列第2維的元素上限值
'UBound(Ar, 1)陣列第1維的元素上限值
End Sub

複製代碼

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

帖子: 16
主題: 4
精華: 0
積分: 20
點名: 0
作業系統: Win7
軟體版本: office2007
閱讀權限: 10
註冊時間: 2014-11-27
最後登錄: 2015-1-3

6^#

發表於 2014-12-3 23:11 | 只看該作者

回復 3# joey0415

謝謝前輩的指導~
由於小弟VBA資歷尚淺
是否可以請前輩撥空稍加說明用法及語法呢
以便小弟方便應用及查詢相關的資訊呢??

其中有幾行小弟不太了解的地方如下:
1.
Set rs = cn.Execute("select 班級,max(年紀) as 最大年紀,min(年級) as 最小年紀 from 某某表 group by 班級")
其中的『最大年紀』及『最小年紀』是要自己先輸入的嗎?
P.S.小弟的需求是希望G2:I4的儲存格都是VBA自動分析產生的

2.
For Each tt In rs.Fields '顯示表單名稱
請問tt所代表的是工作表的名稱嗎?

不好意思~~~
再麻煩前輩撥空指導了
感激不盡~~!!