返回列表 上一主題 發帖

[發問] Excel VBA以定義過的WorkSheet搭配Excel一般函數

peohan

帖子
1 
主題
1 
精華
0 
積分
2 
點名
0  
作業系統
Windows 
軟體版本
7 
閱讀權限
10 
註冊時間
2015-7-26 
最後登錄
2015-7-26 
1# 跳轉到 » 正序看帖
打印
tT
發表於 2015-7-26 15:18 | 只看該作者

[發問] Excel VBA以定義過的WorkSheet搭配Excel一般函數

VBA, Workbook, Worksheet, Excel
小弟正開始Excel VBA的寫作過程

試著將sht1宣告成Worksheet (Dim sht1 as Worksheet)
再以變數Sheet1去與Excel函數做搭配

目前已知可以利用Range ("A1").Value 及 Range ("A1").Formula去輸入公式
但似乎都沒辦法以Sheet1去直接做運用

例如：sht1.Name = "工作表1"
  當要表示 =countif(工作表1!C:C,A1)時
  是否能夠轉換成 將工作表1直接用sh1去做代替，簡化VBA的訊息

  現在我的進度只會把用字串的方式去表示 = "=countif(" & sh1.Name & "!C:C,A1)"
  期望可以簡化成 = "=countif(sh1!C:C,A1)" 這類比較易瞭的公式

另外當有跨活頁簿時，是否也可以藉由宣告Workbook
  Dim wb1 as Workbook
  去將wb1.sh1去運用在Excel公式中！？
收藏 分享

GBKEE

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

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
3#
發表於 2015-7-27 08:40 | 只看該作者
回復 2# peo.han
試試看
是這樣嗎?
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Sh1 As Worksheet, Rd As String, x1Row As Integer
  4.     Set Sh1 = Sheet1
  5.     With Sheet2
  6.         Rd = .Range("C:C").Address(, , , 1, 1)
  7.     End With
  8.     With Sh1
  9.         x1Row = .Range("D65536").End(xlUp).Row
  10.         With .Range("e11:e" & x1Row)
  11.             .Cells = "=IF(D11="""","""",COUNTIF(" & Rd & ",D11))"
  12.             '.Value = .Value '公式轉為數值
  13.             .Select
  14.         End With
  15.     End With
  16. End Sub
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

peo.han

帖子
1 
主題
0 
精華
0 
積分
1 
點名
0  
作業系統
Windows 7 
軟體版本
Microsoft Office 2010 
閱讀權限
10 
註冊時間
2012-9-20 
最後登錄
2021-1-17 
2#
發表於 2015-7-26 16:31 | 只看該作者
目前用到兩個寫法
第一個是用VBA的Application.Function，但效率較差
Dim x1Row As Integer
For x1Row = 11 To sh1.Range("D65536").End(xlUp).Row
    Cells(x1Row, 5).Value = Application.CountIf(sh1.Range("C:C"), Cells(x1Row, 4))
Next x1Row

第二個則是用字串的方式，寫法較複雜，但效率高了10倍以上
需留下 '[Workbook]Worksheet!'的 ' [ ] ! '的符號才不會出錯
Cells( 11, 5).Formula = "=IF(D11="""","""",COUNTIF('[" & wb1.Name & "]" & sh1.Name & "'!$CC,D11))"
Range("E11").AutoFill Destination:=Range("E11:E" & [E65536].End(1).Row)

TOP

        靜思自在 : 【行善要及時】行善要及時，功德要持續。如燒開水一般，未燒開之前千萬不要停熄火候，否則重來就太費事了。
返回列表 上一主題