Soru for ve foreach donguleri ile formul

[camlica2018]

herkese selamlar, bu formulleri for foreach ile kisatmak istiyorum, birkac varyasyon denedim ama basarisiz oldu.
1.si bu:

Worksheets("Production").Range("E6").Value = UserForm.TextBox8
Worksheets("Production").Range("F6").Value = UserForm.TextBox9
Worksheets("Production").Range("G6").Value = UserForm.TextBox10
Worksheets("Production").Range("H6").Value = UserForm.TextBox11
Worksheets("Production").Range("E7").Value = UserForm.TextBox12
Worksheets("Production").Range("F7").Value = UserForm.TextBox13
Worksheets("Production").Range("G7").Value = UserForm.TextBox14
Worksheets("Production").Range("H7").Value = UserForm.TextBox15
Worksheets("Production").Range("E8").Value = UserForm.TextBox16
Worksheets("Production").Range("F8").Value = UserForm.TextBox17
Worksheets("Production").Range("G8").Value = UserForm.TextBox18
Worksheets("Production").Range("H8").Value = UserForm.TextBox19
Worksheets("Production").Range("E9").Value = UserForm.TextBox20
Worksheets("Production").Range("F9").Value = UserForm.TextBox21
Worksheets("Production").Range("G9").Value = UserForm.TextBox22
Worksheets("Production").Range("H9").Value = UserForm.TextBox23
Worksheets("Production").Range("E10").Value = UserForm.TextBox24
Worksheets("Production").Range("F10").Value = UserForm.TextBox25
Worksheets("Production").Range("G10").Value = UserForm.TextBox26
Worksheets("Production").Range("H10").Value = UserForm.TextBox27
Worksheets("Production").Range("E11").Value = UserForm.TextBox28
Worksheets("Production").Range("F11").Value = UserForm.TextBox29
Worksheets("Production").Range("G11").Value = UserForm.TextBox30
Worksheets("Production").Range("H11").Value = UserForm.TextBox31
Worksheets("Production").Range("E12").Value = UserForm.TextBox32
Worksheets("Production").Range("F12").Value = UserForm.TextBox33
Worksheets("Production").Range("G12").Value = UserForm.TextBox34
Worksheets("Production").Range("H12").Value = UserForm.TextBox35
2.si

UserForm.TextBox43 = Worksheets("Par Pick_List_xlsx").Range("C5").Value
UserForm.TextBox44 = Worksheets("Par Pick_List_xlsx").Range("D5").Value
UserForm.TextBox45 = Worksheets("Par Pick_List_xlsx").Range("E5").Value
UserForm.TextBox46 = Worksheets("Par Pick_List_xlsx").Range("F5").Value
UserForm.TextBox47 = Worksheets("Par Pick_List_xlsx").Range("G5").Value
UserForm.TextBox48 = Worksheets("Par Pick_List_xlsx").Range("H5").Value
UserForm.TextBox49 = Worksheets("Par Pick_List_xlsx").Range("I5").Value
' ----------------------------------------------------------------------
UserForm.TextBox50 = Worksheets("Par Pick_List_xlsx").Range("C6").Value
UserForm.TextBox51 = Worksheets("Par Pick_List_xlsx").Range("D6").Value
UserForm.TextBox52 = Worksheets("Par Pick_List_xlsx").Range("E6").Value
UserForm.TextBox53 = Worksheets("Par Pick_List_xlsx").Range("F6").Value
UserForm.TextBox54 = Worksheets("Par Pick_List_xlsx").Range("G6").Value
UserForm.TextBox55 = Worksheets("Par Pick_List_xlsx").Range("H6").Value
UserForm.TextBox56 = Worksheets("Par Pick_List_xlsx").Range("I6").Value
' ----------------------------------------------------------------------
UserForm.TextBox57 = Worksheets("Par Pick_List_xlsx").Range("C7").Value
UserForm.TextBox58 = Worksheets("Par Pick_List_xlsx").Range("D7").Value
UserForm.TextBox59 = Worksheets("Par Pick_List_xlsx").Range("E7").Value
UserForm.TextBox60 = Worksheets("Par Pick_List_xlsx").Range("F7").Value
UserForm.TextBox61 = Worksheets("Par Pick_List_xlsx").Range("G7").Value
UserForm.TextBox62 = Worksheets("Par Pick_List_xlsx").Range("H7").Value
UserForm.TextBox63 = Worksheets("Par Pick_List_xlsx").Range("I7").Value
' ----------------------------------------------------------------------
UserForm.TextBox64 = Worksheets("Par Pick_List_xlsx").Range("C8").Value
UserForm.TextBox65 = Worksheets("Par Pick_List_xlsx").Range("D8").Value
UserForm.TextBox66 = Worksheets("Par Pick_List_xlsx").Range("E8").Value
UserForm.TextBox67 = Worksheets("Par Pick_List_xlsx").Range("F8").Value
UserForm.TextBox68 = Worksheets("Par Pick_List_xlsx").Range("G8").Value
UserForm.TextBox69 = Worksheets("Par Pick_List_xlsx").Range("H8").Value
UserForm.TextBox70 = Worksheets("Par Pick_List_xlsx").Range("I8").Value
' ----------------------------------------------------------------------
UserForm.TextBox71 = Worksheets("Par Pick_List_xlsx").Range("C9").Value
UserForm.TextBox72 = Worksheets("Par Pick_List_xlsx").Range("D9").Value
UserForm.TextBox73 = Worksheets("Par Pick_List_xlsx").Range("E9").Value
UserForm.TextBox74 = Worksheets("Par Pick_List_xlsx").Range("F9").Value
UserForm.TextBox75 = Worksheets("Par Pick_List_xlsx").Range("G9").Value
UserForm.TextBox76 = Worksheets("Par Pick_List_xlsx").Range("H9").Value
UserForm.TextBox77 = Worksheets("Par Pick_List_xlsx").Range("I9").Value
' ----------------------------------------------------------------------
UserForm.TextBox78 = Worksheets("Par Pick_List_xlsx").Range("C10").Value
UserForm.TextBox79 = Worksheets("Par Pick_List_xlsx").Range("D10").Value
UserForm.TextBox80 = Worksheets("Par Pick_List_xlsx").Range("E10").Value
UserForm.TextBox81 = Worksheets("Par Pick_List_xlsx").Range("F10").Value
UserForm.TextBox82 = Worksheets("Par Pick_List_xlsx").Range("G10").Value
UserForm.TextBox83 = Worksheets("Par Pick_List_xlsx").Range("H10").Value
UserForm.TextBox84 = Worksheets("Par Pick_List_xlsx").Range("I10").Value
' ----------------------------------------------------------------------
UserForm.TextBox85 = Worksheets("Par Pick_List_xlsx").Range("C11").Value
UserForm.TextBox86 = Worksheets("Par Pick_List_xlsx").Range("D11").Value
UserForm.TextBox87 = Worksheets("Par Pick_List_xlsx").Range("E11").Value
UserForm.TextBox88 = Worksheets("Par Pick_List_xlsx").Range("F11").Value
UserForm.TextBox89 = Worksheets("Par Pick_List_xlsx").Range("G11").Value
UserForm.TextBox90 = Worksheets("Par Pick_List_xlsx").Range("H11").Value
UserForm.TextBox91 = Worksheets("Par Pick_List_xlsx").Range("I11").Value
' ----------------------------------------------------------------------
UserForm.TextBox92 = Worksheets("Par Pick_List_xlsx").Range("C12").Value
UserForm.TextBox93 = Worksheets("Par Pick_List_xlsx").Range("D12").Value
UserForm.TextBox94 = Worksheets("Par Pick_List_xlsx").Range("E12").Value
UserForm.TextBox95 = Worksheets("Par Pick_List_xlsx").Range("F12").Value
UserForm.TextBox96 = Worksheets("Par Pick_List_xlsx").Range("G12").Value
UserForm.TextBox97 = Worksheets("Par Pick_List_xlsx").Range("H12").Value
UserForm.TextBox98 = Worksheets("Par Pick_List_xlsx").Range("I12").Value
' ----------------------------------------------------------------------
UserForm.TextBox99 = Worksheets("Par Pick_List_xlsx").Range("C13").Value
UserForm.TextBox100 = Worksheets("Par Pick_List_xlsx").Range("D13").Value
UserForm.TextBox101 = Worksheets("Par Pick_List_xlsx").Range("E13").Value
UserForm.TextBox102 = Worksheets("Par Pick_List_xlsx").Range("F13").Value
UserForm.TextBox103 = Worksheets("Par Pick_List_xlsx").Range("G13").Value
UserForm.TextBox104 = Worksheets("Par Pick_List_xlsx").Range("H13").Value
UserForm.TextBox105 = Worksheets("Par Pick_List_xlsx").Range("I13").Value
' ----------------------------------------------------------------------
UserForm.TextBox106 = Worksheets("Par Pick_List_xlsx").Range("C14").Value
UserForm.TextBox107 = Worksheets("Par Pick_List_xlsx").Range("D14").Value
UserForm.TextBox108 = Worksheets("Par Pick_List_xlsx").Range("E14").Value
UserForm.TextBox109 = Worksheets("Par Pick_List_xlsx").Range("F14").Value
UserForm.TextBox110 = Worksheets("Par Pick_List_xlsx").Range("G14").Value
UserForm.TextBox111 = Worksheets("Par Pick_List_xlsx").Range("H14").Value
UserForm.TextBox112 = Worksheets("Par Pick_List_xlsx").Range("I14").Value
' ----------------------------------------------------------------------
UserForm.TextBox113 = Worksheets("Par Pick_List_xlsx").Range("C15").Value
UserForm.TextBox114 = Worksheets("Par Pick_List_xlsx").Range("D15").Value
UserForm.TextBox115 = Worksheets("Par Pick_List_xlsx").Range("E15").Value
UserForm.TextBox116 = Worksheets("Par Pick_List_xlsx").Range("F15").Value
UserForm.TextBox117 = Worksheets("Par Pick_List_xlsx").Range("G15").Value
UserForm.TextBox118 = Worksheets("Par Pick_List_xlsx").Range("H15").Value
UserForm.TextBox119 = Worksheets("Par Pick_List_xlsx").Range("I15").Value
' ----------------------------------------------------------------------
UserForm.TextBox120 = Worksheets("Par Pick_List_xlsx").Range("C16").Value
UserForm.TextBox121 = Worksheets("Par Pick_List_xlsx").Range("D16").Value
UserForm.TextBox122 = Worksheets("Par Pick_List_xlsx").Range("E16").Value
UserForm.TextBox123 = Worksheets("Par Pick_List_xlsx").Range("F16").Value
UserForm.TextBox124 = Worksheets("Par Pick_List_xlsx").Range("G16").Value
UserForm.TextBox125 = Worksheets("Par Pick_List_xlsx").Range("H16").Value
UserForm.TextBox126 = Worksheets("Par Pick_List_xlsx").Range("I16").Value
' ----------------------------------------------------------------------
UserForm.TextBox127 = Worksheets("Par Pick_List_xlsx").Range("C17").Value
UserForm.TextBox128 = Worksheets("Par Pick_List_xlsx").Range("D17").Value
UserForm.TextBox129 = Worksheets("Par Pick_List_xlsx").Range("E17").Value
UserForm.TextBox130 = Worksheets("Par Pick_List_xlsx").Range("F17").Value
UserForm.TextBox131 = Worksheets("Par Pick_List_xlsx").Range("G17").Value
UserForm.TextBox132 = Worksheets("Par Pick_List_xlsx").Range("H17").Value
UserForm.TextBox133 = Worksheets("Par Pick_List_xlsx").Range("I17").Value
' ----------------------------------------------------------------------
UserForm.TextBox134 = Worksheets("Par Pick_List_xlsx").Range("C18").Value
UserForm.TextBox135 = Worksheets("Par Pick_List_xlsx").Range("D18").Value
UserForm.TextBox136 = Worksheets("Par Pick_List_xlsx").Range("E18").Value
UserForm.TextBox137 = Worksheets("Par Pick_List_xlsx").Range("F18").Value
UserForm.TextBox138 = Worksheets("Par Pick_List_xlsx").Range("G18").Value
UserForm.TextBox139 = Worksheets("Par Pick_List_xlsx").Range("H18").Value
UserForm.TextBox140 = Worksheets("Par Pick_List_xlsx").Range("I18").Value

' ----------------------------------------------------------------------
UserForm.TextBox141 = Worksheets("Par Pick_List_xlsx").Range("C19").Value
UserForm.TextBox142 = Worksheets("Par Pick_List_xlsx").Range("D19").Value
UserForm.TextBox143 = Worksheets("Par Pick_List_xlsx").Range("E19").Value
UserForm.TextBox144 = Worksheets("Par Pick_List_xlsx").Range("F19").Value
UserForm.TextBox145 = Worksheets("Par Pick_List_xlsx").Range("G19").Value
UserForm.TextBox146 = Worksheets("Par Pick_List_xlsx").Range("H19").Value
UserForm.TextBox147 = Worksheets("Par Pick_List_xlsx").Range("I19").Value

tesekkurler

 

[Muzaffer Ali]

Merhaba.

Hücre adresleri hep sabitse kod yazmanıza gerek yok. ControlSource özelliğine hücre adresi yazmanız yeterli.
TextBoxta bir değişiklik olduğunda otomatik hücre değeri de değişir.
Hücrede bir değişiklik olduğunda da textbox değeri de değişir.

Örneğin Texbox8'in ControlSource özellik kısmına Production!E6 yazmalısınız.

 

[camlica2018]

tesekkurler