98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Cell Formatting
Cell Formatting
Resolved · Urgent Priority · Version 2013
Mon 28 Nov 2016, 15:42Delegate Sean said...
Sean has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Cell Formatting
Any idea how to change an actual cell format in VBA? Or do Find/Replace Do Loops?
For example I have data in a main sheet, it is then sent to a separate sheet in a different layout(This is data to be sent to a labeling printer) Though sometimes we need to create labels for other countries.
It turns out Spain use a comma (,) as a decimal in their packaging. I cannot change the WHOLE spreadsheet into a (EU) Format but need a macro to turn JUST cells in 'sheet 2' into a format that replaces (.) with (,)
If I try put a comma into the main data sheet cells it messes with a lot of calculations so really need to just change the end product in 'sheet two'
could this work as a find/replace do loop? As the Decimal is not the only contents of the cell?
Mon 28 Nov 2016, 16:45Trainer Doug said...
RE: Cell Formatting
Hi Sean
Thanks for question.
Yes Replace will work to change "." to ","
It sounds like the figures are mixed with text. As you say, a Do Loop should work. The following replaces . with , in a block of non blank cells and stops when blan cell is selected.
Sub DecimalConvert()
Range("A2").Select
Do Until ActiveCell = ""
Selection.Replace What:=".", Replacement:=",", _ LookAt:=xlPart, SearchOrder:=xlByRows
ActiveCell.Offset(1, 0).Select
Loop
End Sub
My example converted
Amount
Amount 125.25
Amount 125.26
Amount 125.27
Amount 125.28
Amount 125.29
Amount 125.30
to
Amount
Amount 125,25
Amount 125,26
Amount 125,27
Amount 125,28
Amount 125,29
Amount 125,30
Hope that help for your labels.
Regards
Doug
STL
Tue 29 Nov 2016, 08:19Delegate Sean said...
RE: Cell Formatting
Thanks Doug,
My issue is I am trying to convert Rows. I have changed the offset to 0,1 and SearchOrder:=xlByColumn yet I'm getting Runtime Error 9 - Subscript out of range?
My Macro needs to run from A5 to the right until cell is blank.
Am I being blind and not seeing what is wrong here?
Code at current moment
Sub Foreign()
Range("A5").Select
Do Until ActiveCell = ""
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder:=xlBycolumn
ActiveCell.Offset(0, 1).Select
Loop
End Sub
Tue 29 Nov 2016, 11:07Trainer Doug said...
RE: Cell Formatting
Hi Sean
Just tested your macro. Change by xlBycolumn to xlByColumns.
Works fine!
Doug
Tue 29 Nov 2016, 11:37Delegate Sean said...
RE: Cell Formatting
Brilliant!
Final issue, Some of the cells within the database will not be filled. So the Do until activecell.value="" will not work.
There will never be more than 5 cells blank in one sheet. Is there a way to do a block of cells value as the terminating factor of a do loop?
I tried offsetting but that means that my Do loop will just end 5 cells prior to any blank cell.
ideally I need this to run until 5 consecutive cells are blank.
Tue 29 Nov 2016, 13:24Trainer Doug said...
RE: Cell Formatting
Hi Sean
There is a different way which doesn't depend on blank cells.
It needs the macro to highlight the range of cells first.
Alter Range("A5:N5") to suit your data.
Sub Foreign2()
Dim MyRange As Range
Range("A5:N5").Select
For Each MyRange In Selection
Selection.Replace What:=".", Replacement:=",",
LookAt:=xlPart, SearchOrder:=xlByColumns
Next MyRange
End Sub
This approach uses a range variable. It might work better for data containing blank cells.
Hope that helps.
Doug
Tue 6 Dec 2016: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Change the Value of a ConstantWhen using a named constant in a worksheet, you may wish to change the value of that constant. |
