将值转换为LBS -Excel VBA
将值转换为LBS -Excel VBA
提问于 2015-12-07 10:16:36
我试图使用以下代码将数字从不同的单位转换为LBS：
我不知道下面这个简单的代码有什么问题，但它没有给出任何输出：
数据采用表格格式，前2行被冻结。
截图附后
Option Explicit
Sub ConvertToLBS()
Application.ScreenUpdating = False
Dim wk As Worksheet
Dim str As String
Dim i As Long
Dim strq, strs As Double
Dim FinalRow As Long
Set wk = Sheets(1)
FinalRow = wk.Range("B900000").End(xlUp).Row
For i = 2 To FinalRow
str = wk.Range("R" & i).Text
str = Trim(str)
strq = wk.Range("Q" & i).Value
If str = "POUNDS" Then
strs = strq * 1
wk.Range("S" & i).Value = strs
Else: End If
If str = "YARDS" Then
strs = strq * 1688.55
wk.Range("S" & i).Value = strs
Else: End If
If str = "KILOGRAMS" Then
strs = strq * 2.20462
wk.Range("S" & i).Value = strs
Else: End If
If str = "TONS" Then
strs = strq * 2000
wk.Range("S" & i).Value = strs
Else: End If
If str = "GALLONS" Then
strs = strq * 8.34
wk.Range("S" & i).Value = strs
Else: End If
Next i
Application.ScreenUpdating = True
End Sub
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-12-07 10:42:11
我相信你的.其他人被搞砸了，可能是因为那些冒号试图缩短代码。我建议换一个选择Case语句。它们确实是为这样的有条件检查而设计的。
Sub ConvertToLBS()
Application.ScreenUpdating = False
Dim wk As Worksheet
Dim str As String
Dim i As Long, FinalRow As Long
Dim strq As Double, strs As Double
Set wk = Sheets(1)
With wk
FinalRow = .Range("B" & .Rows.Count).End(xlUp).Row
For i = 2 To FinalRow
str = Trim(UCase(.Range("R" & i).Text))
strq = .Range("Q" & i).Value
strs = 0
Select Case str
Case "POUNDS"
strs = strq * 1
Case "YARDS"
strs = strq * 1688.55
Case "KILOGRAMS"
strs = strq * 2.20462
Case "TONS"
strs = strq * 2000
Case "GALLONS"
strs = strq * 8.34
Case Else
'do nothing; not covered
Debug.Print str
End Select
.Range("S" & i) = strs
Next i
End With
Application.ScreenUpdating = True
End SubStack Overflow用户
发布于 2015-12-07 10:49:52
如评论中所建议的：
Option Compare Text
Option Explicit
Sub ConvertToLBS()
Application.ScreenUpdating = False
Dim wk As Worksheet
Dim str As String
Dim i As Long
Dim strq, strs As Double
Dim FinalRow As Long
'Set wk = Sheets(1)
Set wk = Sheets("BR Mailing List_12-4-15 (3)")
FinalRow = wk.Range("R" & wk.Rows.Count).End(xlUp).Row
For i = 2 To FinalRow
str = Trim(wk.Range("R" & i).Value)
strq = CDbl(wk.Range("Q" & i).Value)
Select Case str
Case Is = "POUNDS"
strs = strq * 1
Case Is = "YARDS"
strs = strq * 1688.55
Case Is = "KILOGRAMS"
strs = strq * 2.20462
Case Is = "TONS"
strs = strq * 2000
Case Is = "GALLONS"
strs = strq * 8.34
End Select
wk.Range("S" & i).Value = CDbl(strs)
Next i
Application.ScreenUpdating = True
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34131115
复制相关文章
相似问题
腾讯云开发者
