返回列表 上一主題 發帖

[發問] 如何設一按鈕,就能把公式轉換過的值,取代原有的值?

[發問] 如何設一按鈕,就能把公式轉換過的值,取代原有的值?

各位大大好

我有一個EXCEL檔案,類似員工資料

姓名    工作組別    年資

  A            B                    C
小明    電機A組     1年0月20日
小華    電機B組     2年10月0日
小王    油漆A組     0年0月25日

大概像這樣

年資的部分,已經問到答案了,因為我要轉換成OO年OO月OO日
D去設公式      =TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"日",),"年",":"),"月",":"),"[hh]年mm月ss日")

工作組別,我只需要大類,電機不管在A或B,就顯示電機,油漆就顯示油漆
E去設公式     =IF(MID(B1,1,2)="電機","電機",IF(MID(B1,1,2)="油漆","油漆",""))  就是取B1前兩個字,是電機就顯示電機

只是資料有上百筆,我目前必須先設D跟E ,再把公式貼上百筆
然後得到正確格式的數值後,再複製,貼上值,把B取代,把C取代
得到以下的樣子

  A            B               C
小明    電機     01年00月20日
小華    電機     02年10月00日
小王    油漆     00年00月25日

有沒有辦法設一個按鈕
可以自動偵測我貼進來的資料有幾筆
然後可以自動把B跟C,依照我設的公式去轉變
並取代原本的值

謝謝
哈囉~大家好呀

上傳檔案, 才好處理~~
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

回復 1# iceandy6150

試用看看? 我想應該還會有人寫的比我更簡短吧。
如果B的 電機、油漆...等,都是固定在前面兩個,我想只需要使用MID就可。
  1. Sub test()
  2. x = [A1].CurrentRegion.Rows.Count
  3. For i = 2 To x
  4.     Cells(i, "B") = Mid(Cells(i, "B"), 1, 2)
  5.     y = Application.Substitute(Cells(i, "C"), "日", ":")
  6.     y = Application.Substitute(y, "月", ":")
  7.     y = Application.Substitute(y, "年", ":")
  8.     Cells(i, "C") = Application.Text(y, "hh年mm月ss日")
  9. Next i
  10. End Sub
複製代碼

TOP

回復 3# 蒼雪

謝謝你的程式,可以運作無誤
太棒啦~
感謝

回 准提部林 大大
因為這個程式我不知道怎麼寫
所以就沒丟檔案上來了
哈囉~大家好呀

TOP

回復 3# 蒼雪


    大大,可以再問個進階題嗎

這個按鈕的指令,可不可以 "作用在" 別的檔案? 或是別的SHEET?

例如我現在按鈕是寫在檔案A的Sheet1上面,按下去之後

我想讓Sheet2裡面的資料作變動

或是我想讓檔案B的Sheet2裡面的資料作變動

有辦法嗎?  謝謝
哈囉~大家好呀

TOP

不好意思....剛剛才想到一個問題

原本的設定是
   A       B        C
姓名  組別   年資

但有時候,排列不一定會照這樣
也許第一個檔案是照順序排
但第二個檔案,裡面就不一樣了
可能是

   A       B        C
姓名   年資  組別

程式有沒有辦法說,自己先去找標題列(因為一定會有標題列)
如果該檔案,年資在B攔,則作用在B攔
同理,該檔案年資在C攔,就作用在C攔

不曉得做不做得到
謝謝
哈囉~大家好呀

TOP

回復 4# iceandy6150

Sub 轉換()
Dim Arr, i&, j%, T$
Arr = Range([C1], [A65536].End(xlUp))
For i = 2 To UBound(Arr)
For j = 1 To 3
    T = Arr(i, j)
    If Arr(1, j) = "年資" Then
       T = Application.Text(Replace(Replace(Replace(T, "日", ""), "月", ":"), "年", ":"), "[hh]年mm月ss日")
    ElseIf Arr(1, j) = "組別" And T Like "*[A-z]組*" Then
       T = Left(T, Len(T) - 2)
    End If
    Arr(i, j) = T
Next j
Next i
[A:C].NumberFormatLocal = "@"
[A1:C1].Resize(UBound(Arr)) = Arr
End Sub

要你上傳檔案, 是讓想幫忙的人不用再多花時間去建立測試資料, 這是最基本的提問規則:
X0002.rar (8.45 KB)
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

回復 7# 准提部林

哇~ 版主好強阿  
   
真是太厲害了

感謝

以後發問我會先做個檔案傳上來
謝謝
哈囉~大家好呀

TOP

回復 7# 准提部林

版主您好  我研究了幾天
有一些小問題
再麻煩了

1.您的檔案是.xls,然後我點[開發人員],[設計模式],去點按鈕,居然沒跑出程式碼。不曉得您是怎麼讓按鈕可以啟動程式的?

  (我都是[插入],ActiveX控制項,裡面的按鈕。再用設計模式點開,放入程式碼。但是這樣檔案必須存成.xlsm檔)


2.您的程式中
   Arr = Range([C1], [A65536].End(xlUp))  ---->從右上C1到左下A65536這個範圍內,有使用的範圍到底(可以得出有幾筆資料,也就是幾列)
For i = 2 To UBound(Arr)  ----->從2到有使用的列數,對嗎?

如果我的檔案,不只有 姓名,組別,年資。還有出生 年月日、住址、電話等等
那麼,這樣的寫法,還是可以找出,到底有幾列的資料,對吧? (我測試是都沒問題)


3.承上,您的程式最後面
[A:C].NumberFormatLocal = "@"
[A1:C1].Resize(UBound(Arr)) = Arr
這邊是在把第一欄到第三欄的資料,修改好的,放回去取代原來的,對吧

那,如果我的檔案,不只有 姓名,組別,年資。還有出生 年月日、住址、電話等等
也就是欄,已經不只3欄了,我要自動偵測有幾欄
目前我的寫法是
多設一個K
k = Range("A1", Range("A1").End(xlToRight)).Count
For i = 2 To UBound(Arr)
For j = 1 To k
這樣我測試是都正常,可以找出到底用了幾欄,並且每一欄都會檢查到

之前記得,好像有人教過一個語法
是...   
.UsedRange.Rows.Count
相信如果要計算欄
應該是改成
.UsedRange.Column.Count  對吧?

不曉得
Arr = Range([C1], [A65536].End(xlUp))
For i = 2 To UBound(Arr)

k = Range("A1", Range("A1").End(xlToRight)).Count
For j = 1 To k

.UsedRange.Rows.Count

這幾種是差在哪裡? 都可以用嗎  還是功能有差呢

以上幾個問題請教
感謝
哈囉~大家好呀

TOP

對了,還有個問題我不會解決

就是版主程式最後面
[A:C].NumberFormatLocal = "@"
[A1:C1].Resize(UBound(Arr)) = Arr

原本只有三欄,這樣寫OK
可是如果不知道有幾欄呢?

我用K來得知有幾欄
k = Range("A1", Range("A1").End(xlToRight)).Count

所以是要改成
[A:Text(k)].NumberFormatLocal = "@"
[A1:Text(k)&1].Resize(UBound(Arr)) = Arr
這樣嗎?
好像會有問題耶

再請各位先進解惑,謝謝
哈囉~大家好呀

TOP

        靜思自在 : 人要自愛,才能愛普天下的人。
返回列表 上一主題