使用时间格式重新格式化Excel表格的脚本字典
使用时间格式重新格式化Excel表格的脚本字典
提问于 2019-07-05 02:14:39
我在两个月前开始了我的VBA之旅，我遇到了一个我无法解决的问题。我有一个特定格式的表，我得到了使用脚本字典重新格式化的帮助。
我尝试添加另一个名为time的变量，并将其用"/“分隔。我担心的另一个问题是，time列的格式是"hh:mm”，这可以在循环后进行修改，尽管我相信。这是原始代码：
Dim lastrow As Long
Dim iter As Long
Dim diter As Long
Dim countrydict As Object
Dim country As String
Dim data As String
Dim key As Variant
Set countrydict = CreateObject("Scripting.Dictionary")
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).row
For iter = 1 To lastrow
country = Trim(.Cells(iter, 1).value)
data = Trim(.Cells(iter, 2).value)
If countrydict.Exists(country) Then
If Not InStr(1, countrydict(country), data) > 0 Then
' Remove Dupes
countrydict(country) = countrydict(country) & "|" & data
End If
Else
countrydict.Add country, data
End If
Next
iter = 1
For Each key In countrydict
.Cells(iter, 1).value = key & ":"
.cells(iter, 1).font.bold = True
.cells(iter, 1).font.colorindex = 30
iter = iter + 1
For diter = 0 To UBound(Split(countrydict(key), "|"))
.Cells(iter, 1).value = Split(countrydict(key), "|")(diter)
iter = iter + 1
Next
Next
.Columns("B").Clear
End With这会将我的表从以下格式转换为
"A" "B"
India Sales
France Sales
France Tax
Spain Sales
Spain Tax转到
India:
Sales
France:
Tax
Spain:
Sales
Tax 这可以很好地工作，但是我想知道如何添加另一个列，所以如果我有一个像这样的表
"A" "B" "C"
India Sales 12:00
France Sales 09:00
France Tax 11:00
Spain Sales 11:00
Spain Tax 05:00我想让它看起来像这样
"A" "B"
India:
Sales 12:00
France:
Sales 09:00
Tax 11:00
Spain:
Sales 11:00
Tax 05:00我试着添加了
dim diter2 as Long
For iter = 1 To lastrow
country = Trim(.Cells(iter, 1).value)
data = Trim(.Cells(iter, 2).value)
time = Trim(.Cells(iter, 3).value)
If countrydict.Exists(country) Then
If Not InStr(1, countrydict(country), data) > 0 Then
countrydict(country) = countrydict(country) & "|" & data & "/" & time
End If
Else
countrydict.Add country, data, time
End If
Next
iter = 1
For Each key In countrydict
.Cells(iter, 1).value = key & ":"
.cells(iter, 1).font.bold = True
.cells(iter, 1).font.colorindex = 30
iter = iter + 1
For diter = 0 To UBound(Split(countrydict(key), "|"))
.Cells(iter, 1).value = Split(countrydict(key), "|")(diter)
iter = iter + 1
For diter2 = 0 To UBound(Split(countrydict(key), "|"))
.Cells(iter, 2).value = Split(countrydict(key), "/")(diter2)
iter = iter + 1
Next
Next
Next非常感谢您的帮助
回答 1
Stack Overflow用户
发布于 2019-07-05 14:16:56
这应该更接近你想要的。您可以将时间复制为文本或值，并相应地设置接收单元格的格式。
dim diter2 as Long, arr, arr2
For iter = 1 To lastrow
country = Trim(.Cells(iter, 1).value)
data = Trim(.Cells(iter, 2).value)
time = Trim(.Cells(iter, 3).Text) '<<<<<<
If countrydict.Exists(country) Then
If Not InStr(1, countrydict(country), data) > 0 Then
countrydict(country) = countrydict(country) & _
"|" & data & "/" & time
End If
Else
countrydict.Add country, data & "/" & time '<<<edit
End If
Next
iter = 1
For Each key In countrydict
.Cells(iter, 1).value = key & ":"
.cells(iter, 1).font.bold = True
.cells(iter, 1).font.colorindex = 30
iter = iter + 1
arr = Split(countrydict(key), "|")
For diter = 0 To UBound(arr)
arr2 = Split(arr(diter), "/")
.Cells(iter, 1).value = arr2(0)
.Cells(iter, 2).value = arr2(1)
Next diter
Next key页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56892734
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号