PDA

View Full Version : Replace content of column



JohnBradley
08-31-2014, 11:42 AM
Hi,

Thanks if someone could help me with following, I am new in VBA -

1. VBA code for replacing some content of column of the excel cell with other content
(not find and replace, but replace the existing content of specific cells for example A1:A10 with something specific for example "my name")

2. VBA code - I have a column with numbers (B1:B10) and I want to multiple each cell by *1.32 and replace these numbers with existing (it's euro to dollar calculation).
I mean that instead of value numbers of euro will be now numbers of dollars.

Thanks!

John.

mancubus
08-31-2014, 01:09 PM
welcome to vbax.

try this. change worksheet name to suit.



With Worksheets("Sheet1")
.Range("A1:A10").Value = "My Name"
.Range("B1:B10").Value = Evaluate(Range("B1:B10").Address & "*1.32")
End With

SamT
08-31-2014, 02:36 PM
John,

Welcome to VBAX, IMO the best VBA site in the world.

I imagine that you would like to be able to run those Procedures on many workbooks. I also think that you want to be able to invoke them with Hot-Key shortcuts.

In order to use the procedures on many workbooks, they must be stored in the Personal.xls|xlsm workbook. I don't know what version of Excel you are using. The easiest way to do so, and create Personal.xls, if you haven't already, is to record a macro and tell the recorder to store it in the Personal Macro workbook.

Before you do this for the first time, decide what Hot-Key combo you want to use for, say, the Replace Procedure, (Example: Ctrl+M for MyName,) and the name of the Procedure, (For Example: "InsertMyName".) Do not use c,d,r,x,v, or z, as Hot-Keys, because those are the most often used shortcuts in excel.

Now, select a cell, set up the Macro recorder and start recording. Type something, press Enter, and stop the recorder. You now have a Procedure in the Personal workbook. To see the Macro, press F11 to open the VBA editor, thern press Ctrl+r to insure that the Project Explorer is visible. If VBAProject(Personal) is not expanded, click the "+" next to it. Expand the "Modules" folder and double click on "Module1" to see the macro you just recorded.

Note that comments in VBA code are preceded by an apostrophe and are green. Replace all the code (not the comments) inside the Sub with

Selection.Value = "MyName" That Sub Procedure is now ready for use in any workbook. Merely select some cells and press Ctrl+M.
While you'r in Module1, at the very top add the words "Option Explicit", then go to the Toolbar Menu Tools >>Options >>Editor and check everything in the "Code Settings" box. This is very helpful in finding errors and will put Option Explicit on all your code pages.

The currency conversion procedure is only a bit more thoughtful because you have to decide where to store the always changing conversion rate value. If you are going to use this Procedure on many workbooks (Code A), I suggest that you add a Module Level Constant just below the Option Explicit in Personal.Module1
Const Euro2USD_Rate as Double = 1.32

If this Procedure will only be used in one workbook (tell the Macro Recorder) you can use a Named Formula for the value. Go to the Toolbar Menu Insert >> Name >> Define and in "Names In Workbook" Type "Euro2USD_Rate". In the Refers To" Box type "=1.32", (Code B). You can also set up a Variables worksheet with a list of 1 or more variables, (euro2USD, PoundSterling2USD, USD2Euro, Etc) in one column, and the conversion rates in the next column, (Code C). Now merely select the names and rates and go to ToollBar Menu Insert >> Names >> Create.

Now, as before, record a macro, store it in Personal for many workbooks or in "This Workbook" for use only in this particular workbook. Replace all the code in the recorded Macro with:

Code A, for use in Personal with Module level Constant

Dim Cel As Range
For Each Cel in Selection
Cel.Value = Cel.Value * Euro2USD_Rate
Next Cel

Code B, for use in only one workbook with Named Formula

Dim Cel As Range
Dim RateFormula As String
Dim ConversionRate As Double

'convert formula String ("=123") to number (123)
RateFormula = ActiveWorkbook.Names("Euro2USD")
ConversionRate = CDbl(Right(RateFormula, Len(RateFormula) - 1))

For Each Cel In Selection
Cel.Value = Cel.Value * ConversionRate
Next Cel

Code C, For use in one Workbook with Named Range(s) on a worksheet

Dim Cel As Range

For Each Cel In Selection
Cel.Value = Cel.Value * Range("Euro2USD").Value
Next Cel

Each has its advantages and disadvantages.
Code A needs to have the conversion rate changed in the module, but it works on any workbook.
Code B Is longer and harder to understand, but you can change the Named conversion rate from any sheet in the workbook
Code C can be replicated for different currencies and all conversion rates can be maintained on one sheet, but again, only works for this one workbook.

In all three cases, select the cells then run the Macro.