Replace values in a selected column with different values

**Mencae** · 12-03-2012, 02:56 PM

All,

I have a spreadsheet that has a column with 4 different values in it. I need to replace those values with different values. (It's replacing kilo values to the lbs values)
The values that are to be replaced are as follows:
50 should be 110
60 should be 132.28
69 should be 152.12
70 should be 154.32

I already have the macro I'm trying to create selecting the range (which will vary in size each month) using the following code, I'm just stuck on the REPLACE part of it (having a Brain Fart):

Sub WeightConversion()
Dim rngSalesUnit As Range
Set rngSalesUnit = Range("A1:Z1").Find("sales_unit")
If rngSalesUnit Is Nothing Then
MsgBox "Sales_Unit column was not found."
Exit Sub
End If
Range(rngSalesUnit, rngSalesUnit.End(xlDown)).Select
Replace
End Sub

Any suggestions would be great!!

Thank you in advance

**Jakobshavn** · 12-03-2012, 03:51 PM

How about:

Please Login or Register  to view this content.

After you have Selected the range to change.

**MarvinP** · 12-03-2012, 03:59 PM

Hi Mencae and welcome to the forum.

You don't need VBA code to do this process. All you need to do is use the Paste Special using a Multiply by 2.2.

Do this. Type 2.2 somewhere in a cell and copy it to the windows clipboard.

Highlight the cells you want to change and then click on Paste Special. In the Paste Special dialog click on Multiply and all those selected values will update to Kilo.

Or Read http://www.mrexcel.com/articles/past...-operation.php

**Mencae** · 12-04-2012, 04:28 PM

This is exactly what I thought of late last night while in bed. I just wish I had read your solution sooner to save me some sleep (LOL)

Works perfect!!

Thank you Jakobshavn!!!!!!