Formatting Cell in Excel with a Macro

[easbury]

I currently have a problem with formatting some cells.
Below I have posted some cells in excel. I need to remove the .1 at the end of each cell. The names in the cells will be different every day along with the amount of cells that the .1 will need to be removed from.
I'm new to programming so I don't know exactly how to conquer this or if it is even possible. I want to keep this in a macro because multiple users will be using it.

Thanks for the help!


<table width="64" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">80ppm.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">15ppm.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Blank.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">71220.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">71577.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">71825.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1274.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1274.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1029.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">277.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">637.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1660.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">80ppm.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">15ppm.1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Blank.1</td> </tr> </tbody></table>

 

[GK039]

Hi

If your pattern is always like that ie: removing ".1" from right then try this assuming that your range starts at A2:

Open your VBE and copy - paste

Sub RemoveDotOne()

Dim i As Long
Dim Lrow As Long

On Error GoTo errdescr

Worksheets("Sheet1").Range("a2").Select
Lrow = Selection.End(xlDown).Row

For i = 2 To Lrow
    If Right(Range("a" & i), 2) = ".1" Then
       Range("a" & i).Value = Left(Range("a" & i), Len(Range("a" & i)) - 2)
    End If
Next i

errdescr:
MsgBox Err.Description
End Sub

George

I currently have a problem with formatting some cells.
Below I have posted some cells in excel. I need to remove the .1 at the end of each cell. The names in the cells will be different every day along with the amount of cells that the .1 will need to be removed from.
I'm new to programming so I don't know exactly how to conquer this or if it is even possible. I want to keep this in a macro because multiple users will be using it.

Thanks for the help!


<table width="64" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">80ppm.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">15ppm.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Blank.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">71220.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">71577.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">71825.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1274.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1274.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1029.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">277.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">637.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1660.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">80ppm.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">15ppm.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Blank.1</td> </tr> </tbody></table>

 

[easbury]

The range would need to start at A12. I modified the code you gave me to start at A12 and it is not working. When I run the macro, I get an excel error of "Subscript out of Range." I'm not sure what to do about this. Any suggestions?

 

[GK039]

Change the For Next part from 2 to 12


ie:

For i = 12 To Lrow

George

The range would need to start at A12. I modified the code you gave me to start at A12 and it is not working. When I run the macro, I get an excel error of "Subscript out of Range." I'm not sure what to do about this. Any suggestions?

 

[easbury]

This is what I have and I keep getting an error of "subscript out of range."
I'm not sure what I am doing wrong here.


Code:
Sub RemoveDotOne()
Dim i As Long
Dim Lrow As Long
On Error GoTo errdescr
Worksheets("Sheet1").Range("a12").Select
Lrow = Selection.End(xlDown).Row
For i = 12 To Lrow
If Right(Range("a" & i), 2) = ".1" Then
Range("a" & i).Value = Left(Range("a" & i), Len(Range("a" & i)) - 2)
End If
Next i
errdescr:
MsgBox Err.Description
End Sub

 

[GK039]

Hi there,

The code is working fine when I run it. Can you debug it to go line by line to see where your code breaks and let us know? In VBE go to Debug and then Step Into and see what happens. Where your code breaks a yellow line highlighting the problem should appear.

George

This is what I have and I keep getting an error of "subscript out of range."
I'm not sure what I am doing wrong here.


Code:
Sub RemoveDotOne()
Dim i As Long
Dim Lrow As Long
On Error GoTo errdescr
Worksheets("Sheet1").Range("a12").Select
Lrow = Selection.End(xlDown).Row
For i = 12 To Lrow
If Right(Range("a" & i), 2) = ".1" Then
Range("a" & i).Value = Left(Range("a" & i), Len(Range("a" & i)) - 2)
End If
Next i
errdescr:
MsgBox Err.Description
End Sub

 

[easbury]

Highlights:

Sub Macro1()

On Error GoTo errdescr
Worksheets("Sheet1").Range("a2").Select

MsgBox Err.Description

 

[GK039]

What is the name of your first Sheet? If not "Sheet1" change accordingly. Have in mind that it has to be enclosed in "" ""

George

Highlights:

Sub Macro1()

On Error GoTo errdescr
Worksheets("Sheet1").Range("a2").Select

MsgBox Err.Description

 

[easbury]

That was it! I didn't notice that I had not put a space the first time.
I had "Sheet1" instead of "Sheet 1".

Thanks for all your help!!

 

[GK039]

Your welcome

George

That was it! I didn't notice that I had not put a space the first time.
I had "Sheet1" instead of "Sheet 1".

Thanks for all your help!!