請幫幫忙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

中學生 Rank: 2

帖子: 55
主題: 17
精華: 0
積分: 79
點名: 0
作業系統: XP
軟體版本: office2007
閱讀權限: 20
性別: 男
註冊時間: 2013-7-17
最後登錄: 2020-12-31

2^#

發表於 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] = ""

你猜!!

TOP

oobird

　頭銜: 街友一族

超級版主 Rank: 8 Rank: 8

帖子: 1572
主題: 16
精華: 2
積分: 1521
點名: 0
作業系統: xp
軟體版本: office 2003
閱讀權限: 150
性別: 男
註冊時間: 2010-5-1
最後登錄: 2016-1-13

論壇貢獻勳章

3^#

發表於 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

TOP

GBKEE