#excel #vba #replace #vlookup
#excel #vba #заменить #vlookup
Вопрос:
У меня есть текстовый файл размером 80 байт, из которого я извлекаю данные. Мне нужно преобразовать результат ячеек (j, 5) = Mid (tempstr, 43,13) в цифру в долларах.
Промежуточные результаты: 000000148012H
Самый правый символ — «H», и его необходимо преобразовать в «8» на основе приведенной ниже таблицы
Правильный символ
B 2 1
C 3 1
D 4 1
E 5 1
F 6 1
G 7 1
H 8 1
I 9 1
{ 0 1
J 1 -1
K 2 -1
L 3 -1
M 4 -1
N 5 -1
O 6 -1
P 7 -1
Q 8 -1
R 9 -1
} 0 -1
Формула Excel работает на листе как отдельная ячейка после вызова макросов, но мне нужно, чтобы это работало в коде VBA для этого листа в ячейках (j,5) и Cells (j, 6). E2 = средний результат (000000148012H), таблица данных = таблица выше
Формула Excel:
=REPLACE(E2,13,1,VLOOKUP(RIGHT(E2,1),'Data
Sheet'!$A$2:$C$21,2,FALSE))*VLOOKUP(RIGHT(E2,1),'Data
Sheet'!$A$2:$C$21,3,FALSE)*0.01
Reslut= 14801.28
Текущий код VBA:
If Val(Mid(tempstr, 13, 12)) <> 0 And Val(Mid(tempstr, 25, 7)) = "0000077" Then
Worksheets(2).Activate
If j = 1 Then
Cells(j, 1) = "SSN"
Cells(j, 2) = "Transaction"
Cells(j, 3) = "Source"
Cells(j, 4) = "Fund Symbol"
Cells(j, 5) = "Cash/Market Value"
Cells(j, 6) = "Shares"
Cells(j, 7) = "Cost Converted"
Cells(j, 8) = "C/M/V Converted"
Cells(j, 9) = "Shares Converted"
j = j 1
End If
Cells(j, 1) = Mid(tempstr, 13, 12) 'SSN
Cells(j, 2) = Mid(tempstr, 32, 3) 'Transaction
Cells(j, 3) = Mid(tempstr, 35, 2) 'Source
Cells(j, 4) = Mid(tempstr, 39, 4) 'Fund Symbol
Cells(j, 5) = Mid(tempstr, 43, 13) 'Cash/Market Value
Cells(j, 6) = Mid(tempstr, 56, 12) 'Shares
Cells(j, 7) = Mid(tempstr, 68, 13) 'Cost
j = j 1
End If
Ответ №1:
Я не получил никакого ответа на свой вопрос, поэтому я просто жестко закодировал значения, как показано ниже. кажется, работает отлично, но если что-либо когда-либо изменится в файле, его будет сложно обновить.
If Val(Mid(tempstr, 13, 12)) <> 0 And Val(Mid(tempstr, 25, 7)) = "0000077" Then
Worksheets(2).Activate
If j = 1 Then
Cells(j, 1) = "SSN"
Cells(j, 2) = "Transaction"
Cells(j, 3) = "Source"
Cells(j, 4) = "Fund Symbol"
Cells(j, 5) = "Cash/Market Value"
Cells(j, 6) = "Shares"
Cells(j, 7) = "Cost"
j = j 1
End If
Cells(j, 1) = Mid(tempstr, 13, 12) 'SSN
Cells(j, 2) = Mid(tempstr, 32, 3) 'Transaction
Cells(j, 3) = Mid(tempstr, 35, 2) 'Source
Cells(j, 4) = Mid(tempstr, 39, 4) 'Fund Symbol
If Mid(tempstr, 55, 1) = "A" Then Cells(j, 5) = 1 * (Mid(tempstr, 43, 12) amp; "1") * 0.01
If Mid(tempstr, 55, 1) = "B" Then Cells(j, 5) = 1 * (Mid(tempstr, 43, 12) amp; "2") * 0.01
If Mid(tempstr, 55, 1) = "C" Then Cells(j, 5) = 1 * (Mid(tempstr, 43, 12) amp; "3") * 0.01
If Mid(tempstr, 55, 1) = "D" Then Cells(j, 5) = 1 * (Mid(tempstr, 43, 12) amp; "4") * 0.01
If Mid(tempstr, 55, 1) = "E" Then Cells(j, 5) = 1 * (Mid(tempstr, 43, 12) amp; "5") * 0.01
If Mid(tempstr, 55, 1) = "F" Then Cells(j, 5) = 1 * (Mid(tempstr, 43, 12) amp; "6") * 0.01
If Mid(tempstr, 55, 1) = "G" Then Cells(j, 5) = 1 * (Mid(tempstr, 43, 12) amp; "7") * 0.01
If Mid(tempstr, 55, 1) = "H" Then Cells(j, 5) = 1 * (Mid(tempstr, 43, 12) amp; "8") * 0.01
If Mid(tempstr, 55, 1) = "I" Then Cells(j, 5) = 1 * (Mid(tempstr, 43, 12) amp; "9") * 0.01
If Mid(tempstr, 55, 1) = "{" Then Cells(j, 5) = 1 * (Mid(tempstr, 43, 12) amp; "0") * 0.01
If Mid(tempstr, 55, 1) = "J" Then Cells(j, 5) = -1 * (Mid(tempstr, 43, 12) amp; "1") * 0.01
If Mid(tempstr, 55, 1) = "K" Then Cells(j, 5) = -1 * (Mid(tempstr, 43, 12) amp; "2") * 0.01
If Mid(tempstr, 55, 1) = "L" Then Cells(j, 5) = -1 * (Mid(tempstr, 43, 12) amp; "3") * 0.01
If Mid(tempstr, 55, 1) = "M" Then Cells(j, 5) = -1 * (Mid(tempstr, 43, 12) amp; "4") * 0.01
If Mid(tempstr, 55, 1) = "N" Then Cells(j, 5) = -1 * (Mid(tempstr, 43, 12) amp; "5") * 0.01
If Mid(tempstr, 55, 1) = "O" Then Cells(j, 5) = -1 * (Mid(tempstr, 43, 12) amp; "6") * 0.01
If Mid(tempstr, 55, 1) = "P" Then Cells(j, 5) = -1 * (Mid(tempstr, 43, 12) amp; "7") * 0.01
If Mid(tempstr, 55, 1) = "Q" Then Cells(j, 5) = -1 * (Mid(tempstr, 43, 12) amp; "8") * 0.01
If Mid(tempstr, 55, 1) = "R" Then Cells(j, 5) = -1 * (Mid(tempstr, 43, 12) amp; "9") * 0.01
If Mid(tempstr, 55, 1) = "}" Then Cells(j, 5) = -1 * (Mid(tempstr, 43, 12) amp; "0") * 0.01 'Cash/Market Value
If Mid(tempstr, 67, 1) = "A" Then Cells(j, 6) = 1 * (Mid(tempstr, 56, 11) amp; "1") * 0.01
If Mid(tempstr, 67, 1) = "B" Then Cells(j, 6) = 1 * (Mid(tempstr, 56, 11) amp; "2") * 0.01
If Mid(tempstr, 67, 1) = "C" Then Cells(j, 6) = 1 * (Mid(tempstr, 56, 11) amp; "3") * 0.01
If Mid(tempstr, 67, 1) = "D" Then Cells(j, 6) = 1 * (Mid(tempstr, 56, 11) amp; "4") * 0.01
If Mid(tempstr, 67, 1) = "E" Then Cells(j, 6) = 1 * (Mid(tempstr, 56, 11) amp; "5") * 0.01
If Mid(tempstr, 67, 1) = "F" Then Cells(j, 6) = 1 * (Mid(tempstr, 56, 11) amp; "6") * 0.01
If Mid(tempstr, 67, 1) = "G" Then Cells(j, 6) = 1 * (Mid(tempstr, 56, 11) amp; "7") * 0.01
If Mid(tempstr, 67, 1) = "H" Then Cells(j, 6) = 1 * (Mid(tempstr, 56, 11) amp; "8") * 0.01
If Mid(tempstr, 67, 1) = "I" Then Cells(j, 6) = 1 * (Mid(tempstr, 56, 11) amp; "9") * 0.01
If Mid(tempstr, 67, 1) = "{" Then Cells(j, 6) = 1 * (Mid(tempstr, 56, 11) amp; "0") * 0.01
If Mid(tempstr, 67, 1) = "J" Then Cells(j, 6) = -1 * (Mid(tempstr, 56, 11) amp; "1") * 0.01
If Mid(tempstr, 67, 1) = "K" Then Cells(j, 6) = -1 * (Mid(tempstr, 56, 11) amp; "2") * 0.01
If Mid(tempstr, 67, 1) = "L" Then Cells(j, 6) = -1 * (Mid(tempstr, 56, 11) amp; "3") * 0.01
If Mid(tempstr, 67, 1) = "M" Then Cells(j, 6) = -1 * (Mid(tempstr, 56, 11) amp; "4") * 0.01
If Mid(tempstr, 67, 1) = "N" Then Cells(j, 6) = -1 * (Mid(tempstr, 56, 11) amp; "5") * 0.01
If Mid(tempstr, 67, 1) = "O" Then Cells(j, 6) = -1 * (Mid(tempstr, 56, 11) amp; "6") * 0.01
If Mid(tempstr, 67, 1) = "P" Then Cells(j, 6) = -1 * (Mid(tempstr, 56, 11) amp; "7") * 0.01
If Mid(tempstr, 67, 1) = "Q" Then Cells(j, 6) = -1 * (Mid(tempstr, 56, 11) amp; "8") * 0.01
If Mid(tempstr, 67, 1) = "R" Then Cells(j, 6) = -1 * (Mid(tempstr, 56, 11) amp; "9") * 0.01
If Mid(tempstr, 67, 1) = "}" Then Cells(j, 6) = -1 * (Mid(tempstr, 56, 11) amp; "0") * 0.01 'Shares
If Mid(tempstr, 80, 1) = "A" Then Cells(j, 7) = 1 * (Mid(tempstr, 68, 12) amp; "1") * 0.01
If Mid(tempstr, 80, 1) = "B" Then Cells(j, 7) = 1 * (Mid(tempstr, 68, 12) amp; "2") * 0.01
If Mid(tempstr, 80, 1) = "C" Then Cells(j, 7) = 1 * (Mid(tempstr, 68, 12) amp; "3") * 0.01
If Mid(tempstr, 80, 1) = "D" Then Cells(j, 7) = 1 * (Mid(tempstr, 68, 12) amp; "4") * 0.01
If Mid(tempstr, 80, 1) = "E" Then Cells(j, 7) = 1 * (Mid(tempstr, 68, 12) amp; "5") * 0.01
If Mid(tempstr, 80, 1) = "F" Then Cells(j, 7) = 1 * (Mid(tempstr, 68, 12) amp; "6") * 0.01
If Mid(tempstr, 80, 1) = "G" Then Cells(j, 7) = 1 * (Mid(tempstr, 68, 12) amp; "7") * 0.01
If Mid(tempstr, 80, 1) = "H" Then Cells(j, 7) = 1 * (Mid(tempstr, 68, 12) amp; "8") * 0.01
If Mid(tempstr, 80, 1) = "I" Then Cells(j, 7) = 1 * (Mid(tempstr, 68, 12) amp; "9") * 0.01
If Mid(tempstr, 80, 1) = "{" Then Cells(j, 7) = 1 * (Mid(tempstr, 68, 12) amp; "0") * 0.01
If Mid(tempstr, 80, 1) = "J" Then Cells(j, 7) = -1 * (Mid(tempstr, 68, 12) amp; "1") * 0.01
If Mid(tempstr, 80, 1) = "K" Then Cells(j, 7) = -1 * (Mid(tempstr, 68, 12) amp; "2") * 0.01
If Mid(tempstr, 80, 1) = "L" Then Cells(j, 7) = -1 * (Mid(tempstr, 68, 12) amp; "3") * 0.01
If Mid(tempstr, 80, 1) = "M" Then Cells(j, 7) = -1 * (Mid(tempstr, 68, 12) amp; "4") * 0.01
If Mid(tempstr, 80, 1) = "N" Then Cells(j, 7) = -1 * (Mid(tempstr, 68, 12) amp; "5") * 0.01
If Mid(tempstr, 80, 1) = "O" Then Cells(j, 7) = -1 * (Mid(tempstr, 68, 12) amp; "6") * 0.01
If Mid(tempstr, 80, 1) = "P" Then Cells(j, 7) = -1 * (Mid(tempstr, 68, 12) amp; "7") * 0.01
If Mid(tempstr, 80, 1) = "Q" Then Cells(j, 7) = -1 * (Mid(tempstr, 68, 12) amp; "8") * 0.01
If Mid(tempstr, 80, 1) = "R" Then Cells(j, 7) = -1 * (Mid(tempstr, 68, 12) amp; "9") * 0.01
If Mid(tempstr, 80, 1) = "}" Then Cells(j, 7) = -1 * (Mid(tempstr, 68, 12) amp; "0") * 0.01 'Cost
j = j 1
End If