On Tue, 20 Mar 2007 12:40:15 -0700, Mudragaddam
wrote:

Sheet1

Servers Environment
devServer1 Dev
preprodServer1 PreProd
prodServer1 Prod
prodServer2 Prod

Sheet 2

Dev PreProd Prod

I need a code to evalate or compare the value from Environment column
(Sheet1) and append the server name under appropriate Column in Sheet2.

End Result for Sheet2 will be

Dev PreProd Prod
devServer1 preprodServer1 prodServer1
prodserver2

Try this:

Sub ListServers()

Dim rCell As Range
Dim rFound As Range

For Each rCell In Sheet1.Range("B2:B5").Cells
Set rFound = Sheet2.Rows(1).Find(rCell.Value, , xlValues, xlWhole)

If Not rFound Is Nothing Then 'header exists
Sheet2.Cells(Sheet2.Rows.Count,
rFound.Column).End(xlUp).Offset(1, 0).Value = _
rCell.Offset(0, -1).Value
Else 'header doesn't exist
With Sheet2.Cells(1,
Sheet2.Columns.Count).End(xlToLeft).Offset(0, 1)
.Value = rCell.Value
.Offset(1, 0).Value = rCell.Offset(0, -1).Value
End With
End If
Next rCell

End Sub

Make sure your sheet's are codenamed Sheet1 and Sheet2. Watch for word wrap
in this message.

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com