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 ? |
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 ? |
All times are GMT +1. The time now is 06:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com