麻辣家族討論版版 - Powered by Discuz! Board

標題: 請幫幫忙VBA簡化一下唄!! [打印本頁]

作者: bear0925900003 時間: 2013-7-26 18:43 標題: 請幫幫忙VBA簡化一下唄!!

本帖最後由 GBKEE 於 2013-7-27 07:16 編輯

請各位大大能幫忙簡化一下下面的程式，不勝感激啊!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("D4") = "=IF(C4="""","""",VLOOKUP(日報表1!C4,資料庫!$A$1:$B$66,2))"
Range("D5") = "=IF(C5="""","""",VLOOKUP(日報表1!C5,資料庫!$A$1:$B$66,2))"
Range("D6") = "=IF(C6="""","""",VLOOKUP(日報表1!C6,資料庫!$A$1:$B$66,2))"
Range("D7") = "=IF(C7="""","""",VLOOKUP(日報表1!C7,資料庫!$A$1:$B$66,2))"
Range("D8") = "=IF(C8="""","""",VLOOKUP(日報表1!C8,資料庫!$A$1:$B$66,2))"
Range("D9") = "=IF(C9="""","""",VLOOKUP(日報表1!C9,資料庫!$A$1:$B$66,2))"
Range("D10") = "=IF(C10="""","""",VLOOKUP(日報表1!C10,資料庫!$A$1:$B$66,2))"
Range("D11") = "=IF(C11="""","""",VLOOKUP(日報表1!C11,資料庫!$A$1:$B$66,2))"
Range("D12") = "=IF(C12="""","""",VLOOKUP(日報表1!C12,資料庫!$A$1:$B$66,2))"
Range("D13") = "=IF(C13="""","""",VLOOKUP(日報表1!C13,資料庫!$A$1:$B$66,2))"
Range("D14") = "=IF(C14="""","""",VLOOKUP(日報表1!C14,資料庫!$A$1:$B$66,2))"
Range("D15") = "=IF(C15="""","""",VLOOKUP(日報表1!C15,資料庫!$A$1:$B$66,2))"
Range("D16") = "=IF(C16="""","""",VLOOKUP(日報表1!C16,資料庫!$A$1:$B$66,2))"
Range("D17") = "=IF(C17="""","""",VLOOKUP(日報表1!C17,資料庫!$A$1:$B$66,2))"
Range("D18") = "=IF(C18="""","""",VLOOKUP(日報表1!C18,資料庫!$A$1:$B$66,2))"
Range("D19") = "=IF(C19="""","""",VLOOKUP(日報表1!C19,資料庫!$A$1:$B$66,2))"
Range("D20") = "=IF(C20="""","""",VLOOKUP(日報表1!C20,資料庫!$A$1:$B$66,2))"
Range("D21") = "=IF(C21="""","""",VLOOKUP(日報表1!C21,資料庫!$A$1:$B$66,2))"
Range("D22") = "=IF(C22="""","""",VLOOKUP(日報表1!C22,資料庫!$A$1:$B$66,2))"
Range("D23") = "=IF(C23="""","""",VLOOKUP(日報表1!C23,資料庫!$A$1:$B$66,2))"
Range("D24") = "=IF(C24="""","""",VLOOKUP(日報表1!C24,資料庫!$A$1:$B$66,2))"
Range("D25") = "=IF(C25="""","""",VLOOKUP(日報表1!C25,資料庫!$A$1:$B$66,2))"
Range("D26") = "=IF(C26="""","""",VLOOKUP(日報表1!C26,資料庫!$A$1:$B$66,2))"
Range("D27") = "=IF(C27="""","""",VLOOKUP(日報表1!C27,資料庫!$A$1:$B$66,2))"
Range("D28") = "=IF(C28="""","""",VLOOKUP(日報表1!C28,資料庫!$A$1:$B$66,2))"
Range("D29") = "=IF(C29="""","""",VLOOKUP(日報表1!C29,資料庫!$A$1:$B$66,2))"
Range("D30") = "=IF(C30="""","""",VLOOKUP(日報表1!C30,資料庫!$A$1:$B$66,2))"
Range("D31") = "=IF(C31="""","""",VLOOKUP(日報表1!C31,資料庫!$A$1:$B$66,2))"
Range("D32") = "=IF(C32="""","""",VLOOKUP(日報表1!C32,資料庫!$A$1:$B$66,2))"
Range("D33") = "=IF(C33="""","""",VLOOKUP(日報表1!C33,資料庫!$A$1:$B$66,2))"

If [c4] <> "" And [a2] = "" Then [a2] = Date
If [c4] = "" Then [a2] = ""

If [c4] <> "" And [b4] = "" Then [b4] = Date
If [c4] = "" Then [b4] = ""
End Sub

作者: bear0925900003 時間: 2013-7-26 18:46

更正下面的IF公式是這部分要簡化，下面的公式要從C4一直重複很多，拜託幫幫忙，感恩

If [c4] <> "" And [b4] = "" Then [b4] = Date
If [c4] = "" Then [b4] = ""
If [c5] <> "" And [b5] = "" Then [b5] = Date
If [c5] = "" Then [b5] = ""

作者: oobird 時間: 2013-7-26 21:58

學習vba最基本的要學習建立迴圈，如此例可以這樣：
For i = 4 To 100
If Range("c" & i) = "" Then
Range("b" & i) = ""
Else
If Range("b" & i) = "" Then Range("b" & i) = Date
End If
Next

作者: GBKEE 時間: 2013-7-27 07:15

回復 1# bear0925900003
用R1C1表示法

Range("D4:D33").FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(日報表1!RC[-1],資料庫!R1C1:R2C66,2))"

複製代碼

作者: bear0925900003 時間: 2013-7-28 01:51

各位大大太神了，感激阿

作者: bear0925900003 時間: 2013-7-29 09:47

對不起!!小弟看書自學，"迂迴"這篇始終無法參透.....

作者: bear0925900003 時間: 2013-7-29 16:57

回復 4# GBKEE

不能用耶

作者: GBKEE 時間: 2013-7-29 17:25

回復 7# bear0925900003
你說:不能用耶我也不知道,你要說清楚錯誤值是何?,可附檔看看

作者: bear0925900003 時間: 2013-7-30 09:05

Range("D4

33").FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(日報表1!RC[-1],資料庫!R1C1:R2C66,2))"

這個公式理論上應該D4~D33都能具有相同的功能，但試驗後只有D4、D5可以找到正確資訊，D5之後都沒法正確對到相對資訊

作者: GBKEE 時間: 2013-7-30 09:22

回復 9# bear0925900003
請再詳看工作表函數 VLOOKUP的用法說明

作者: bear0925900003 時間: 2013-7-30 11:33

解決了!!感恩

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