標題: [發問] INDIRECT後取值 [打印本頁]

---

作者: yc1031    時間: 2020-5-28 10:25     標題: INDIRECT後取值

本帖最後由 yc1031 於 2020-5-28 10:38 編輯

請問大家

1. 用INDIRECT取不同工作表的資料要怎麼辦到? 目前查到的用=INDIRECT($C$1&""&"B4"), 但也無法帶出貨料
2. 帶入資料後, 再怎麼全部以取值的資料帶到 資料貼入區? 我有試著用 f5 去取, 但都會取到空白格
    *帶到資料貼入區時, 要跳到空白列才能貼, 我是用Range("L4").End(xlDown).Offset(1, 0).Select

我也有把我的問題列在excel檔裡, 再請大家幫忙, 感謝!

---

作者: n7822123    時間: 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    時間: 2020-5-28 13:03

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

回復 2# n7822123


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

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

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

複製代碼

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

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

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

複製代碼

---

作者: yc1031    時間: 2020-5-28 13:28

回復 3# n7822123


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

---

作者: yc1031    時間: 2020-5-28 13:52

回復 2# n7822123


你好,

謝謝你撥空幫我回答問題

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

---

作者: n7822123    時間: 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步不難，

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

---

作者: 准提部林    時間: 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找到最後一筆


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

---

作者: yc1031    時間: 2020-5-29 08:57

回復 6# n7822123



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

---

作者: yc1031    時間: 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-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


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

---

作者: yc1031    時間: 2020-6-2 08:17

回復 10# 准提部林

你好,

謝謝幫忙! 想再請問一下, 若帳號#後, 不是用A-Z, 而是用數字表示, 類如 一銀#7777, 一銀#8888,  這樣的話, VBA碼要怎麼改呢?

---

作者: 准提部林    時間: 2020-6-2 15:33

回復 11# yc1031

Sub 依帳戶貼入值()
Dim Sht As Worksheet, T$, R&, xE As Range
[驗算!I3:P6000].ClearContents
For Each Sht In Sheets
    If INSTR(Sht.NAME ,"#")=0 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) = Sht.Name
    xE(2, 2).Resize(R) = "#" & SPLIT(Sht.Name,"#")(1)
101: Next
End Sub

---

作者: yc1031    時間: 2020-6-14 09:56

回復 12# 准提部林


    十分感謝你!  問題已經有解決了。  再次謝謝

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)