花式柔道总冠军丶
sub initialdata()
sheets("sheet1").select
range("b27").select
activecell.formular1c1 = "len("""")"
range("b35").select
activecell.formular1c1 = "code(""t"")"
range("b36").select
sheets("sheet2").select
range("k22").select
activecell.formular1c1 = "zip001"
range("k23").select
activecell.formular1c1 = "zip001"
range("k24").select
activecell.formular1c1 = "zip001"
range("k25").select
activecell.formular1c1 = "zip001"
range("k26").select
activecell.formular1c1 = "zip001"
range("k27").select
activecell.formular1c1 = "zip001"
range("k28").select
activecell.formular1c1 = "zip002"
range("k29").select
activecell.formular1c1 = "zip003"
range("k30").select
activecell.formular1c1 = "zip004"
range("k31").select
activecell.formular1c1 = "zip005"
range("k32").select
activecell.formular1c1 = "zip006"
range("k33").select
activecell.formular1c1 = "zip007"
range("k34").select
activecell.formular1c1 = "zip008"
range("k35").select
activecell.formular1c1 = "zip009"
range("k36").select
activecell.formular1c1 = "zip010"
range("k37").select
activecell.formular1c1 = "zip010"
range("k38").select
activecell.formular1c1 = "zip011"
range("k39").select
activecell.formular1c1 = "zip012"
range("k40").select
activecell.formular1c1 = "zip013"
range("k41").select
activecell.formular1c1 = "zip014"
range("k42").select
activecell.formular1c1 = "zip015"
range("k43").select
activecell.formular1c1 = "zip016"
range("k44").select
activecell.formular1c1 = "zip017"
range("k45").select
activecell.formular1c1 = "zip018"
range("l22").select
activecell.formular1c1 = "aceo1"
range("l23").select
activecell.formular1c1 = "aceo2"
range("l24").select
activecell.formular1c1 = "aceo3"
range("l25").select
activecell.formular1c1 = "aceo4"
range("l26").select
activecell.formular1c1 = "aceo5"
range("l27").select
activecell.formular1c1 = "aceo6"
range("l28").select
activecell.formular1c1 = "rar_ace"
range("l29").select
activecell.formular1c1 = "aceo8"
range("l30").select
activecell.formular1c1 = "aceo9"
range("l31").select
activecell.formular1c1 = "aceo10"
range("l32").select
activecell.formular1c1 = "aceo11"
range("l33").select
activecell.formular1c1 = "aceo12"
range("l34").select
activecell.formular1c1 = "aceo13"
range("l35").select
activecell.formular1c1 = "aceo14"
range("l36").select
activecell.formular1c1 = "rar_ace"
range("l37").select
activecell.formular1c1 = "aceo16"
range("l38").select
activecell.formular1c1 = "aceo17"
range("l39").select
activecell.formular1c1 = "aceo18"
range("l40").select
activecell.formular1c1 = "aceo19"
range("l41").select
activecell.formular1c1 = "aceo20"
range("l42").select
activecell.formular1c1 = "aceo21"
range("l43").select
activecell.formular1c1 = "aceo22"
range("l44").select
activecell.formular1c1 = "aceo23"
range("l45").select
activecell.formular1c1 = "aceo24"
range("l46").select
sheets("sheet3").select
range("c2").select
activecell.formular1c1 = "my"
range("d2").select
activecell.formular1c1 = "77s"
range("f2").select
activecell.formular1c1 = "my"
range("g2").select
activecell.formular1c1 = "77s"
range("h2").select
activecell.formular1c1 = "ttt"
range("j2").select
activecell.formular1c1 = "my"
range("k2").select
activecell.formular1c1 = "77s"
range("l2").select
activecell.formular1c1 = "ttt"
range("n3").select
activecell.formular1c1 = "my"
range("n4").select
activecell.formular1c1 = "77s"
range("n5").select
activecell.formular1c1 = "ttt"
range("n13").select
activecell.formular1c1 = "my"
range("n14").select
activecell.formular1c1 = "77s"
range("n15").select
activecell.formular1c1 = "ttt"
end sub
sub groupdata()
with worksheets("sheet2")
dim endrow as long
dim companyendrow as long
dim r as range
dim findcell as range
dim companycount as integer
.activate
endrow = iif(.range("k22").end(xldown).row = 1048576, 22, .range("k22").end(xldown).row)
companyendrow = iif(worksheets("sheet3").range("c3").end(xldown).row = 1048576, 3, worksheets("sheet3").range("f3").end(xldown).row)
worksheets("sheet3").range("c3:" & "d" & companyendrow).clearcontents
worksheets("sheet3").range("f3:" & "h" & (3 9 * 3 - 1)).clearcontents
worksheets("sheet3").range("j3:" & "l" & (3 9 * 3 - 1)).clearcontents
worksheets("sheet3").range("j3:" & "l" & (3 9 * 3 - 1)).clearcontents
worksheets("sheet3").[o3:o5].clearcontents
worksheets("sheet3").[o13:o15].clearcontents
.range("k22:k" & endrow).advancedfilter action:=xlfiltercopy, copytorange:=worksheets("sheet3").range("c3"), unique:=true
with worksheets("sheet3")
if .range("c3").end(xldown).row = 1048576 then
companyendrow = 3
else
.range("c3:c" & .range("c3").end(xldown).row).removeduplicates columns:=1, header:=xlno
companyendrow = iif(.range("c3").end(xldown).row = 1048576, 3, .range("c3").end(xldown).row)
end if
end with
for each r in .range("k22:k" & endrow)
if r.offset(0, 1).value <> "rar_ace" then
set findcell = worksheets("sheet3").range("c3:c" & companyendrow).find(what:=r.value, after:=worksheets("sheet3").range("c3"), lookin:=xlformulas, lookat _
:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, matchcase:= _
false, matchbyte:=false, searchformat:=false)
if not findcell is nothing then
findcell.offset(0, 1).value = iif(findcell.offset(0, 1).value = "", r.offset(0, 1).value, findcell.offset(0, 1).value & "," & r.offset(0, 1).value)
end if
end if
next r
application.calculation = xlcalculationmanual
application.screenupdating = false
with worksheets("sheet3")
for each r in .range("c3:c" & companyendrow)
companycount = companycount 1
if companycount <= 9 then
.range("f" & (3 (companycount - 1) * 3)).value = .range("c" & (3 companycount - 1)).value
.range("f" & (3 (companycount - 1) * 3) 1).value = .range("c" & (3 companycount - 1)).value
.range("f" & (3 (companycount - 1) * 3) 2).value = .range("c" & (3 companycount - 1)).value
.range("g" & (3 (companycount - 1) * 3)).value = "a"
.range("g" & (3 (companycount - 1) * 3) 1).value = .range("d" & (3 companycount - 1)).value
.range("g" & (3 (companycount - 1) * 3) 2).value = "b"
.range("h" & (3 (companycount - 1) * 3)).value = "aa"
.range("h" & (3 (companycount - 1) * 3) 1).value = "bb"
.range("h" & (3 (companycount - 1) * 3) 2).value = "cc"
else
.range("j" & (3 (companycount - 9 - 1) * 3)).value = .range("c" & (3 companycount - 1)).value
.range("j" & (3 (companycount - 9 - 1) * 3) 1).value = .range("c" & (3 companycount - 1)).value
.range("j" & (3 (companycount - 9 - 1) * 3) 2).value = .range("c" & (3 companycount - 1)).value
.range("k" & (3 (companycount - 9 - 1) * 3)).value = "a"
.range("k" & (3 (companycount - 9 - 1) * 3) 1).value = .range("d" & (3 companycount - 1)).value
.range("k" & (3 (companycount - 9 - 1) * 3) 2).value = "b"
.range("l" & (3 (companycount - 9 - 1) * 3)).value = "aa"
.range("l" & (3 (companycount - 9 - 1) * 3) 1).value = "bb"
.range("l" & (3 (companycount - 9 - 1) * 3) 2).value = "cc"
end if
next r
companyendrow = iif(.range("f3").end(xldown).row = 1048576, 3, .range("f3").end(xldown).row)
for each r in .range("f3:f" & companyendrow)
.[o3].value = iif(.[o3].value = "", r.value, .[o3].value & "|" & r.value)
.[o4].value = iif(.[o4].value = "", r.offset(0, 1).value, .[o4].value & "|" & r.offset(0, 1).value)
.[o5].value = iif(.[o5].value = "", r.offset(0, 2).value, .[o5].value & "|" & r.offset(0, 2).value)
next r
companyendrow = iif(.range("j3").end(xldown).row = 1048576, 3, .range("j3").end(xldown).row)
for each r in .range("j3:j" & companyendrow)
.[o13].value = iif(.[o13].value = "", r.value, .[o13].value & "|" & r.value)
.[o14].value = iif(.[o14].value = "", r.offset(0, 1).value, .[o14].value & "|" & r.offset(0, 1).value)
.[o15].value = iif(.[o15].value = "", r.offset(0, 2).value, .[o15].value & "|" & r.offset(0, 2).value)
next r
.activate
end with
with worksheets("sheet1")
.[c3].formula = "=" & .[b27].value & ""
.[c7].formula = "=" & .[b35].value & ""
end with
application.calculation = xlcalculationautomatic
application.screenupdating = true
end with
end sub
用户登录
还没有账号?立即注册
用户注册
投稿取消
| 文章分类: |
|
还能输入300字
上传中....
