1

So, I have a bunch of columns that need to be replaced with a letter, this is old signed overpunch stuff. So basically what i want to do is replace the letters with numbers and then multiply it by 0.02 for each cell.

However I want to make it so I can specify I range and then output this new information in a new work sheet.

So basically, I'll have a column like 1. 0000012C = 00000123 x 0.02 = 2.46 2. 0002927B = 29272 x 0.02 = 585.44

Private Sub CommandButton1_Click()
Dim OriginalText As String
Dim CorrectedText As String

OriginalText = Range("A1:D15").Value

CorrectedText = Replace(OriginalText, "A", "1")
CorrectedText = Replace(OriginalText, "B", "2")
CorrectedText = Replace(OriginalText, "C", "3")
CorrectedText = Replace(OriginalText, "D", "4")
CorrectedText = Replace(OriginalText, "E", "5")
CorrectedText = Replace(OriginalText, "F", "6")
CorrectedText = Replace(OriginalText, "G", "7")
CorrectedText = Replace(OriginalText, "H", "8")
CorrectedText = Replace(OriginalText, "I", "9")
CorrectedText = Replace(OriginalText, "{", "0")
CorrectedText = Replace(OriginalText, "}", "-0")

Worksheets("Sheet1").Range("F1:I15").Value = CorrectedText
End Sub

This is what I have so far, but I don't think I'm doing this correctly, could any one with more vb experience in excel take a look.

Improve this question

3 Answers 3

Reset to default
3

A couple of things.

  1. You will want to loop through each cell individually with a For Each loop.

  2. There is no need of correctedtext. Just maintain the value in original text. The way you had it would replace the correctedtext with each new line so that the only one that would show would be the last, by using the originaltext only it saves the changes on each replace.

  3. Use offset to place the value into the correct cell.

    Dim OriginalText As String
Dim cell As Range
Dim aws As Worksheet
Dim dws As Worksheet

Set aws = ActiveSheet
Set dws = ActiveWorkbook.Sheets("Sheet1")
For Each cel In aws.Range("A1:D15")
    OriginalText = cel.value
    OriginalText = Replace(OriginalText, "A", "1")
    OriginalText = Replace(OriginalText, "B", "2")
    OriginalText = Replace(OriginalText, "C", "3")
    OriginalText = Replace(OriginalText, "D", "4")
    OriginalText = Replace(OriginalText, "E", "5")
    OriginalText = Replace(OriginalText, "F", "6")
    OriginalText = Replace(OriginalText, "G", "7")
    OriginalText = Replace(OriginalText, "H", "8")
    OriginalText = Replace(OriginalText, "I", "9")
    OriginalText = Replace(OriginalText, "{", "0")
    OriginalText = Replace(OriginalText, "}", "-0")
    dws.Range(cel.Address).Offset(, 5) = OriginalText
Next cel

If you want to do the multiplication of each number when you place it in the new cell, change this line:

        dws.Range(cel.Address).Offset(, 5) = OriginalText

to:

        dws.Range(cel.Address).Offset(, 5) = OriginalText * .01
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Oh wow, Cool, How does the cel.offset work? what does the 5 represent? As well, How would I multiply the output by 0.01?
@SaadA I missed where you wanted it on a different sheet, I have edited the answer. Offset takes the cell and moves it to a different cell. The method is this: Offset(rows, columns). Since rows are 0 it can be skipped for shorthand. Same with columns, if columns were 0 it would be.Offset(1) meanig one row down in the same column.
Thanks Scott!, I was banging my head against the wall for a good day trying to figure it out my self!
@SaadA Glad I could help, remember to mark the answer as correct.
0

Saad,

Below code will work for you.

Private Function DoReplace(Text As String) As String
    Dim ReplacedValue As String
    ReplacedValue = Text

    ReplacedValue = Replace(ReplacedValue, "A", "1")
    ReplacedValue = Replace(ReplacedValue, "B", "2")
    ReplacedValue = Replace(ReplacedValue, "C", "3")
    ReplacedValue = Replace(ReplacedValue, "D", "4")
    ReplacedValue = Replace(ReplacedValue, "E", "5")
    ReplacedValue = Replace(ReplacedValue, "F", "6")
    ReplacedValue = Replace(ReplacedValue, "G", "7")
    ReplacedValue = Replace(ReplacedValue, "H", "8")
    ReplacedValue = Replace(ReplacedValue, "I", "9")
    ReplacedValue = Replace(ReplacedValue, "{", "0")
    ReplacedValue = Replace(ReplacedValue, "}", "-0")

    DoReplace = ReplacedValue
End Function

Private Sub CommandButton1_Click()
    Dim Text As String, CalculatedValue As Double

    For Each cell In Worksheets("Sheet1").Range("A1:D15").Cells
        If cell.Value <> "" Then
            Text = DoReplace(cell.Value)
            CalculatedValue = Val(Text) * 0.02
            Worksheets("Sheet2").Cells(cell.Row, cell.Column).Value = CalculatedValue
        End If
    Next

End Sub

The above code will perform all replaces and calculations, and place the final output in Sheet2 same row same column.

Improve this answer

2 Comments

Thank you Shalvin, I will also try this out too!
@SaadA I see in this post that you ended up using this method. You should change the correct answer and give credit where it is due.
0

This version performs the replacements on the entire range:

Option Explicit

Private Sub CommandButton1_Click()

    replaceLetters Worksheets("Sheet1").Range("A1:D15")

End Sub

Private Sub replaceLetters(ByRef rng As Range, Optional ByVal offsetCol As Long = 1)

    Const RELACEMENTS   As String = "A1 B2 C3 D4 E5 F6 G7 H8 I9 {0 }-0"
    Const TIMES         As String = " x 0.02"

    Dim newRng As Range, replVals As Variant, rv As Variant

    replVals = Split(RELACEMENTS)

    Application.ScreenUpdating = False

    Set newRng = rng.Offset(0, rng.Column + rng.Columns.Count + (offsetCol - 1))

    newRng.Value2 = rng.Value2  'copy values to the offset range

    For Each rv In replVals
        With newRng
            .Replace What:=Left(rv, 1), Replacement:=Right(rv, Len(rv) - 1) & TIMES, _
                     LookAt:=xlPart
        End With
    Next

    newRng.EntireColumn.AutoFit

    Application.ScreenUpdating = True
End Sub
Improve this answer

1 Comment

So it would automatically replace what is inbetween A1 and D15, I guess that would be better than putting it in a new worksheet. Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.