loop error

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

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 ?
 
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
 
Back
Top