英文:
What does Application.CleanString() convert my string to?
问题
以下是代码部分的中文翻译:
Sub getRows()
'计算给定表格中的行数
rows_num = Selection.Tables(1).Rows.Count
'获取第3行到第N行,单元格2、3、5和7的值,并将它们保存为数字值,除了dGVS
For i = 3 To rows_num
With Selection.Tables(1).Rows(i)
TK = Val(Replace(Replace(.Cells(2).Range.Text, ",", "."), "+", ""))
dOSBL = Val(Replace(.Cells(3).Range.Text, ",", "."))
dAFRN = Val(Replace(.Cells(5).Range.Text, ",", "."))
dGVS = .Cells(7).Range.Text
End With
'清理dGVS,因为否则会出现特殊字符
dGVS = Application.CleanString(dGVS)
'计算所需的值
kOSBL = TK - dOSBL
kAFRN = TK - dAFRN
'应该检查dGVS是否为连字符,如果是,则将kGVS设置为相同的值,这个检查不起作用
If dGVS = "-" Then
kGVS = "-"
Else
dGVS = Val(Replace(dGVS, ",", "."))
kGVS = TK - dGVS
End If
'将计算出的值返回到所需的单元格
With Selection.Tables(1).Rows(i)
.Cells(4).Range.Text = "+" & kOSBL
.Cells(6).Range.Text = "+" & kAFRN
.Cells(8).Range.Text = "+" & kGVS
End With
Next i
End Sub
希望这有所帮助。如果您有任何其他问题,请随时提问。
英文:
I'm currently trying to take inputs from a table in word, convert the text to numbers, do some basic math on them and then return them to the desired cells.
A typical table looks something like this:
I've made the following code (forgive my spaghetti):
Sub getRows()
'Counts the number of rows in the given table
rows_num = Selection.Tables(1).Rows.Count
'Get the values of row 3 -> N, cell 2, 3, 5 and 7 and saves them as a numerical value, ecept for dGVS
For i = 3 To rows_num
With Selection.Tables(1).Rows(i)
TK = Val(Replace(Replace(.Cells(2).Range.Text, ",", "."), "+", ""))
dOSBL = Val(Replace(.Cells(3).Range.Text, ",", "."))
dAFRN = Val(Replace(.Cells(5).Range.Text, ",", "."))
dGVS = .Cells(7).Range.Text
End With
'Cleans dGVS since I get a special char otherwise
dGVS = Application.CleanString(dGVS)
'Calculates what I need
kOSBL = TK - dOSBL
kAFRN = TK - dAFRN
'Should check if dGVS is a hyphen and if so set kGVS to the same value, this check does not work
If dGVS = "-" Then
kGVS = "-"
Else
dGVS = Val(Replace(dGVS, ",", "."))
kGVS = TK - dGVS
End If
'Returns the calculated values to the desired cells
With Selection.Tables(1).Rows(i)
.Cells(4).Range.Text = "+" & kOSBL
.Cells(6).Range.Text = "+" & kAFRN
.Cells(8).Range.Text = "+" & kGVS
End With
Next i
End Sub
The only problem I seem to encounter is that the if-statement at the bottom doesn't seem to recognize the value of dGVS as a hyphen and therefore, I assume, the check fails and returns the following table:
Does this happen due to the Application.CleanString() or is it another problem entirely? Is there a way to figure out what symbol is actually contained in dGVS to make a proper check, or should I go about this entirely differetly?
A MsgBox print of dGVS returns the following:
答案1
得分: 1
通过使用AscW(dGVS)检查字符代码来解决了这个问题,对于这种情况中使用的连字符,它的代码是45,然后执行以下检查:
If AscW(dGVS) = 45 Then
kGVS = Chr(45)
Selection.Tables(1).Rows(i).Cells(8).Range.Text = kGVS
Else
dGVS = Val(Replace(dGVS, """, "."))
kGVS = TK - dGVS
Selection.Tables(1).Rows(i).Cells(8).Range.Text = "+" & kGVS
End If
感谢Jonsson的建议。
英文:
Solved the problem by checking the character code with AscW(dGVS), which for the used hyphen in this case was 45 and then doing the following check:
If AscW(dGVS) = 45 Then
kGVS = Chr(45)
Selection.Tables(1).Rows(i).Cells(8).Range.Text = kGVS
Else
dGVS = Val(Replace(dGVS, ",", "."))
kGVS = TK - dGVS
Selection.Tables(1).Rows(i).Cells(8).Range.Text = "+" & kGVS
End If
Thank you Jonsson for the suggestion.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论