Replace function and negative values

Question

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.01 for each cell.

So basically, I'll have a column like 1. 0000012C = 00000123 x 0.01 = 2.46 2. 0002927B = 29272 x 0.01 = 585.44 3. 0000245N = -00002455 x 0.01 = -24.55

So the problem I cant figure out is how to make the value in a cell negative. As you can see the values for letters (JKLMNOPQR) are negative, however during the replacement step, excel doesnt know how to make the value negative.

I was wondering is there a step I can add where I can turn the values that are JKLMNOPQR negative first and then continue you with the replace function and the multiplication function. I was thinking an it would search cel for JKLMNOPQR and if it had JKLMNOPQR in the cell it would add a negative value in front of the cell and continue you on.

Thanks in advanced, sorry I am new to this.

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")
ReplacedValue = Replace(ReplacedValue, "J", "-1")
ReplacedValue = Replace(ReplacedValue, "K", "-2")
ReplacedValue = Replace(ReplacedValue, "L", "-3")
ReplacedValue = Replace(ReplacedValue, "M", "-4")
ReplacedValue = Replace(ReplacedValue, "N", "-5")
ReplacedValue = Replace(ReplacedValue, "O", "-6")
ReplacedValue = Replace(ReplacedValue, "P", "-7")
ReplacedValue = Replace(ReplacedValue, "Q", "-8")
ReplacedValue = Replace(ReplacedValue, "R", "-9")



DoReplace = ReplacedValue
End Function




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

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

Bas Verlaat · Accepted Answer · 2015-10-02 20:39:35Z

0

You can use instr() to first check if the character is present. If so, then then it puts the minus sign in front of the value.

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")

If InStr(ReplacedValue, "}") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "}", "0")
End If
If InStr(ReplacedValue, "J") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "J", "1")
End If
If InStr(ReplacedValue, "K") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "K", "2")
End If
If InStr(ReplacedValue, "L") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "L", "3")
End If
If InStr(ReplacedValue, "M") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "M", "4")
End If
If InStr(ReplacedValue, "N") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "N", "5")
End If
If InStr(ReplacedValue, "O") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "O", "6")
End If
If InStr(ReplacedValue, "P") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "P", "7")
End If
If InStr(ReplacedValue, "Q") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "Q", "8")
End If
If InStr(ReplacedValue, "R") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "R", "9")
End If

answered Oct 2, 2015 at 20:39

Bas Verlaat

8526 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Saad A Over a year ago

That worked, I have never seen of hte InSTR command before, thank you

Bas Verlaat Over a year ago

You're welcome. It return the position of the character

Scott Craner · Accepted Answer · 2015-10-03 18:47:18Z

YOu can use a function like this:

Function negt(celstr As String) As Boolean
Dim temp As Boolean
Dim charArr As Variant
Dim charStr As String
temp = False

charArr = Array("J", "K", "L", "M", "N", "O", "P", "Q", "R", "}") '// add as required

'// Create string representation of array
charStr = "{"""
For i = LBound(charArr) To UBound(charArr)
    charStr = charStr & charArr(i) & ""","""
Next
charStr = Left(charStr, Len(charStr) - 2) & "}"

If Evaluate("ISERROR(MATCH(" & Right(celstr, 1) & "," & charStr & ",0))") Then
    temp = True
End If

negt = temp
End Function

You would then call it thus in your button click event:

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

For Each cell In Worksheets("Sheet1").Range("A1:Z100").Cells
    If cell.value <> "" Then
        Text = DoReplace(cell.value)
        if negt(cell.value) then
            CalculatedValue = Val(Text) * 0.01 * -1
        else
            CalculatedValue = Val(Text) * 0.01
        End If
 Worksheets("Sheet2").Cells(cell.Row, cell.Column).value = CalculatedValue
    End If
Next
End Sub

You would then remove all the '-'s from the all the replace lines.

Collectives™ on Stack Overflow

Replace function and negative values

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related