现在的位置: 首页 > 搜索引擎优化 > 正文

SEO流量及品牌词非品牌词日访问次数访问量及占比等处理VBA代码

2014年01月06日 搜索引擎优化 ⁄ 共 10710字 暂无评论 ⁄ 阅读 1,715 次

该数据为原WA人员交接数据,因处理繁琐,所需时间较长故写了Excel VBA代码以实现一键完成。同时,也是笨鸟玩VBA的练习代码,数据本身非SEO必要数据,可转移性也不高。若您对Excel VBA自动处理数据有兴趣或疑问,欢迎沟通交流。

Sub SEO数据库自动化()
' 【备注1】源数据路径定义,现数据路径为“C:\Users\NJ\Desktop\月常规数据\中间数据\”,若更换数据路径 _
请使用Ctrl+H将该路径全部替换为新路径。
' 【备注2】SEO全流量源数据文件请使用“城市名”命名,品牌词源数据请使用“城市双拼首字母+365”命名
' 【备注3】每月使用时,请将静态变量b更改为当月天数
Windows("2014SEO数据库.xlsx").Activate
Sheets("南京").Select
Dim a As Integer
a = Range("C65536").End(xlUp).Row
Static b As Byte
b = 31
Range("C" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'C:\Users\NJ\Desktop\月常规数据\中间数据\[南京.xlsx]数据集2'!R2C1:R32C3,2,0)"
Range("D" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'C:\Users\NJ\Desktop\月常规数据\中间数据\[南京.xlsx]数据集2'!R2C1:R32C3,3,0)"
Range("E" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 3), Cells(a + 1, 5)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 3), Cells(a + b, 5)), Type:=xlFillDefault
Range(Cells(a + 1, 3), Cells(a + b, 5)).Select
Range("F" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'C:\Users\NJ\Desktop\月常规数据\中间数据\[nj365.xlsx]数据集2'!R1C[-5]:R32C[-3],2,0)"
Range("G" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'C:\Users\NJ\Desktop\月常规数据\中间数据\[nj365.xlsx]数据集2'!R1C[-6]:R32C[-4],3,0)"
Range("H" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 6), Cells(a + 1, 8)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 6), Cells(a + b, 8)), Type:=xlFillDefault
Range(Cells(a + 1, 6), Cells(a + b, 8)).Select
Range("I" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("K" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("J" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[1]/RC[-1]"
Range(Cells(a + 1, 9), Cells(a + 1, 11)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 9), Cells(a + b, 11)), Type:=xlFillDefault
Range(Cells(a + 1, 9), Cells(a + b, 11)).Select
Range(Cells(a + 1, 3), Cells(a + b, 11)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("苏州").Select
Range("C" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'C:\Users\NJ\Desktop\月常规数据\中间数据\[苏州.xlsx]数据集2'!R2C1:R32C3,2,0)"
Range("D" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'C:\Users\NJ\Desktop\月常规数据\中间数据\[苏州.xlsx]数据集2'!R2C1:R32C3,3,0)"
Range("E" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 3), Cells(a + 1, 5)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 3), Cells(a + 31, 5)), Type:=xlFillDefault
Range(Cells(a + 1, 3), Cells(a + 31, 5)).Select
Range("F" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'C:\Users\NJ\Desktop\月常规数据\中间数据\[sz365.xlsx]数据集2'!R1C[-5]:R32C[-3],2,0)"
Range("G" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'C:\Users\NJ\Desktop\月常规数据\中间数据\[sz365.xlsx]数据集2'!R1C[-6]:R32C[-4],3,0)"
Range("H" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 6), Cells(a + 1, 8)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 6), Cells(a + b, 8)), Type:=xlFillDefault
Range(Cells(a + 1, 6), Cells(a + b, 8)).Select
Range("I" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("K" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("J" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[1]/RC[-1]"
Range(Cells(a + 1, 9), Cells(a + 1, 11)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 9), Cells(a + b, 11)), Type:=xlFillDefault
Range(Cells(a + 1, 9), Cells(a + b, 11)).Select
Range(Cells(a + 1, 3), Cells(a + b, 11)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("无锡").Select
Range("C" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'C:\Users\NJ\Desktop\月常规数据\中间数据\[无锡.xlsx]数据集2'!R2C1:R32C3,2,0)"
Range("D" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'C:\Users\NJ\Desktop\月常规数据\中间数据\[无锡.xlsx]数据集2'!R2C1:R32C3,3,0)"
Range("E" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 3), Cells(a + 1, 5)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 3), Cells(a + 31, 5)), Type:=xlFillDefault
Range(Cells(a + 1, 3), Cells(a + 31, 5)).Select
Range("F" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'C:\Users\NJ\Desktop\月常规数据\中间数据\[wx365.xlsx]数据集2'!R1C[-5]:R32C[-3],2,0)"
Range("G" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'C:\Users\NJ\Desktop\月常规数据\中间数据\[wx365.xlsx]数据集2'!R1C[-6]:R32C[-4],3,0)"
Range("H" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 6), Cells(a + 1, 8)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 6), Cells(a + b, 8)), Type:=xlFillDefault
Range(Cells(a + 1, 6), Cells(a + b, 8)).Select
Range("I" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("K" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("J" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[1]/RC[-1]"
Range(Cells(a + 1, 9), Cells(a + 1, 11)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 9), Cells(a + b, 11)), Type:=xlFillDefault
Range(Cells(a + 1, 9), Cells(a + b, 11)).Select
Range(Cells(a + 1, 3), Cells(a + b, 11)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("合肥").Select
Range("C" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'C:\Users\NJ\Desktop\月常规数据\中间数据\[合肥.xlsx]数据集2'!R2C1:R32C3,2,0)"
Range("D" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'C:\Users\NJ\Desktop\月常规数据\中间数据\[合肥.xlsx]数据集2'!R2C1:R32C3,3,0)"
Range("E" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 3), Cells(a + 1, 5)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 3), Cells(a + 31, 5)), Type:=xlFillDefault
Range(Cells(a + 1, 3), Cells(a + 31, 5)).Select
Range("F" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'C:\Users\NJ\Desktop\月常规数据\中间数据\[hf365.xlsx]数据集2'!R1C[-5]:R32C[-3],2,0)"
Range("G" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'C:\Users\NJ\Desktop\月常规数据\中间数据\[hf365.xlsx]数据集2'!R1C[-6]:R32C[-4],3,0)"
Range("H" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 6), Cells(a + 1, 8)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 6), Cells(a + b, 8)), Type:=xlFillDefault
Range(Cells(a + 1, 6), Cells(a + b, 8)).Select
Range("I" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("K" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("J" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[1]/RC[-1]"
Range(Cells(a + 1, 9), Cells(a + 1, 11)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 9), Cells(a + b, 11)), Type:=xlFillDefault
Range(Cells(a + 1, 9), Cells(a + b, 11)).Select
Range(Cells(a + 1, 3), Cells(a + b, 11)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("杭州").Select
Range("C" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'C:\Users\NJ\Desktop\月常规数据\中间数据\[杭州.xlsx]数据集2'!R2C1:R32C3,2,0)"
Range("D" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'C:\Users\NJ\Desktop\月常规数据\中间数据\[杭州.xlsx]数据集2'!R2C1:R32C3,3,0)"
Range("E" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 3), Cells(a + 1, 5)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 3), Cells(a + 31, 5)), Type:=xlFillDefault
Range(Cells(a + 1, 3), Cells(a + 31, 5)).Select
Range("F" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'C:\Users\NJ\Desktop\月常规数据\中间数据\[hz365.xlsx]数据集2'!R1C[-5]:R32C[-3],2,0)"
Range("G" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'C:\Users\NJ\Desktop\月常规数据\中间数据\[hz365.xlsx]数据集2'!R1C[-6]:R32C[-4],3,0)"
Range("H" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 6), Cells(a + 1, 8)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 6), Cells(a + b, 8)), Type:=xlFillDefault
Range(Cells(a + 1, 6), Cells(a + b, 8)).Select
Range("I" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("K" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("J" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[1]/RC[-1]"
Range(Cells(a + 1, 9), Cells(a + 1, 11)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 9), Cells(a + b, 11)), Type:=xlFillDefault
Range(Cells(a + 1, 9), Cells(a + b, 11)).Select
Range(Cells(a + 1, 3), Cells(a + b, 11)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("常州").Select
Range("C" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'C:\Users\NJ\Desktop\月常规数据\中间数据\[常州.xlsx]数据集2'!R2C1:R32C3,2,0)"
Range("D" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'C:\Users\NJ\Desktop\月常规数据\中间数据\[常州.xlsx]数据集2'!R2C1:R32C3,3,0)"
Range("E" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 3), Cells(a + 1, 5)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 3), Cells(a + 31, 5)), Type:=xlFillDefault
Range(Cells(a + 1, 3), Cells(a + 31, 5)).Select
Range("F" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'C:\Users\NJ\Desktop\月常规数据\中间数据\[cz365.xlsx]数据集2'!R1C[-5]:R32C[-3],2,0)"
Range("G" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'C:\Users\NJ\Desktop\月常规数据\中间数据\[cz365.xlsx]数据集2'!R1C[-6]:R32C[-4],3,0)"
Range("H" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 6), Cells(a + 1, 8)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 6), Cells(a + b, 8)), Type:=xlFillDefault
Range(Cells(a + 1, 6), Cells(a + b, 8)).Select
Range("I" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("K" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("J" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[1]/RC[-1]"
Range(Cells(a + 1, 9), Cells(a + 1, 11)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 9), Cells(a + b, 11)), Type:=xlFillDefault
Range(Cells(a + 1, 9), Cells(a + b, 11)).Select
Range(Cells(a + 1, 3), Cells(a + b, 11)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("西安").Select
Range("C" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'C:\Users\NJ\Desktop\月常规数据\中间数据\[西安.xlsx]数据集2'!R2C1:R32C3,2,0)"
Range("D" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'C:\Users\NJ\Desktop\月常规数据\中间数据\[西安.xlsx]数据集2'!R2C1:R32C3,3,0)"
Range("E" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 3), Cells(a + 1, 5)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 3), Cells(a + 31, 5)), Type:=xlFillDefault
Range(Cells(a + 1, 3), Cells(a + 31, 5)).Select
Range("F" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'C:\Users\NJ\Desktop\月常规数据\中间数据\[xa365.xlsx]数据集2'!R1C[-5]:R32C[-3],2,0)"
Range("G" & a + 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'C:\Users\NJ\Desktop\月常规数据\中间数据\[xa365.xlsx]数据集2'!R1C[-6]:R32C[-4],3,0)"
Range("H" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range(Cells(a + 1, 6), Cells(a + 1, 8)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 6), Cells(a + b, 8)), Type:=xlFillDefault
Range(Cells(a + 1, 6), Cells(a + b, 8)).Select
Range("I" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("K" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-3]"
Range("J" & a + 1).Select
ActiveCell.FormulaR1C1 = "=RC[1]/RC[-1]"
Range(Cells(a + 1, 9), Cells(a + 1, 11)).Select
Selection.AutoFill Destination:=Range(Cells(a + 1, 9), Cells(a + b, 11)), Type:=xlFillDefault
Range(Cells(a + 1, 9), Cells(a + b, 11)).Select
Range(Cells(a + 1, 3), Cells(a + b, 11)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?