Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop error
hi there
in sheet 1 column A i have a list of numbers in sheet 2 it gives me a matrix using the foll. code Sub test() Dim j As Integer Dim i As Integer Dim x As Integer Dim rng1 As Range Dim rng2 As Range Sheets("Sheet2").Select Range("A1:Z500").Select Selection.ClearContents Set rng1 = Worksheets("Sheet1").Range("A1") Set rng2 = Worksheets("Sheet2").Range("A1") x = rng1.CurrentRegion.Rows.Count - 1 For j = 0 To x For i = 0 To x rng2.Offset(j, i) = rng1.Offset(j, 0) Next i Next j For j = 0 To x rng2.Offset(j, j) = rng1.Offset(j, 0) + 0.001 Next j End Sub ---------- now in sheet 3 using data and lots of formulas i get results in row A now what i wud like to do is ..... first .....get column A values from sheet 2 and paste them in sheet 1 column A ...and get the result from sheet 3...........and store it in sheet 4... then get column B values from sheet 2 and paste them in sheet 1 column A ...and get the result from sheet 3...........and store it in sheet 4... get column C values from sheet 2 and paste them in sheet 1 column A ....and get the result from sheet 3...........and store it in sheet 4... get column D values from sheet 2 and paste them in sheet 1 column A ....and get the result from sheet 3...........and store it in sheet 4... get column E values from sheet 2 and paste them in sheet 1 column A ....and get the result from sheet 3...........and store it in sheet 4... and at last rset the original value of sheet 1 column A ..... AND SO ON .... i m trying add a loop which will do this in the above code..... but am stuck up with the above prob .. can some one help ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop error
You have't furnished any details about sheet3, so this is the best I can
offer. Should give you some ideas. Sub test() Dim j As Integer Dim i As Integer Dim x As Integer Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim rng4 As Range Dim rng5 As Range Dim varr As Variant Sheets("Sheet2").Select Range("A1:Z500").Select Selection.ClearContents Set rng1 = Worksheets("Sheet1").Range("A1") Set rng2 = Worksheets("Sheet2").Range("A1") varr = rng1.CurrentRegion.Columns(1).Cells.Value For e = 0 To 5 x = rng1.CurrentRegion.Rows.Count - 1 For j = 0 To x For i = 0 To x rng2.Offset(j, i) = rng1.Offset(j, 0) Next i Next j For j = 0 To x rng2.Offset(j, j) = rng1.Offset(j, 0) + 0.001 Next j If e 0 Then With rng2.Parent Set rng3 = .Range(.Cells(1, e), _ .Cells(1, e).End(xlDown)) End With rng3.Copy Destination:=rng1 Set rng4 = Worksheets("sheet3") _ .UsedRange Set rng5 = Worksheets("Sheet4") _ .Cells(Rows.Count, 1).End(xlUp)(2) rng4.Copy rng5.PasteSpecial xlValues End If Next e rng1.Resize(UBound(varr, 1) - _ LBound(varr, 1) + 1, 1).Value = varr End Sub -- Regards, Tom Ogilvy Sam wrote in message om... hi there in sheet 1 column A i have a list of numbers in sheet 2 it gives me a matrix using the foll. code Sub test() Dim j As Integer Dim i As Integer Dim x As Integer Dim rng1 As Range Dim rng2 As Range Sheets("Sheet2").Select Range("A1:Z500").Select Selection.ClearContents Set rng1 = Worksheets("Sheet1").Range("A1") Set rng2 = Worksheets("Sheet2").Range("A1") x = rng1.CurrentRegion.Rows.Count - 1 For j = 0 To x For i = 0 To x rng2.Offset(j, i) = rng1.Offset(j, 0) Next i Next j For j = 0 To x rng2.Offset(j, j) = rng1.Offset(j, 0) + 0.001 Next j End Sub ---------- now in sheet 3 using data and lots of formulas i get results in row A now what i wud like to do is ..... first .....get column A values from sheet 2 and paste them in sheet 1 column A ...and get the result from sheet 3...........and store it in sheet 4... then get column B values from sheet 2 and paste them in sheet 1 column A ...and get the result from sheet 3...........and store it in sheet 4... get column C values from sheet 2 and paste them in sheet 1 column A ...and get the result from sheet 3...........and store it in sheet 4... get column D values from sheet 2 and paste them in sheet 1 column A ...and get the result from sheet 3...........and store it in sheet 4... get column E values from sheet 2 and paste them in sheet 1 column A ...and get the result from sheet 3...........and store it in sheet 4... and at last rset the original value of sheet 1 column A ..... AND SO ON .... i m trying add a loop which will do this in the above code..... but am stuck up with the above prob .. can some one help ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 in loop | Excel Discussion (Misc queries) | |||
stuck in an error loop | Excel Discussion (Misc queries) | |||
loop code error help!!!! | Excel Discussion (Misc queries) | |||
Error on second pass of loop. | Excel Discussion (Misc queries) | |||
error on second pass of loop. | Excel Discussion (Misc queries) |