123
返回列表 上一主題 發帖

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

luffyzoro

帖子
23 
主題
2 
精華
0 
積分
25 
點名
0  
作業系統
XP 
軟體版本
EXCEL 2003 
閱讀權限
10 
性別
男 
註冊時間
2011-11-21 
最後登錄
2013-4-9 
11# 跳轉到 »
發表於 2011-11-24 15:54 | 只看該作者
回復 9# GBKEE
  1. Sub Test()
  2.     Dim hubopf As String, AR
  3.     hubopf = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
  4.     If hubopf = "False" Then MsgBox "請選取檔案": Exit Sub
  5.     AR = Split(hubopf, "\")
  6.     hubopf = AR(UBound(AR))   'hubopf = "B.xls"隨著開啟檔名不同也可能是"C.xls"
  7.     Workbooks.Open hubopf     'Workbooks( hubopf )
  8.     Worksheets("AC8E").Cells.Select
  9.     Worksheets("AC8E").Range("A:BY").Sort Key1:=Worksheets("AC8E").Range("D2"), _
  10.     Order1:=xlAscending, Key2:=Worksheets("AC8E").Range("I2") _
  11.     , Order2:=xlAscending, Header:=xlYes
  12.     ThisWorkbook.Activate
  13.     Dim calopf As String
  14.     calopf = ThisWorkbook.Name
  15.     ActiveSheet.Cells.Select
  16.     Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("C2"), _
  17.     Order2:=xlAscending, Key3:=Range("A2"), _
  18.     Order3:=xlAscending, Header:=xlYes
  19.     Cells(ActiveSheet.Rows.Count, 6).End(xlUp).Select
  20.     Selection.Offset(1, 0).Select
  21.     Workbooks(hubopf).Worksheets("AC8E").Activate
  22.   Dim hubcn As Integer, hubrwcnt As Integer
  23.    hubrwcnt = ActiveSheet.UsedRange.Rows.Count
  24.   For hubcn = 2 To hubrwcnt - 1
  25.     Workbooks(hubopf).Activate
  26.     Worksheets("AC8E").Range("BT" & hubcn).Select
  27.    If Selection.Value > 0 Then
  28.       Workbooks(calopf).Worksheets("sheet2").Activate
  29.       Dim mycarw As Integer
  30.       mycarw = ActiveCell.Row
  31.       Do
  32.         ActiveCell.FormulaR1C1 = _
  33.         "=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)"
  34.       Selection.Value = Selection.Value
  35.         ActiveCell.Offset(1).Select
  36.       Loop Until ActiveCell.Offset(-1).Value = False
複製代碼
就是這樣子然後到公式時就一直要重開檔案

TOP

GBKEE

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

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
12#
發表於 2011-11-24 15:56 | 只看該作者
本帖最後由 GBKEE 於 2011-11-24 16:00 編輯

回復 10# luffyzoro
改成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)"

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)"
你知道 hubcn 是變數要如此的加入變數  & hubcn &
同理 hubopf 是變數 也要如此做的

TOP

luffyzoro

帖子
23 
主題
2 
精華
0 
積分
25 
點名
0  
作業系統
XP 
軟體版本
EXCEL 2003 
閱讀權限
10 
性別
男 
註冊時間
2011-11-21 
最後登錄
2013-4-9 
13#
發表於 2011-11-24 16:10 | 只看該作者
回復 12# GBKEE


    感謝GBKEE大大迅速回覆

我還沒發問前已有改過版大所教的方式,但還是執行錯誤,實在是沒輒了,只好厚臉皮的一直來這邊請教

:L

TOP

GBKEE

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

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
14#
發表於 2011-11-24 16:55 | 只看該作者
本帖最後由 GBKEE 於 2011-11-25 16:30 編輯

回復 13# luffyzoro
8樓: 假設我的巨集是寫在A.xls , 用A.xls開啟B.xls , 照版大的方式我的hubopf會得到"D:\C資料夾\F資料夾\B.xls"

hubopf ="B.xls"
[" & hubopf & "]如目前磁碟機的路徑不是 D:\C資料夾\F資料夾  Excel會找不到檔案的  
請你在適當的地方加入   ChDir  "D:\C資料夾\F資料夾"    移動目前磁碟機的路徑

TOP

luffyzoro

帖子
23 
主題
2 
精華
0 
積分
25 
點名
0  
作業系統
XP 
軟體版本
EXCEL 2003 
閱讀權限
10 
性別
男 
註冊時間
2011-11-21 
最後登錄
2013-4-9 
15#
發表於 2011-11-24 17:53 | 只看該作者
本帖最後由 luffyzoro 於 2011-11-24 17:58 編輯

回復 14# GBKEE
   For hubcn = 2 To hubrwcnt - 1
    Workbooks(hubopf).Activate
    Worksheets("AC8E").Range("BT" & hubcn).Select
   If Selection.Value > 0 Then
      Workbooks(calopf).Worksheets("sheet2").Activate
      Dim mycarw As Integer
      mycarw = ActiveCell.Row    Do
       ChDir "H:\My documents\專案\工作量專案"        
       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)"
        Selection.Value = Selection.Value
        ActiveCell.Offset(1).Select
      Loop Until ActiveCell.Offset(-1).Value = False

感謝版大
但是還是一樣出問題
錯誤訊息  執行階段錯誤'1004':應用程式或物件定義上的錯誤

不好意思啊,版大
藍色那一段我看儲存格都有在hubopf所代表的檔案移動
可是到了公式那一段就出錯了
還請您幫忙看一下是否還有哪邊有問題

TOP

GBKEE

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

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
16#
發表於 2011-11-24 20:24 | 只看該作者
回復 15# luffyzoro
9樓的錯誤   這裡會跳出對話框要求我選擇開啟檔案,  ChDir "H:\My documents\專案\工作量專案"    後9樓的錯誤消失了

15樓錯誤訊息  執行階段錯誤'1004':應用程式或物件定義上的錯誤
這程式碼 我代入變數 hubopf   , hubcn,  沒有錯誤,  請檢查每一  Workbooks (hubopf) 中是否 有工作表名稱"AC8E"
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)"

TOP

luffyzoro

帖子
23 
主題
2 
精華
0 
積分
25 
點名
0  
作業系統
XP 
軟體版本
EXCEL 2003 
閱讀權限
10 
性別
男 
註冊時間
2011-11-21 
最後登錄
2013-4-9 
17#
發表於 2011-11-25 04:40 | 只看該作者
回復 16# GBKEE

請檢查每一  Workbooks (hubopf) 中是否 有工作表名稱"AC8E"
請問版大這句話的意思是指在公式中的還是說我指定的資料夾每一檔案都要有工作表"AC8E"?

我拿版大15樓的回覆公式貼回我的程式碼?仍然出現同樣的錯誤訊息

或是該公式有沒有其他的寫法?真不知道是哪裡有問題?頭快爆了>"<

我的EXCEL版本是2003有差嗎?

再次感謝

TOP

GBKEE

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

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
18#
發表於 2011-11-25 07:35 | 只看該作者
回復 17# luffyzoro
請檢查每一  Workbooks (hubopf) 中是否 有工作表名稱"AC8E"
=[" & hubopf & "]AC8E!R" & hubcn & "C2
這公式 hubopf  會是 "b.xls" ."c.xls" "d.xls"  不一定的檔案 ,  AC8E 需是這些檔案中一定有的工作表名稱

TOP

luffyzoro

帖子
23 
主題
2 
精華
0 
積分
25 
點名
0  
作業系統
XP 
軟體版本
EXCEL 2003 
閱讀權限
10 
性別
男 
註冊時間
2011-11-21 
最後登錄
2013-4-9 
19#
發表於 2011-11-25 10:22 | 只看該作者
回復 18# GBKEE


版大您好
再次謝謝
如此多次麻煩您,真是不好意思

我現在就只有一個"b.xls"
  1.    Workbooks(hubopf).Worksheets("AC8E").Activate
  2.   Dim hubcn As Integer, hubrwcnt As Integer
  3.    hubrwcnt = ActiveSheet.UsedRange.Rows.Count
  4.   For hubcn = 2 To hubrwcnt - 1
  5.     Workbooks(hubopf).Activate
  6.     Worksheets("AC8E").Range("BT" & hubcn).Select
  7.    If Selection.Value > 0 Then
  8.       Do
  9.         Workbooks(calopf).Worksheets("sheet2").Activate
  10.         Cells(ActiveSheet.Rows.Count, 6).End(xlUp).Select
  11.         Selection.Offset(1, 0).Select
  12.         Dim mycarw As Integer
  13.         mycarw = ActiveCell.Row
  14.         ActiveCell.FormulaR1C1 = _
  15.         "=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)"
  16.         Selection.Value = Selection.Value
  17.         ActiveCell.Offset(1).Select
  18.       Loop Until ActiveCell.Offset(-1).Value = False
複製代碼
以上是片段的程式碼,
我想請教版大,我所想要的ActiveCell.FormulaR1C1 應該是在Workbooks(calopf).worksheets("sheet2")的儲存格之中,是不是我哪邊寫錯了,導致公式並沒在我要的儲存格內?

或是版大願意的話,我可以將檔案寄給版大幫忙看一下>"<

感謝

TOP

GBKEE

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

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
20#
發表於 2011-11-25 12:23 | 只看該作者
回復 19# luffyzoro
導致公式並沒在我要的儲存格內?  你這意思是ActiveCell的位置不對!

我註解你看看
Cells(ActiveSheet.Rows.Count, 6).End(xlUp).Select  'F欄裡最後一個資料的Cells
Selection.Offset(1, 0).Select                                         
F欄裡最後一個資料的Cells的下一列的Cells   這就是 ActiveCell  對不對!

TOP

        靜思自在 : 原諒別人就是善待自己。
123
返回列表 上一主題