PaSha
08-28-2008, 01:27 AM
Helo...
need some help...
the problem is that there is actually no problem, but i have a bunch of code and i would like to shorten it with some for statements but don't know how because i get only errors or it does nothing...
so the code look like this:
Select case mesec
Case 1
.Range("N68").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E28").Value
.Range("N70").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K28").Value
.Range("N61").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E53").Value
.Range("N62").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K53").Value
Case 2
.Range("C68").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E28").Value
.Range("C70").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K28").Value
.Range("C61").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E53").Value
.Range("C62").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K53").Value
Case 3
.Range("D68").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E28").Value
.Range("D70").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K28").Value
.Range("D61").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E53").Value
.Range("D62").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K53").Value
'and so on till 12
Case 12
.Range("M68").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E28").Value
.Range("M70").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K28").Value
.Range("M61").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E53").Value
.Range("M62").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K53").Value
End Select
so hope you see what i meen... i get a lot rows of code which is the same only the ALPHABET is going from N... and then from C to M ...
so is there a posibility to make a for statement to fill this in??
i tryed like this but won't work:
For x = 2 To 12 Step 1
Select Case mesec
Case x
.Range(Chr(65 + x) & "68").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E28").Value
.Range(Chr(65 + x) & "70").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K28").Value
.Range(Chr(65 + x) & "61").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E53").Value
.Range(Chr(65 + x) & "62").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K53").Value
End Select
Next
: pray2::doh::dunno
need some help...
the problem is that there is actually no problem, but i have a bunch of code and i would like to shorten it with some for statements but don't know how because i get only errors or it does nothing...
so the code look like this:
Select case mesec
Case 1
.Range("N68").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E28").Value
.Range("N70").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K28").Value
.Range("N61").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E53").Value
.Range("N62").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K53").Value
Case 2
.Range("C68").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E28").Value
.Range("C70").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K28").Value
.Range("C61").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E53").Value
.Range("C62").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K53").Value
Case 3
.Range("D68").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E28").Value
.Range("D70").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K28").Value
.Range("D61").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E53").Value
.Range("D62").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K53").Value
'and so on till 12
Case 12
.Range("M68").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E28").Value
.Range("M70").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K28").Value
.Range("M61").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E53").Value
.Range("M62").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K53").Value
End Select
so hope you see what i meen... i get a lot rows of code which is the same only the ALPHABET is going from N... and then from C to M ...
so is there a posibility to make a for statement to fill this in??
i tryed like this but won't work:
For x = 2 To 12 Step 1
Select Case mesec
Case x
.Range(Chr(65 + x) & "68").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E28").Value
.Range(Chr(65 + x) & "70").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K28").Value
.Range(Chr(65 + x) & "61").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E53").Value
.Range(Chr(65 + x) & "62").Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K53").Value
End Select
Next
: pray2::doh::dunno