On Jan 30, 1:49*pm, "Per Jessen" <per.jes...@mail.dk> wrote:
> "shriil" <sanjib.lah...@gmail.com> skrev i en meddelelsenews:68f9af92-5bc5-4bf1-86a5-(E-Mail Removed)...
>
>
>
>
>
> > Hi
>
> > I would like to write a procedure in the VB Editor such that the
> > following operation
> > (detailed below) can be done at one go.
>
> > I have data in each of 12 columns of 20 Consecutive Rows. Ex. From
> > Rows 23 to 42, Columns D to O
> > Additionally I have data in Row 7, Column D to O.
>
> > Now I have to Copy each of the 20 Rows, one at a time, and paste it
> > in
> > Row 8, Column D to O. The First operation occurs when Row 23 is
> > selected, copied and pasted on Row 8. Based upon the data pasted in
> > Row 8 along with that already resident in Row 7, a calculated value
> > is
> > generated in Row 8, Column P (P8). The value in Cell (P8) is copied
> > and pasted in Row 9 Column A,i.e., Cell (A9).
>
> > In the second operation, Row 24 is selected, copied and pasted on Row
> > 8. Based upon the changed data in Row 8, along with that resident in
> > Row 7, a calculated value is again generated in Row 8 Column P (P8).
> > Subsequently the new value in Cell (P8) is copied and pasted in Row 9
> > Column B,i.e., Cell (B9).
>
> > So for each of the 20 Rows pasted at the same location of Row 8, one
> > by one,
> > I shall generate 20 calculated values that would be available from
> > (A9)
> > to (T9). *Depending upon the highest value among these cells, the Row
> > that was responsible for the same (i.e. one of the Rows between Rows
> > 23 to 42), shall be permanently copied and pasted in Row 8 location.
>
> > I hope I have been able to explain it clearly. What I understand that
> > I have to write the code with a few Do.. While statements, but cannot
> > fathom how to denote which particular Row to be copied and pasted and
> > how to increment by one row at a time for the "Copy & Paste"
> > operation.
>
> > Pls Help
>
> Hi
>
> Try this.
>
> Sub test()
> Dim tRange As Range
> Dim r, c
> Dim testval As Long
> Dim ColOff As Long
> Const DestRange As String = "D8"
> Dim counter As Single
> Set tRange = Range("D23:O42")
>
> For Each r In tRange.Rows
> * * r.Copy Destination:=Range(DestRange)
> * * Range("A9").Offset(0, counter) = Range("P8").Value
> * * counter = counter + 1
> Next
>
> counter = 0
> For Each c In Range("A9:T9")
> * * If c.Value > testval Then
> * * * * testval = c.Value
> * * * * ColOff = counter
> * * End If
> * * counter = counter + 1
> Next
> Range("D23:O23").Offset(ColOff - 1, 0).Copy Destination:=Range(DestRange)
> End Sub
>
> Regards,
>
> Per- Hide quoted text -
>
> - Show quoted text -
Hi Per
Thanks a Ton. Your code worked perfectly ok. only the last bit,
i.e. ....
For Each c In Range("A9:T9")
> If c.Value > testval Then
> testval = c.Value
> ColOff = counter
> End If
> counter = counter + 1
> Next
> Range("D23:O23").Offset(ColOff - 1, 0).Copy Destination:=Range(DestRange)
..... i guess didnt work. Because finally depending upon the highest
value in A9:T9, the particular Row in the Range (D23:O42) gets copied
to Destination Range "D8". Am i right in understanding your code?..
But the final copying did not take place.
Could you please explain the above part of the code?
What is the initial value of 'testval'. Is that the highest value in
the Range A9:T9?
Thanks again for putting me on track
|
Similar Threads
