[發問] INDIRECT後取值

帖子: 406
主題: 8
精華: 0
積分: 453
點名: 0
作業系統: WINDOWS 7
軟體版本: 2007
閱讀權限: 20
性別: 男
來自: 台灣
註冊時間: 2015-2-7
最後登錄: 2021-7-31

2^#

發表於 2020-5-28 12:43 | 只看該作者

本帖最後由 n7822123 於 2020-5-28 12:45 編輯

回復 1# yc1031

第二步, 在C3~G1000欄位裡帶入分別的工作表資料, 用INDIRECT不成功

要用單引號 ' 把Sheet名子包起來在加驚嘆號 !

C3：=IF(INDIRECT("'"&$C$1&"'!"&ADDRESS(ROW()+1,COLUMN()-1))="","",INDIRECT("'"&$C$1&"'!"&ADDRESS(ROW()+1,COLUMN()-1)))　　

右拉下拉

第三步, 在貼入值區, 把C3~G1000的"值", 貼上到J3~P1000, 貼入值不成功

Range("J3:P1000") = Range("A3:G1000").Value

如果你第3步貼上的值是要一直往下累加的貼，而不是覆蓋原本資料的話

那第2步也不建議用函數做，統一用巨集做會比較好

(只要儲存格有函數，不論有無數值，對於巨集 End 方法來說，都是有值)

程式是依需求寫的，需求表達不清楚
或者沒有上傳附件，愛莫能助

n7822123

　暱稱: 阿龍

帖子: 406
主題: 8
精華: 0
積分: 453
點名: 0
作業系統: WINDOWS 7
軟體版本: 2007
閱讀權限: 20
性別: 男
來自: 台灣
註冊時間: 2015-2-7
最後登錄: 2021-7-31

3^#

發表於 2020-5-28 13:03 | 只看該作者

本帖最後由 n7822123 於 2020-5-28 13:16 編輯

回復 2# n7822123

取工作表名稱區的公式無法自動更正, 有沒有辦法可以自動? (因為日後有可能一直增加工作表)

執行後，V3往下的工作表名稱會更新 (名稱中有"#"才會被列入)

Sub 更新工作表名稱()
Dim ShAll
For Each sh In Sheets
If InStr(sh.Name, "#") Then
ShAll = ShAll & "," & sh.Name
End If
Next
ShAll = Split(Mid(ShAll, 2), ",")
[V3].Resize(UBound(ShAll) + 1) = Application.Transpose(ShAll)
End Sub

複製代碼

如果你想要新增工作表的時候就 "自動執行"

可以在活頁簿事件中，呼叫上面的程序

Private Sub Workbook_NewSheet(ByVal Sh As Object)
更新工作表名稱
End Sub

複製代碼

程式是依需求寫的，需求表達不清楚
或者沒有上傳附件，愛莫能助

帖子: 106
主題: 35
精華: 0
積分: 146
點名: 0
作業系統: Window 7
軟體版本: 2007
閱讀權限: 20
性別: 女
註冊時間: 2011-8-30
最後登錄: 2024-3-26

4^#

發表於 2020-5-28 13:28 | 只看該作者

回復 3# n7822123

十分感謝, 可以自動取工作表名稱了 !

帖子: 106
主題: 35
精華: 0
積分: 146
點名: 0
作業系統: Window 7
軟體版本: 2007
閱讀權限: 20
性別: 女
註冊時間: 2011-8-30
最後登錄: 2024-3-26

5^#

發表於 2020-5-28 13:52 | 只看該作者

回復 2# n7822123

你好,

謝謝你撥空幫我回答問題

第三步的取值, 是因為我不會用vba, 所以我才想用indirect去帶資料後, 錄vba取值貼到"資料貼入區", 之後再錄vba篩選排序, 弄到總表去
如果可以的話, 也是直接用vba 做第三步, 不曉得是否可以教我怎麼做?
* 我日後會再新增帳戶

n7822123

　暱稱: 阿龍

帖子: 406
主題: 8
精華: 0
積分: 453
點名: 0
作業系統: WINDOWS 7
軟體版本: 2007
閱讀權限: 20
性別: 男
來自: 台灣
註冊時間: 2015-2-7
最後登錄: 2021-7-31

6^#

發表於 2020-5-28 14:11 | 只看該作者

本帖最後由 n7822123 於 2020-5-28 14:12 編輯

回復 5# yc1031

謝謝你撥空幫我回答問題

第三步的取值, 是因為我不會用vba, 所以我才想用indirect去帶資料後, 錄vba取值貼到"資料貼入區", 之後再錄vba篩選排序, 弄到總表去
如果可以的話, 也是直接用vba 做第三步, 不曉得是否可以教我怎麼做?
* 我日後會再新增帳戶

用函數再用VBA的 End方法會有問題的 End(xlUP) = 按住Ctrl + 鍵盤方向箭向上

你可以做個小實驗，開一個新工作表

在[B2]儲存格填入公式 B2 =""  (空資料)

手動複製B2儲存格，選擇 A8儲存格滑鼠右鍵 > 選擇性貼上 > 值

然後選擇 A15 儲存格 (A8以後都行) 按住Ctrl + 鍵盤方向箭向上  儲存格會停留在A8 不是 A1

雖然A8 看起來沒有人任何東西!  這是Excel 一直有的問題，UsedRange也有同樣問題

準備上班去了，你要的第3步不難，

看這裡有沒有人幫你寫，沒有的話我上班回來再幫你

程式是依需求寫的，需求表達不清楚
或者沒有上傳附件，愛莫能助

版主 Rank: 7 Rank: 7 Rank: 7

准提部林

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-22

7^#

發表於 2020-5-28 18:27 | 只看該作者

1) 定義名稱
=GET.WORKBOOK(1)&T(NOW())  加這個可以工作表新增或刪除而自動更新
2) C1公式
=IF(A1="","",TRIM(RIGHT(SUBSTITUTE(LOOKUP(1,0/(RIGHT(QM)=A1),QM),"]",REPT(" ",99)),99)))
3) C3公式/右拉下拉
=INDEX(INDIRECT("'"&$C$1&"'!B:F"),ROW($A1)+3,COLUMN(A$1))&""
4) B3公式/下拉
  =IF(C3="","",(N(B2)+1)^(C3=C2))

5) 轉貼VBA
Sub 轉貼()
Dim R, xE As Range
R = Application.Match(9E+307, [B:B])
If IsError(R) Then Exit Sub
Set xE = [J1].Cells(Rows.Count, 1).End(xlUp)(2)
With Range("A3:G" & R)
   xE.Resize(.Rows.Count, .Columns.Count) = .Value
End With
End Sub

以B欄數字為MATCH範圍, 用9E+307找到最後一筆

====================================

帖子: 106
主題: 35
精華: 0
積分: 146
點名: 0
作業系統: Window 7
軟體版本: 2007
閱讀權限: 20
性別: 女
註冊時間: 2011-8-30
最後登錄: 2024-3-26

8^#

發表於 2020-5-29 08:57 | 只看該作者

回復 6# n7822123

感謝你!　我也試了幾次，原來單位格裡有公式的，　即使無資料，都會被ＣＯＰＹ到，所以用Ｆ５，　我就篩不出來了。

帖子: 106
主題: 35
精華: 0
積分: 146
點名: 0
作業系統: Window 7
軟體版本: 2007
閱讀權限: 20
性別: 女
註冊時間: 2011-8-30
最後登錄: 2024-3-26

9^#

發表於 2020-5-29 09:01 | 只看該作者

回復 7# 准提部林

感謝你解答我所有的問題！

第一步: S欄有新增/刪減帳戶工作表時可自動更新檔名 <----已OK
第二步: 帶入各帳戶明細到C~G欄位 <----已OK
第三步: 把C~G欄位的值貼入J~P ( 不覆蓋原本的資料) <--已OK
* 我試著用錄VBA的方式把U欄位的代碼輸入A1, 再執行"貼入值VBA", 但這樣只能一筆一筆試到Z, 有沒有辦法可以用T欄位來辨別, 意思是: 我只有A~G的帳戶, 那就是A1, 只輸入到G, 那H~Z就不用做了 (可日後若是新增帳戶就是往下繼續)

2020年_帳戶進出記錄TEST.rar (66.56 KB)

版主 Rank: 7 Rank: 7 Rank: 7

准提部林

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-22

10^#

發表於 2020-5-29 11:17 | 只看該作者

回復 9# yc1031

用不到公式,
只要工作表名稱最後為"#"加英文字, 即自動陸續抓出有效範圍貼入值:

Sub 依帳戶貼入值()
Dim Sht As Worksheet, T$, R&, xE As Range
[驗算!I3:P6000].ClearContents
For Each Sht In Sheets
T = Right(Replace(UCase(Sht.Name), "#", "$"), 2)
If Not T Like "$[A-Z]" Then GoTo 101
R = Sht.Cells(Rows.Count, 2).End(xlUp).Row - 3
If R <= 0 Then GoTo 101
Set xE = [驗算!I1].Cells(Rows.Count, 1).End(xlUp)
xE(2, 3).Resize(R, 6) = Sht.[A4].Resize(R, 7).Value
xE(2, 1).Resize(R) = UCase(Sht.Name)
xE(2, 2).Resize(R) = Right(T, 1)
101: Next
End Sub

'=====================================