Solved: For Statement Inside Select Statement

[PaSha]

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

[Bob Phillips]

Select Case mesec

Case 1

Call UpdateData(.Range("N68"), .Range("N70"), .Range("N61"), .Range("N62"))

Case 2

Call UpdateData(.Range("C68"), .Range("C70"), .Range("C61"), .Range("C62"))

Case 3

Call UpdateData(.Range("D68"), .Range("D70"), .Range("D61"), .Range("D62"))

'and so on till 12

Case 12

Call UpdateData(.Range("M68"), .Range("M70"), .Range("M61"), .Range("M62"))

End Select

' and then further along a separate procedure

Private Sub UpdateData(Target1 As Range, Target2 As Range, _
Target3 As Range, Target4 As Range)


Target1.Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E28").Value
Target2.Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K28").Value
Target3.Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("E53").Value
Target4.Value = Workbooks("Blabla.xls").Sheets("Sheet1").Range("K53").Value
End Sub

[PaSha]

Thanks XLD :hi:... you're still the best :friends:

every time i learn something new :yes

but i also realised that the part of my code with For and so on also works!?... i was just not puting the right adress of the Workbook that's why it didn't write inside those values...
but for the code to work ok i had to paste one more Case ... Case 1...

so thanks again VBAEXPRESS rocks:beerchug:

[Bob Phillips]

Would you like to show us the code that you are referring to? I cannot see how you can have a loop for Select Case as it it is checking an item for a set of values, not looping through a collection or range or whatever.

[PaSha]

Set x = Workbooks.Open("C:\Users\User\Documents\Company\KOST\CCAbc_prot.xls")
Windows(x.Name).Visible = False
With x.Worksheets(2)

For x = 2 To 12 Step 1
Select Case mesec

Case x

.Range(Chr(65 + x) & "68").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("E28").Value
.Range(Chr(65 + x) & "70").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("K28").Value

.Range(Chr(65 + x) & "61").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("E53").Value
.Range(Chr(65 + x) & "62").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("K53").Value
.Range(Chr(65 + x) & "63").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("K78").Value
.Range(Chr(65 + x) & "64").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("E78").Value

Case 1

.Range("N68").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("E28").Value
.Range("N70").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("K28").Value

.Range("N61").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("E53").Value
.Range("N62").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("K53").Value
.Range("N63").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("K78").Value
.Range("N64").Value = Workbooks("Book1.xls").Sheets("Sheet1").Range("E78").Value


End Select
Next
End With

Windows("CCAbc_prot.xls").Visible = True
Workbooks("CCAbc_prot.xls").Close SaveChanges:=True


Where the parameter"mesec" is a VALUE OF A CELL which contains the numeric month...for ex.this month is: 9

the code works!! i checked it several times...

the Range(Chr(65 + x) & "64") meens...that i am walking true alphabetic order...starting from C...
In ASCII code (HEX) is C number: 67
so i had to start with 65+x (which is at first 2) to get the C ...and then so on D E F...

oh and because there are some specifics when the january comes...a have to put the values into december... and couldn't use the 1 in the For statement...so and have had to put an extra case 1...