Replace value in cell with value from a table/array?

[43fan]

I have a report that's exported to Excel. I want to take that report, copy
it into another sheet, change the formatting a bit, then... run a macro or
program to take the values in certain cells, look them up in an array I've
created on another tab, and replace the value with the corresponding one in
the array.

I can use vlookup to put the value in another cell, but I'm hoping to just
be able to replace the value in the existing cell.

I know I have to use VB to do this, but I'm not sure how. Also, I don't
know how many records will be pulled in each time, so it needs to loop
through until it reaches the end of a column.

Help????

Thanks!!
Shawn

 

[Joel]

Sub Format_Report()

With Sheets("Sheet1")
.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "NewSheet"
End With
With Sheets("NewSheet")
RowCount = 1
Do While .Range("C" & RowCount) <> ""
Data = .Range("C" & RowCount)
With Sheets("LookupSheet")
Set c = .Columns("D").Find(what:=Data, _
LookIn:=xlValues)
If Not c Is Nothing Then
Newdata = c.Offset(rowoffset:=0, columnoffset:=1)
Sheets("NewSheet").Range("C" & RowCount) = Newdata
End If
End With

RowCount = RowCount + 1
Loop
End With