Excel VBA宏会产生意外结果吗？
Excel VBA宏会产生意外结果吗？
提问于 2020-12-16 16:13:47
我有一个宏循环通过一个工作表(Sheet4)将数据放在另一个工作表(Ark7)中。'rKvartal‘变量是从第三个工作表(Ark4)获取的文本字符串。只有在满足外部循环中的两个标准时，它才会进入内部循环。这部分起作用了。我的问题是，宏会列出内部循环中不小于或等于'rKvartal‘变量的数据。(这必须在宏中完成)宏如下所示：
Dim C, C2 As Range
Dim rKvartal As String
Application.ScreenUpdating = False
Ark7.Columns("B:F").ClearContents
cValOffset = 101
cVal = 2 'row# where data in rLog (ark7) goes to
rKvartal = Sheet3.Range("AP24") 'Criteria to filter data
'Create headers for use in the list being created
Ark7.Cells(1, 2).Value = "ID"
Ark7.Cells(1, 3).Value = "Sansynlighed"
Ark7.Cells(1, 4).Value = "Konsekvens"
Ark7.Cells(1, 5).Value = "Sum"
Ark7.Cells(1, 6).Value = "Kvartal"
For Each C In Sheet4.Range("W15:W114")
If C = "Risk" Or C = "Occured" Then
For Each C2 In Sheet4.Range("K15:K114")
If C2 <= rKvartal Then
'All cells that are either a risk or occured and that is less than or equal to e.g Q4 2021
Ark7.Cells(cVal, 2).Value = C.Offset(0, -21).Value
Ark7.Cells(cVal, 3).Value = C.Offset(0, -15).Value
Ark7.Cells(cVal, 4).Value = C.Offset(0, -14).Value
Ark7.Cells(cVal, 5).Value = C.Offset(0, -13).Value
Ark7.Cells(cVal, 6).Value = C.Offset(0, -12).Value
cVal = cVal + 1
Exit For
End If
Next C2
End If
Next C
ActiveWorkbook.Worksheets("rLog").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("rLog").Sort.SortFields.Add2 key:=Range("E2"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("rLog").Sort
.SetRange Range("B2:F100")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Ark7.Activate
Ark7.Range(Cells(1, 2), Cells(8, 6)).Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Ark7.Range(Cells(1, 2), Cells(8, 6)), , xlYes).Name = "RisikoSorteret" 'Laver en tabel eller liste
ActiveSheet.ListObjects("RisikoSorteret").TableStyle = "Tabeltypografi 3" 'Giver listen en typografi
End Sub
回答 2
Stack Overflow用户
发布于 2020-12-16 17:00:40
rKvartal是一个字符串变量，因此数字比较词(‘小于’或‘大于’)不起作用，因为它们只适用于数字和日期。
如果文本字符串rKvartal表示数值或日期值，请考虑使用CDec()或CDate()来获取该值。或者，如果Sheet3.Range("AP24")将包含数字或日期而不是文本，只需更改变量类型；
'Change
Dim rKvartal As String
'To any one of the below:
Dim rKvartal as Long 'Integer
Dim rKvartal as Date 'Date
Dim rKvartal as Double 'DecimalStack Overflow用户
发布于 2020-12-16 22:16:16
我自己想出来的。内部循环是冗余的。它只需要标准，而不是另一个循环：
For Each C In Sheet4.Range("W15:W114")
If C = "Risiko" Or C = "Indtraf" Then
If C.Offset(0, 1).Value <= rKvartal Then
'Alle risici der opfylder kriterierne
Ark7.Cells(cVal, 2).Value = C.Offset(0, -21).Value
Ark7.Cells(cVal, 3).Value = C.Offset(0, -15).Value
Ark7.Cells(cVal, 4).Value = C.Offset(0, -14).Value
Ark7.Cells(cVal, 5).Value = C.Offset(0, -13).Value
Ark7.Cells(cVal, 6).Value = C.Offset(0, 1).Value
cVal = cVal + 1
End If
End If
Next C页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/65319477
复制相关文章
相似问题
腾讯云开发者
