Free Microsoft Excel 2013 Quick Reference

Replace dot with comma with macro

I've created a small macro to replace a dot with a comma sign, but it doesn't work in all the cells I want to make the replacement. See attached file.

When pressing the button, the macro is supposed to replace the dot to a comma sign in the selected cell. In cells A3 and A4 it works, but in cells A1 and A2 the comma sign is placed after the decimals. I've tried to switch cell format between General and Number and also the number of decimals when using the Number format, but can't see any difference.


Hi,

I have a problem and maybe somebody can give me some good advice.

The problem is that I need to replace delimiter dots with commas, otherwise numbers will be handeled as text. When I simply use Replace function from Edit menu, it work perfectly - dots will be replaced with commas and text will be converted to numbers.

I tried to record this function as a macro, but it doesn't work in macros. It replaces dots with commas, but text won't be converted to numbers and I can't use them in functions as SUM etc. Its strange because it works when done manually but doesn't work in recorded macro.

How can I fix that?

Thank you!

Does anyone know an easy way of converting numbers with thousands separated
by dots to commas. One can run a replace macro which is fine but i have some
numbers such as 123.45 which actually means 123,450.00!! and it does not work
with this properly.

I know i could write some code say using instr but is there a simpler way?

Grateful for any advices, probably not using my brain and a basic answer!!
--
with kind regards

Spike

Hi.

I would like to create a macro witch can detect dots in a textbox value and replace them by commas. I intend this because this is an issue that can only be overcomed by changing the Regional and Language Properties in the Control Panel, by definig the dot as the decimal mark,but i don't want to do this, because i would have to do that in every computer that this file is runed...

So, does anyone know how to do this?

Thank you!

I get a report several times a week consisting of several columns and some hundred rows. There must be no comma signs in column E but the reports that I get will sometimes have commas in Col E anyway.

I have a macro/vba code in another workbook that I start by a keyboard combination. This macro will adapt the look of the report, but it can't take care of the comma issue. However, I have managed to remove the comma and replace it with nothing but that is not sufficient. I want to delete the comma and all figures to the right of the comma sign. There can between 1 and 4 decimal numbers.

I need to integrate some kind of vba code that will check every cell in column E and if it finds a comma in any cell, the comma must be deleted and all the numbers to the right of the comma too.

Hope you can help me with this issue because I have been googling around for two days now... It drives me crazy.

Hi there

I have some numbers that I reformat from eg 1,000,000.00 into 1.000.000,00 or 100.00 into 100,00

now I need to take the thousand separator from the resulting only changing the . into a ,


	VB:
	
 'with a comma.
Private Function ReformattedNumber(ByVal Num As Double) As String 
     
    If InStr(1, Num, ".") > 0 Then 
         
         
        ReformattedNumber = Replace(Num, ".", ",") 
         
         
         
    Else 
         
        ReformattedNumber = Num & "," 
         
    End If 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Auto Merged Post Until 24 Hrs Passes;

sorry being stupid & overcomplicating it.. all it needs it seems is>

	VB:
	
ReformattedNumber = Num & "," 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi everyone,

I am having problems applying custom format to numbers that need to be formatted.

custom format use is 0",00"

When I apply the custom format above to whole numbers, I achieve the results I want:

0.00
20.00
40.00

becomes

0,00
20,00
40,00

However, when I apply to numbers with decimal places

0.20
0.40
0.60

becomes

0,00
0,00
1,00

anyone knows how I can change the custom format so that the period is replaced with a comma but the decimal values are preserved?

appreciate your help, thanks!

OK here it goes, I am using a data aquisition system to collect data in Excel. One of the columns i am collecting is temperature. Once I have the data, Each temperature corresponds to a reaction rate. I need a macro that will copy the temperature column and then replace the temp value with the corresponding reaction rate value. I tried one where i simply record the macro with the replace function but this only works if all of the values are there, for example if in the macro i am trying to replace 24 with 56 and in the data that was collected there is no 24 then the macro bugs out. This is a problem because the data that is being collected can change from time to time. I hope i explained this ok, thanks, dan.

Hi,

I have the following code which is save Sheet as a text file. I know there are some options in Excel for saving, but it is not saving as ".txt" file.

I would like to modify this code to replace blank space with comma between Cells.

PHP Code:


Private Sub CommandButton1_Click()

Application.DisplayAlerts = False

' Save file name and path into a variable
    template_file = ActiveWorkbook.FullName
 
' Default directory would be c:temp.  Users however will have the ability to change where to save the file if need be.
' Notice that i'm only allowing the save as option to be of .txt format.
' I'm also attaching the current date to the file name.
    fileSaveName = Application.GetSaveAsFilename( _
    InitialFileName:="C:tempfilename_" + VBA.Strings.Format(Now, "mmddyyyy") + ".txt", _
    fileFilter:="Text Files (*.txt), *.txt")
    
    If fileSaveName = False Then
        Exit Sub
    End If

' Save file as .txt TAB delimited
    ActiveWorkbook.SaveAs Filename:= _
        fileSaveName, FileFormat:=xlText, _
        CreateBackup:=False

    
    file_name_saved = ActiveWorkbook.FullName
    MsgBox "Your Acct Rec upload file has been successfully created at: " & vbCr & vbCr & file_name_saved 
    
    
' Go back to excel format after TAB delimited file has been created and saved
    ActiveWorkbook.SaveAs Filename:= _
        template_file, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False

     Application.DisplayAlerts = True

End Sub 
Any one can help Please.

Regards

I need help replacing a number with text using a Macro.

For example: I need to replace numbers in Column B with text

Replace 212 with "Sike Branch Manager"
Replace 154 with "So County Manager"
Replace 188 with "Bridge Manager"

Any help would be greatly appreciated.

hello

I need a macro for excel through which i could replace the specific alphabet with numbers in a range of cell. Details are as under:

Range B1:H50

Replace "A+" with 10
Replace "A" with 9
Replace "B+" with 8
Replace "B" with 7
Replace "C+" with 6
Replace "C" with 5
Replace "D+" with 4
Replace "D" with 3
Replace "F" with 0

Please help

I have an excel spreadsheet where I'm using macros to find dept numbers and replace them with macro names. The macro works well until it can't find a department number. Then I get the error message "Run Time Error 91"
"object variable or with block not set". How can I get the macro to go find the next department number when it doesn't finds that the current one doesn't exist?
A portion of the macro is shown below.

Sub Convert_dept_Nos()
'
' Convert_dept_Nos Macro
' Macro recorded 12/14/2006 by Guilford County
'

'
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SPREADSHEET WITH DEPT NAMES").Select
Columns("B:B").Select
Selection.Find(What:="101", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="101", Replacement:="COUNTY COMMISSIONERS", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Find(What:="102", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

Plz help me to replace this
formula with a macro
=IF(A1="","",IF(ISNA(VLOOKUP
(A1,Sheet2!
A:B,2,0)),A1,(VLOOKUP
(A1,SHEET2!A:B,2,0))

I need help replacing a number with text using a Macro.

For example: I need to replace numbers in Column B with text

Replace 212 with "Sike Branch Manager"
Replace 154 with "So County Manager"
Replace 188 with "Bridge Manager"

Any help would be greatly appreciated.

Hi there,

I'm relatively to MS Excel 2003 macros.

what I would like to do is.....run a macro on a column of 9-digit numbers and turn them into letters according to a specific matching code, which is very easy to understand:

I would like 9 = A, 8 = B, 7=c, 6 =d, etc.

I'm not sure how to go about doing this with a macro and VBA. What should the code look like? Will using replace function in VBA be sufficient?

could anybody give me a start?

Thank you very much. all comments much appreciated.

Hi,

I'm trying to become more efficient and avoid loops where I can.

I deal with sheets of various sizes, and my macro just counts the number of rows (which are usually 20,000+) by using COUNTA in the index column, and loops that many times.... replacing blanks with "."

Is there a better way to do this... by using the COUNTA command to tell excel the range of cells with data in it?

Your help would be much appreciated.

Hi folks,

Excel (and VBA) newbie here. I'm building a scorecard that shows status as Green, Yellow, or Red (the actual text is G, Y, R). What I'd like to do, but don't know if it's possible, is replace "G" with a green dot, "Y" with a yellow dot, and "R" with a red dot.

For example, Overall Status might be Y (so show a Yellow dot), but Budget and Availability status might be G (so show a Green dot in these columns).

Is this possible? I've been using conditional formatting to highlight the entries in these columns, but alas the powers that be want to have the eye candy. I'm using Excel 2003, and I have NO idea how to work VBA stuff...so simple answers are most apprectiated. Also note - we're not allowed to download software here... so any external tools that might be useful are a no-go.

Thanks,
Mike

Hi, I created a dynamic command button now I want to name the button from a variable. If the variable has spaces in how can I replace them with underscore.

Thank You

This is the code I used to create the button

Private Sub Create_Menu_Buttton_Click()

' Create object variable.
Dim VariableSheetName As String
Dim oleButton As OLEObject

VariableSheetName = InputBox("Enter name for new sheet to create.", "ENTER NEW PROJECT NAME", "")

'Insert rows to make space for new button
Rows("6:7").Select
Selection.Insert Shift:=xlDown

'Add the button to the sheet
Set oleButton = ActiveSheet.OLEObjects.Add _
(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=9, Top:=76.5, Width:=141, Height _
:=20)

'Rename the button, so that the code added later refers to it
oleButton.Name = VariableSheetName
oleButton.Object.Caption = VariableSheetName

End Sub

Hi, I am really stuck on this one.

I have a spreadsheet with a long list of data. Column C contains a date, Column K contains a different date.
The problem is that some dates in column K show as 00/00/0000 (as in 'no date available'), which causes my formulas on that sheet to return no value.

What I would like to do is for a macro to go through the sheet, check in column K for the value 00/00/0000 and replace it with the corresponding value in cell C of that row.

Any ideas on this?

Your help is much appreciated

I really hope to have some help with this as i am very basic at VBA i can run them but not create :P!

Hi Excelers.

Maybe I will not be the best to explain but please let me know if you need further explanation.

I have different workbooks (around 20) and they give the information about different products for the branches, so in all those workbooks the code and the name of the branch and the info starts at B8:B52 and goes on on F8:F52 so for each branch the starting cell number is 8..now I want to do a summary for each branch with those information on the 20 workbooks. I do the first branch that starts at 8 so i go =workbookx, d8 and so on and then copy this one to the other sheet, and as there the common value is 8, i go CTRL F, find 8 and replace with 9 but at the same time keep the 20 workbooks open and it does replace all the values so make the changes for the righ branch that starts at 9...and i go on with the same paste the first one so with value 8, and i go replce with 10 and so on....this is an easy way that I did found but i would like to do it with a macro as it will not be a boring job and will same me a lot of time and to be honest i love what macro do .

but what i would like is that if i do the first sheet ready with the info, i want the macro do save the next sheet with the code of the branch, where it will copy it let's say B8 and the name at the top of it the branch name.

Please, if you need a sample i can attach it.

P.S I did search in the internet about it and i looked at around 20 links but still, but i can't find what i look for.

Thank you a lot.

Hi,

I'm working with a very large spreadsheet (about 10mb), with lots of formulas in it. The workbook has started freezing when I try to make changes to any of the data or formulas, I think because it's too much to crunch through.

To reduce the size of the spreadsheet, I'd like to replace these formulas with macros that spit out the *result* of the formula for each cell, so that I'll no longer need 10,000 iterations of the same formula (for each row)

For example, in Column A, each cell has the formula:

	VB:
	
=vlookup(H#,rates,3,) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
...the only thing that changes as you go down the column is the Row #.

I'd like to replace that with a macro that I can run once and, instead of having a formula in each cell, will produce a value in each cell.

Any advice?
Best Regards,

Vaughn

I need to find and replace text in certain cells an Excel sheet but with conditions:

For example:
If Cell in Column A="House" and Cell in Column B="Red" then rename Cell in Column B="Dark Red"

Do I do this with a macro? Please help. Thanks.

I'm posting the complete macro code in a hope somebody can spot
something that may be wrong. For those of you that have not been
keeping track of my previous post over the last few days; I am copying
2 sheets from one file into a new file and then removing all the
formulae by replacing them with the cell value. I am doing this to
remove links that I have present. The problem I have though is that it
errors out during the last usedrange.formula=usedrange.value. If I
have copied one sheet it fails on that one. If I have copied 2 sheets,
then it does the first one but fails on the second!. The Runtime error
that pops up happens on the last run through of the '*'d line with the
error message.....
Mehtod 'Formula' of object 'Range' failed
.......Here's my code....

Option Explicit

Sub Actual1()

Dim FName As String
Dim i As Integer
Dim s, w
ReDim MyResults(1 To 100)
Dim iArea As Range

''''''''''''''''''''''''''
' Selects The Chart File '
''''''''''''''''''''''''''
For Each w In Workbooks
If InStr(w.Name, "Charts") Then
FName = w.Name
Exit For
End If
Next w

If FName = "" Then
MsgBox ("You Need A Chart File Open.")
GoTo End1:
Else
Workbooks(FName).Activate
End If

''''''''''''''''''''''''''''''''
' These Are The Sheets To Copy '
''''''''''''''''''''''''''''''''
MyResults(1) = "A3RH"
MyResults(2) = "C6LH"
ReDim Preserve MyResults(1 To 2)

Workbooks(FName).Activate
Sheets(MyResults(UBound(MyResults))).Activate
Sheets(MyResults).Copy
Worksheets.Add after:=Worksheets(Worksheets.Count)

ChDrive "I"
ChDir "I:DataTempCopy Chart"
ActiveWorkbook.SaveAs Filename:="Copy Chart.xls"
Application.CutCopyMode = False

''''''''''''''''''''''''''''''''''''''''
' Removes All Formulae And Hence Links '
''''''''''''''''''''''''''''''''''''''''
For Each s In ActiveWorkbook.Sheets
s.Activate
s.Unprotect
Cells.Select
Selection.MergeCells = False
Columns("AZ").ColumnWidth = 17.75
Range("AX1").Select
s.UsedRange.Formula = s.UsedRange.Value
s.Protect
Next s

End1:
End Sub

.......Any ideas? And thanks to those guys that have kept posting to my
previous thread over the past few days.

Hi all.

I have over 1500 rows of this type of information

0A GNT Amber Air (Lithuania)

what I need to do is replace the 1st space after 0A and the second space after GNT with comma's. Now each line is different but they all follow the same rule, 2 letters then a space then 3 letters then a space.

Can any one help?

Thanks
Chris

Save a Cells Contents in MS Word with a Macro in a Loop

Dear Forum,

I had about a 2000 Email-Ids in Excel stored cellwise one below the other in a single column..

With my recent request I got these emails ids appended with commas..

However its still tedious to copy the cells contents from a single excel in the Text Box of an Email Account..

So it will be easier if I could just loop this entire activity and store these email ids as a chunk of 20 Email Ids in MS word so that when I start sending these emails its easier to copy it from MS WOrd than Excel's single cell.

I need to have these email ids copied into a Word File one below the other with 2 blank line spaces in it to differentiate between 2 any two chunks..

Warm Regards
e4excel