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?
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
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
RE: Cell Formatting
Hi Sean
Just tested your macro. Change by xlBycolumn to xlByColumns.
Works fine!
Doug
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.
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
