using information in one cell to copy values in cells above to other cells

[jluensman]

Hello-

I am trying to use a macro to look at the text in one cell and copy values of other cells elsewhere based on that value.

In the example below, I would want the macro to look at column A and find the word "Price". Once it finds "Price" in A13, I would like to move A12 to J2, A11 to I2, A10 to H2, A9 to G2 and A8 to F2. Since Example 1 does not have "Price" anywhere in it, I would like it to be skipped.

 

[RobP]

Hi,

would something like this do what you need ?

Sub test()

Dim SrcRow, SrcRange As Variant
Dim x, op, lastrow As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
op = 2 'start row for output

For x = 3 To lastrow

    If Range("A" & x) = "Price" Then
    
        Set SrcRange = Range(Cells(x - 5, 1), Cells(x - 1, 1))
        SrcRow = SrcRange.Value
        Transposerow = Application.Transpose(SrcRow)
        Range(Cells(op, 6), Cells(op, 10)).Value = Transposerow
        op = op + 1
    End If

Next x


End Sub

cheers
Rob

 

[jluensman]

Hi,

would something like this do what you need ?

Sub test()

Dim SrcRow, SrcRange As Variant
Dim x, op, lastrow As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
op = 2 'start row for output

For x = 3 To lastrow

    If Range("A" & x) = "Price" Then
   
        Set SrcRange = Range(Cells(x - 5, 1), Cells(x - 1, 1))
        SrcRow = SrcRange.Value
        Transposerow = Application.Transpose(SrcRow)
        Range(Cells(op, 6), Cells(op, 10)).Value = Transposerow
        op = op + 1
    End If

Next x


End Sub

cheers
Rob

Hey Rob-

Yes this worked. Thank you.

 

[RobP]

thanks for feedback, glad we could help.

Rob