excel code problems 1

[roscok]

To anyone who can help.

In excel I have 13 columns of numbers from which I need to select a row at a time, copy that row to another location which gives a result. That result then needs to be copied (value only) to a location next to the original selection.

I then need to select the next row of numbers and do the same proceedure over until the end of the data in the columns.

Below is what I'm using to get by.

Range("B14:O14&quot.Select
Selection.Copy
Range("B10&quot.Select
ActiveSheet.Paste
Range("C12&quot.Select
Application.CutCopyMode = False
Selection.Copy
Range("R14&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:xlNone, SkipBlanks:=_
False, Transpose:=False

Range("B15:O15&quot.Select
Selection.Copy
Range("B10&quot.Select
ActiveSheet.Paste
Range("C12&quot.Select
Application.CutCopyMode = False
Selection.Copy
Range("R15&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:xlNone, SkipBlanks:=_
False, Transpose:=False


and so on until the end.

In the above the "B10" and "C12" are constant and the other selections increment by one row of 13 columns each time.

If anyone can help with some sort of loop and increment code I would greatly appreciate it.

thanks

RoscoK

 

[JGillespie]

Try this:

Sub TestLoop()

Dim MyRowCount, MyStartRow
MyStartRow = 14 ' change this to set first row to copy
Cells(MyStartRow, 2).Select
MyRowCount = Selection.CurrentRegion.Rows.Count
For i = 0 To MyRowCount - 1
Range(Cells(i + MyStartRow, 2), Cells(i + MyStartRow, 15)).Select
Selection.Copy
Range("B10&quot.Select
ActiveSheet.Paste
Range("C12&quot.Select
Application.CutCopyMode = False
Selection.Copy
Cells(i + MyStartRow, 16).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next i
End Sub

The variable MyStratRow can be set to the first row you wish to copy (your example started with row 14). This code will loop through to the end of your data using the CurrentRegion property so as long as there are no empty rows in your data it will keep going to the end of your data table.

Hope it helps!! Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[mymou]

Hi roscok

I would be careful about overcomplicating the issue.

If the data resides elsewhere (another sheet etc) then why not just use references to those locations?

Just trying to help.

Stew "Even a stopped clock tells the right time twice a day."