請教由巨集錄制的公式如何轉換為VBA公式,並且儲存格可以是變數

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 257
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-27

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

2^#

發表於 2011-11-21 20:05 | 只看該作者

回復 1# luffyzoro

錄製巨集的公式是以R1C1參照形式
R後面的數值是表示列位，當R後面沒有數值表示與公式所在儲存格同列，數值以中括號標示表示相對參照於公式所在儲存格的列數。
C後面的數值是表示欄位，當C後面沒有數值表示與公式所在儲存格同欄，數值以中括號標示表示相對參照於公式所在儲存格的欄數。
而公式在VBA中是一個字串型態的資料
例如:R2C24這是指到X2儲存格
若是欄位要變動，先以變數指定欄位然後帶入字串
k=26
"R2C" & k
這樣得到字串就是R2C26

學海無涯_不恥下問

帖子: 23
主題: 2
精華: 0
積分: 25
點名: 0
作業系統: XP
軟體版本: EXCEL 2003
閱讀權限: 10
性別: 男
註冊時間: 2011-11-21
最後登錄: 2013-4-9

3^#

發表於 2011-11-22 02:10 | 只看該作者

本帖最後由 luffyzoro 於 2011-11-22 02:59 編輯

敬請指點.rar (34.38 KB) 回復 2# Hsieh

版大您好
感謝您熱心回覆
小弟因工作需要,因此嚐試寫VBA
寫了一堆自己看了都很複雜的程式碼,寫到卡住了
懇請版大開釋
該用什麼寫法才能完成小弟想要達成的完成檔
其中有附上小弟寫的
懇請版大指點

帖子: 23
主題: 2
精華: 0
積分: 25
點名: 0
作業系統: XP
軟體版本: EXCEL 2003
閱讀權限: 10
性別: 男
註冊時間: 2011-11-21
最後登錄: 2013-4-9

4^#

發表於 2011-11-22 18:06 | 只看該作者

回復 2# Hsieh

hubcn = 2
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-1]=[hub.xls]AA!"R"&hubcn&"C2",[hub.xls]AA!"R"&hubcn&"C24">0,[hub.xls]AA!"R"&hubcn&"C24"-SUM(R2C11:RC[5])>0),[hub.xls]AA!"R"&hubcn&"C9")"

請問版大若照您舉例是否是寫成如上?這次我要變動的是rows
但是卻出現語法錯誤?

請問哪裡需要修正?

感謝

Hsieh

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 257
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-27

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

5^#

發表於 2011-11-22 20:16 | 只看該作者

回復 4# luffyzoro

hubcn = 2
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-1]=[hub.xls]AA!R" & hubcn & "C2,[hub.xls]AA!R" & hubcn & "C24>0,[hub.xls]AA!R" & hubcn & "C24-SUM(R2C11:RC[5])>0),[hub.xls]AA!R" & hubcn & "C9)"

學海無涯_不恥下問

帖子: 23
主題: 2
精華: 0
積分: 25
點名: 0
作業系統: XP
軟體版本: EXCEL 2003
閱讀權限: 10
性別: 男
註冊時間: 2011-11-21
最後登錄: 2013-4-9

6^#

發表於 2011-11-23 17:39 | 只看該作者

本帖最後由 luffyzoro 於 2011-11-23 17:50 編輯

回復 5# Hsieh

再次感謝大大迅速的回覆,我又有新問題了

Application.Dialogs(xlDialogOpen).Show
  Dim hubopf As String
  hubopf = ActiveWorkbook.Name←我的用意是要取得以對話框凱啟檔案的名稱,是否這裡有問題?
  Worksheets("AC-8E").Cells.Select
  Worksheets("AC-8E").Range("A:BY").Sort Key1:=Worksheets("AC-8E").Range("D2"), _
Order1:=xlAscending, Key2:=Worksheets("AC-8E").Range("I2") _
, Order2:=xlAscending, Header:=xlYes
  ThisWorkbook.Activate
  Dim calopf As String
  calopf = ThisWorkbook.Name
  ActiveSheet.Cells.Select
  Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2"), _
Order2:=xlAscending, Key3:=Range("E2"), _
Order3:=xlAscending, Header:=xlYes
  Cells(ActiveSheet.Rows.Count, 6).End(xlUp).Select
  Selection.Offset(1, 0).Select
  Workbooks(hubopf).Worksheets("AC-8E").Activate←逐行執行到這裡出現錯誤"型態不符合"

請問是不是我在宣告hubopf這個變數時有問題?

感謝大大

Hsieh

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 257
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-27

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

7^#

發表於 2011-11-23 19:53 | 只看該作者

回復 6# luffyzoro

Dim hubopf As String
hubopf = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If hubopf = "False" Then MsgBox "請選取檔案": Exit Sub
Workbooks.Open hubopf

學海無涯_不恥下問

帖子: 23
主題: 2
精華: 0
積分: 25
點名: 0
作業系統: XP
軟體版本: EXCEL 2003
閱讀權限: 10
性別: 男
註冊時間: 2011-11-21
最後登錄: 2013-4-9

8^#

發表於 2011-11-24 10:02 | 只看該作者

回復 7# Hsieh

版大再次感謝您的熱心

抱歉,我沒有把問題說清楚
假設我的巨集是寫在A.xls , 用A.xls開啟B.xls , 照版大的方式我的hubopf會得到
"D:\C資料夾\F資料夾\B.xls"
實際上我想得到的字串只有 "B.xls" ,因為每次開的檔名可能不一樣
所以我想將hubopf = "B.xls"
如此後續的Workbooks("hubopf")應該就會照著開啟不同的檔案做變動

請問
1.我該如何取得hubopf = "B.xls"隨著開啟檔名不同也可能是"C.xls"
2.後續要套用Workbooks是要寫Workbooks(hubopf)還是要寫Workbooks("hubopf")?

感謝

GBKEE

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

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

9^#

發表於 2011-11-24 11:53 | 只看該作者

回復 8# luffyzoro

Option Explicit
Sub Ex()
Dim hubopf As String, AR
hubopf = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If hubopf = "False" Then MsgBox "請選取檔案": Exit Sub
AR = Split(hubopf, "\")
hubopf = AR(UBound(AR)) 'hubopf = "B.xls"隨著開啟檔名不同也可能是"C.xls"
Workbooks.Open hubopf 'Workbooks( hubopf )
End Sub

複製代碼

帖子: 23
主題: 2
精華: 0
積分: 25
點名: 0
作業系統: XP
軟體版本: EXCEL 2003
閱讀權限: 10
性別: 男
註冊時間: 2011-11-21
最後登錄: 2013-4-9

10^#

發表於 2011-11-24 15:26 | 只看該作者

回復 9# GBKEE

感謝GBKEE版大
Dim hubopf As String, AR
hubopf = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If hubopf = "False" Then MsgBox "請選取檔案": Exit Sub
AR = Split(hubopf, "\")
hubopf = AR(UBound(AR)) 'hubopf = "B.xls"隨著開啟檔名不同也可能是"C.xls"
Workbooks.Open hubopf 'Workbooks( hubopf )

如您的協助將上面的程式碼使用之後,逐行執行到
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-1]=[hubopf]AC8E!R" & hubcn & "C2,[hubopf]AC8E!R" & hubcn & "C72>0,[hubopf]AC8E!R" & hubcn & "C72-SUM(R" & mycarw & "C11:RC[5])>0),[hubopf]AC8E!R" & hubcn & "C9)"

這裡會跳出對話框要求我選擇開啟檔案,如果不選檔案,在儲存格就會判別FALSE,選了檔案才會出現我要的結果
我發現是hubopf沒有值,所以又要求重開檔案,可是這個hubopf是一開始就宣告的,到了公式時卻又需重開檔案才有值,
我的巨集是寫在A.xls的sheet2 , 在上述公式執行前有多次切換A.xls 和 B.xls , 這個公式是在A.xls的儲存格中,
請教大大,如何才能不要持續重開檔案?

感謝