excel - Excel 宏仅格式化重复值的第一个实例
问题描述
我有一个看起来像这样的 Excel makro
我希望它能够格式化和左对齐Streckkod和Återlämningsdatum的所有实例,但它只适用于最先出现的情况。我究竟做错了什么?
我只是一个复制粘贴程序员,这是我第二次尝试用 vba 做某事,所以任何帮助将不胜感激
Sub Transform()
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
Dim c As Long
Dim d As Double
Application.ScreenUpdating = False
Set wshS = ActiveSheet
wshS.Range("A1").CurrentRegion.Sort Key1:=wshS.Range("A1"), Header:=xlYes
m = wshS.Range("A" & wshS.Rows.Count).End(xlUp).Row
For s = 2 To m
If s > 2 Then
t = t + 2
wshT.Cells(t, 1).Value = "total price"
wshT.Cells(t, 2).Value = d
End If
If wshS.Range("A" & s).Value <> wshS.Range("A" & s - 1).Value Then
Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))
For c = 1 To 1
wshT.Cells(c, 1).Value = wshS.Cells(1, c).Value
wshT.Cells(c, 2).Value = wshS.Cells(s, c).Value
wshT.Cells(c, 2).NumberFormat = "0000000000"
wshT.Cells(c, 2).HorizontalAlignment = xlLeft
'Personnummer
Next c
t = 1
d = 0
For c = 2 To 5
wshT.Cells(c, 1).Value = wshS.Cells(1, c).Value
wshT.Cells(c, 2).Value = wshS.Cells(s, c).Value
Next c
t = 5
d = 0
End If
t = t + 1
For c = 6 To 8
t = t + 1
wshT.Cells(t, 1).Value = wshS.Cells(1, c).Value
wshT.Cells(t, 2).Value = wshS.Cells(s, c).Value
wshT.Cells(c, 1).EntireColumn.AutoFit
wshT.Cells(c, 2).EntireColumn.AutoFit
Next c
For c = 9 To 9
t = t + 1
wshT.Cells(t, 1).Value = wshS.Cells(1, c).Value
wshT.Cells(t, 2).Value = wshS.Cells(s, c).Value
wshT.Cells(c, 2).NumberFormat = "0000000000000"
wshT.Cells(c, 2).HorizontalAlignment = xlLeft
'Streckkod
Next c
For c = 10 To 10
t = t + 1
wshT.Cells(t, 1).Value = wshS.Cells(1, c).Value
wshT.Cells(t, 2).Value = wshS.Cells(s, c).Value
wshT.Cells(c, 2).NumberFormat = "yyyy-mm-dd"
wshT.Cells(c, 2).HorizontalAlignment = xlLeft
'återlämningsdatum
Next c
d = d + wshS.Cells(s, 10).Value
Next s
' Last total
t = t + 2
wshT.Cells(t, 1).Value = "total price"
wshT.Cells(t, 2).Value = d
Application.ScreenUpdating = True
End Sub
解决方案
推荐阅读
- c# - 在 ASP.NET Core MVC 中将表单发布到控制器不起作用
- android-studio - 如何在 Android Studio 中运行 Kotlin 的控制台代码?
- javascript - ajax查询子地址时如何使Express JS运行代码
- ruby-on-rails - 如何在 Ruby 中使用事务性电子邮件服务向用户发送激活帐户电子邮件?
- javascript - 如何在数组中找到第一个重复项?
- javascript - 检查另一层时,通过在控制层中解开一层来删除传单层
- list - 如何在 Dart 中实例化具有默认可修改列表字段的对象
- amazon-web-services - 使用变量创建资源
- python - 存储python类实例以迭代和处理,但每个会话保持实例分开
- c - __asm __volatile 如何执行?