Looping with range in VBA, help please

[iuppiter]

Hello to everybody,

I need help in writing this code I need for my final Thesis at University.

I need to extract cells from incremental ranges, (B3:C263), (D3:E263), ... , (ID3:IE263) and paste it to another sheet.

The code I wrote (recorded) for the first extraction is:

 

Range("D2").Select

Windows("DATABASE.xlsx").Activate

Range("B3").Select

Range(Selection, Selection.End(xlDown)).Select

Range("B3:C263").Select

Selection.Copy

Windows("Calcolo implied vol OK.xlsm").Activate

Range("G2").Select

ActiveSheet.Paste

Windows("DATABASE.xlsx").Activate

ActiveWindow.SmallScroll Down:=-264

Range("B1:C1").Select

Application.CutCopyMode = False

Selection.Copy

Windows("Calcolo implied vol OK.xlsm").Activate

Range("D2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("D2").Select

Application.CutCopyMode = False

Selection.AutoFill Destination:=Range("D2:D259")

Range("D2:D259").Select

ActiveWindow.SmallScroll Down:=-192

 

Thanks in advance for your help, I appreciate.

 

Francesco

[Scruff]

whats the question ?

[Pashers]

Hi iuppiter,

 

due to it being a university project I won't feel right in telling you the 'answer' . Also I'm not sure I know what you mean. However, have you thought about using variables for example instead of using Range("B3").Select you would use a two global varibals like myCellInt and myCellStr. The int version will store the letter you are upto. Example:

 

declare globally:

Dim myCellStr As String

Dim myCellInt As Integer

 

Sub Macro1()

'Previous cell was b4. myCellInt was 66 (ascii for B)

myCellInt=myCellInt+1 'moving right one cell

myCellStr = Chr© & "4" '4 being the row number

'myCellStr will be now c4

Range(test).Select

End Sub

 

 

Does this help with what you wanted?

You'll obviously need to make some amendments. However, I hope this might be able to set you on your way

Edited September 8, 2008 by Pashers