transpose loop

[balu]

dears,
im accessing excel through access by creating excel object and copyd some
access query to sheet1 columns A and B
now i want is like this WAY
COPY all the cell values of sheet1!columnB where sheet1!columnA.values =
sheet2!cellA1 next COPY all the cell values of sheet1!columnB where
sheet1!columnA.value = sheet2!cellA2 and so on until sheet2!columnA having
values
(2) then copyd cell values of the ranges from sheet1 will be transposed on
to sheet2 columnB rows
can any one help me please

 

[Bernie Deitrick]

balu,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub BaluTranspose()
Dim myR As Range
Dim myF As Range
Dim myC As Range
Dim myRow As Long

With Worksheets("Sheet1")
Set myR = Intersect(.Range("A:A"), .UsedRange)
End With
With Worksheets("Sheet2")
Set myF = Intersect(.Range("A:A"), .UsedRange)
For Each myC In myR
If Not IsError(Application.Match(myC.Value, myF, False)) Then
myRow = Application.WorksheetFunction.Match(myC.Value, myF, False)
.Cells(myRow, Columns.Count).End(xlToLeft)(1, 2).Value = myC(1, 2).Value
End If
Next myC
End With

End Sub