入黎免費幫你砌excel表 / 教你formula (8)

會當係重複呀，我本身諗住一個字一格之後再用solting搞

想問各位大佬 excel vba short cut, 將一個cell match 另一個tab 既cell, 再跳返去個個cell 既某個column…. 可以點打

Sor for 1999

有冇圖述？

大既係圖中sheet 2 禁個short cut key (ie Ctrl + A), 就會match 番Sheet 1 column A, 再跳去 column C / 或者跳去 C2

Short cut 個code 已有應該唔洗改
現有code
If StrComp(ws_name,名，vb TextCompare) = 0 Then
Col_current=ws_current.Range(自訂）. Find (自訂，Lookin:=xlValues).Column
Else if 唔同 range

唔該曬咁多位Ching 先

我計左一陣, 呢個反而有D難

如果用Beta版既office 365有vstack我都勉強做到, 但都應該會做到好長好核突

普通版office 365我諗唔到咩方法簡潔咁做
有冇巴打有興趣做下

直按sheet1 嘅ABCDE fill哂落 sheet2.[a2:a6] 到，咁樣？
然後select sheet2.[c2]?

本身個excel 好大，好難改個structure

其實係唔係下面最後指返邊個column 就ok?

Col_current=ws_current.Range(自訂）. Find (自訂，Lookin:=xlValues).Column

Sub Testing()
Dim TarSht As Worksheet, LastRow As Integer
Dim MyRng As Range
Set TarSht = ActiveSheet

With ThisWorkbook.Sheets(1)
    
    If Not .Cells(2, 1) = Empty Then
        LastRow = .Cells(1, 1).End(xlDown).Row
    Else
        End
    End If

    Set MyRng = .Range(.Cells(2, 1), .Cells(LastRow, 1))
    
End With

With TarSht
    
    TarSht.Range(MyRng.Address).Value = MyRng.Value
    TarSht.[c2].Select
    
End With

End Sub

唔知係咪你要嘅嘢

唔該晒大佬 ，聽朝番到公司test 下

Sub Testing()
Dim TarSht As Worksheet, LastRow As Integer
Dim MyRng As Range
Set TarSht = ActiveSheet

With ThisWorkbook.Sheets(1)
    
    If Not .Cells(2, 1) = Empty Then
        LastRow = .Cells(1, 1).End(xlDown).Row
    Else
        End
    End If

    Set MyRng = .Range(.Cells(2, 1), .Cells(LastRow, 1))
    
End With

With TarSht
    
    .Range(MyRng.Address).Value = MyRng.Value
    .[c2].Select
    
End With

End Sub

強逼症想改返少少嘢，雖然上面嗰個都得

Thanks

自己寫完都唔係好滿意

推

係啦個result同你呢個差唔多
就係想list曬所有選擇出嚟

唔介意用VBA既話, 我搵到呢個work
https://stackoverflow.com/questions/48150054/listing-all-possible-combination-without-repetition-vba

咁你照寫就得

點樣係excel 入面 由左邊個table 整到右邊咁

用Pivot table

試過都係變唔到想要既

首先你要將你raw data個Table加入去data model
然後用power pivot create一個measure
個measure要打code: =CONCATENATEX('表格1','表格1'[Status],",")

個"表格1"係個table 係data model入面既名黎
咁就會做到

我講得比較粗略, 如果你研究完唔識既再問, 到時再詳細D講你唔識既位

得左 thx bro

lm

如果我有份excel form要填，大概10個cell，但位置好random (i.e. B2, H2, C7, E15 etc)
有無辨法一個sheet 裝data source 然後按每個row嘅Data去auto generate sheet?

最簡單就喺excel form入面有個cell比你手動specify返個index
d random cell就用formula lookup呢個index相應既row data