請幫幫忙VBA簡化一下唄!!

bear0925900003

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

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

更正  下面的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

學習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

回復 1# bear0925900003
用R1C1表示法

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

複製代碼

bear0925900003

各位大大太神了，感激阿

bear0925900003

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

bear0925900003

回復 4# GBKEE

不能用耶

GBKEE

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

bear0925900003

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

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

GBKEE

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