Mirror Negative w/ Alpha - help with this

[jonfull]

I have data coming in that looks like:
<table border="0" cellpadding="0" cellspacing="0" width="147"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:694;width:14pt" width="19"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">Desire:</td> <td style="width:14pt" width="19">
</td> <td style="width:48pt" width="64">TEXT:</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">0008201</td> <td class="xl65">
</td> <td class="xl65">820a</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">0001112</td> <td class="xl65">
</td> <td class="xl65">111b</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">0001223</td> <td class="xl65">
</td> <td class="xl65">122c</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">0001334</td> <td class="xl65">
</td> <td class="xl65">133d</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">0001445</td> <td class="xl65">
</td> <td class="xl65">144e</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">0000056</td> <td class="xl65">
</td> <td class="xl65">5f</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">0949867</td> <td class="xl65">
</td> <td class="xl65">94986g</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">0949878</td> <td class="xl65">
</td> <td class="xl65">94987h</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20" align="right">-100011</td> <td class="xl66">
</td> <td class="xl65">10001j</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20" align="right">-010002</td> <td class="xl66">
</td> <td class="xl65">1000k</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20" align="right">-000003</td> <td class="xl66">
</td> <td class="xl65">0l</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20" align="right">-000110</td> <td class="xl66">
</td> <td class="xl65">11}</td> </tr> </tbody></table>

Where:
{=0
a=1
b=2
c=3
d=4
}=-
j=-1
k=-2
l=-3
m=-4

I've got the vba figured out for the positive translations, but i cannot figure out how to change the cell to negative. (Again, I need help making it negative and replacing the character at the end. I can only figure out how to replace the character and replace the font to red...)

this is what I have so far:
For Each c In Range("a2", Range("a" & Rows.Count).End(xlUp))
H = c
For a = 1 To Len(H)
If Not IsNumeric(Mid(H, a, 1)) Then
Select Case Mid(LCase(H), a, 1)
Case "a"
Mid(H, a, 1) = "1"
Case "b"
Mid(H, a, 1) = "2"
Case "c"
Mid(H, a, 1) = "3"
Case "d"
Mid(H, a, 1) = "4"
Case "e"
Mid(H, a, 1) = "5"
Case "f"
Mid(H, a, 1) = "6"
Case "g"
Mid(H, a, 1) = "7"
Case "h"
Mid(H, a, 1) = "8"
Case "i"
Mid(H, a, 1) = "9"
Case "{"
Mid(H, a, 1) = "0"


Case "}"
'NONE OF THESE WORK:
' c.Value = "'-'" & c.Value
'c.Font.ColorIndex = 3
' c.Value.InsertAfter Chr(1), "abc"
'c.Value = Left(c, 2) = "-1" & Mid(H, a, 1) = "0"
' c.Formula = ActiveCell.Value * -1
' c.Value = Evaluate(c.Address & "*-1")
'value ("-1)").Operation:=xlMultiply("-1")

 

[adam087]

I set up a 'lookup table' that looked like this; I guessed a few of them but I think I got them right. I then assigned it the name LU

Excel Workbook
	G	H	I
1	Code	Digit	Sign
2	{	0	1
3	a	1	1
4	b	2	1
5	c	3	1
6	d	4	1
7	e	5	1
8	f	6	1
9	g	7	1
10	h	8	1
11	i	9	1
12	}	0	-1
13	j	1	-1
14	k	2	-1
15	l	3	-1
16	m	4	-1
17	n	5	-1
18	o	6	-1
19	p	7	-1
20	q	8	-1
21	r	9	-1
Sheet1

#VALUE!

Excel 2007

Cell Formulas
Range	Formula
B2	=(LEFT(A2, LEN(A2)-1)&VLOOKUP(RIGHT(A2, 1), LU, 2, FALSE))*VLOOKUP(RIGHT(A2, 1), LU, 3, FALSE)

Excel Workbook
Name	Refers To
LU	=Sheet1!$F$1:$H$21
Workbook Defined Names

I hope this might help.

Regards
Adam

 

[jonfull]

I'm looking for a vba script, because I'm also having it automate several other functions that are necessary. This will also be a template of sorts, so that people won't have to go in and do any kind of formula's on their own, etc...hoping to reduce errors and standardize with this...

 

[adam087]

OK. I'm sorry I don't have enough time to look through all your code but my brief thoughts are that you might be able to adapt some of my code. It looks like your looping is a little excessive. Then you'd just need to Select Case based on the final character. Convert it to a value using Val() and then just multiply by -1 to get a negative number.

If this is still unanswered tomorrow morning I'll take another look.

Regards
Adam