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

多站点SEO关键词周流量前百词及变化率处理VBA代码

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

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

Sub 各站SEO关键词周流量前百()
' 【使用方法】
' 【第一步】使用GA导出自然流量关键词流量前100的词,并重命名为城市名放置在“C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\”目录下;
' 【第二步】打开2014SEO关键词文件,选中所要处理的周的第一个待处理单元格(如,第一周为C5,第二周为H5等),然后点运行该宏即可;
' 【备注1】若更改源数据文件路径,可使用Ctrl+H,将“C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\”替换为新路径即可;
Dim r, c As Integer
r = ActiveCell.Row
c = ActiveCell.Column
Cells(5, c).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[南京.xlsx]数据集1'!R[-3]C[" & 1 - c & "]"
Cells(5, c + 1).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[南京.xlsx]数据集1'!R[-3]C[" & 1 - c & "]/7"
Range(Cells(5, c), Cells(5, c + 1)).Select
Selection.AutoFill Destination:=Range(Cells(5, c), Cells(104, c + 1))
Cells(r - 2, c + 4) = "=MATCH(""(not provided)"",R[-2]C[-4]:R[102]C[-4],)"
Dim a As Byte
a = Cells(r - 2, c + 4).Value
Range(Cells(a, c), Cells(a, c + 1)).Select
Selection.Delete Shift:=xlUp
Cells(r - 2, c + 4).ClearContents
Cells(5, c + 3).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],R5C3:R103C4,2,0)"
Cells(5, c + 2).Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[1])/RC[1]"
Range(Cells(5, c + 2), Cells(5, c + 3)).Select
Selection.AutoFill Destination:=Range(Cells(5, c + 2), Cells(103, c + 3)), Type:=xlFillDefault
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Cells(3, c + 5), Cells(103, c + 9)).Select
Selection.Copy
Cells(3, c + 11).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Sheets("苏州").Select
Cells(5, c).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[苏州.xlsx]数据集1'!R[-3]C[" & 1 - c & "]"
Cells(5, c + 1).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[苏州.xlsx]数据集1'!R[-3]C[" & 1 - c & "]/7"
Range(Cells(5, c), Cells(5, c + 1)).Select
Selection.AutoFill Destination:=Range(Cells(5, c), Cells(104, c + 1))
Cells(r - 2, c + 4) = "=MATCH(""(not provided)"",R[-2]C[-4]:R[102]C[-4],)"
a = Cells(r - 2, c + 4).Value
Range(Cells(a, c), Cells(a, c + 1)).Select
Selection.Delete Shift:=xlUp
Cells(r - 2, c + 4).ClearContents
Cells(5, c + 3).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],R5C3:R103C4,2,0)"
Cells(5, c + 2).Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[1])/RC[1]"
Range(Cells(5, c + 2), Cells(5, c + 3)).Select
Selection.AutoFill Destination:=Range(Cells(5, c + 2), Cells(103, c + 3)), Type:=xlFillDefault
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Cells(3, c + 5), Cells(103, c + 9)).Select
Selection.Copy
Cells(3, c + 11).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Sheets("无锡").Select
Cells(5, c).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[无锡.xlsx]数据集1'!R[-3]C[" & 1 - c & "]"
Cells(5, c + 1).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[无锡.xlsx]数据集1'!R[-3]C[" & 1 - c & "]/7"
Range(Cells(5, c), Cells(5, c + 1)).Select
Selection.AutoFill Destination:=Range(Cells(5, c), Cells(104, c + 1))
Cells(r - 2, c + 4) = "=MATCH(""(not provided)"",R[-2]C[-4]:R[102]C[-4],)"
a = Cells(r - 2, c + 4).Value
Range(Cells(a, c), Cells(a, c + 1)).Select
Selection.Delete Shift:=xlUp
Cells(r - 2, c + 4).ClearContents
Cells(5, c + 3).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],R5C3:R103C4,2,0)"
Cells(5, c + 2).Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[1])/RC[1]"
Range(Cells(5, c + 2), Cells(5, c + 3)).Select
Selection.AutoFill Destination:=Range(Cells(5, c + 2), Cells(103, c + 3)), Type:=xlFillDefault
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Cells(3, c + 5), Cells(103, c + 9)).Select
Selection.Copy
Cells(3, c + 11).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Sheets("合肥").Select
Cells(5, c).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[合肥.xlsx]数据集1'!R[-3]C[" & 1 - c & "]"
Cells(5, c + 1).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[合肥.xlsx]数据集1'!R[-3]C[" & 1 - c & "]/7"
Range(Cells(5, c), Cells(5, c + 1)).Select
Selection.AutoFill Destination:=Range(Cells(5, c), Cells(104, c + 1))
Cells(r - 2, c + 4) = "=MATCH(""(not provided)"",R[-2]C[-4]:R[102]C[-4],)"
a = Cells(r - 2, c + 4).Value
Range(Cells(a, c), Cells(a, c + 1)).Select
Selection.Delete Shift:=xlUp
Cells(r - 2, c + 4).ClearContents
Cells(5, c + 3).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],R5C3:R103C4,2,0)"
Cells(5, c + 2).Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[1])/RC[1]"
Range(Cells(5, c + 2), Cells(5, c + 3)).Select
Selection.AutoFill Destination:=Range(Cells(5, c + 2), Cells(103, c + 3)), Type:=xlFillDefault
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Cells(3, c + 5), Cells(103, c + 9)).Select
Selection.Copy
Cells(3, c + 11).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Sheets("常州").Select
Cells(5, c).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[常州.xlsx]数据集1'!R[-3]C[" & 1 - c & "]"
Cells(5, c + 1).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[常州.xlsx]数据集1'!R[-3]C[" & 1 - c & "]/7"
Range(Cells(5, c), Cells(5, c + 1)).Select
Selection.AutoFill Destination:=Range(Cells(5, c), Cells(104, c + 1))
Cells(r - 2, c + 4) = "=MATCH(""(not provided)"",R[-2]C[-4]:R[102]C[-4],)"
a = Cells(r - 2, c + 4).Value
Range(Cells(a, c), Cells(a, c + 1)).Select
Selection.Delete Shift:=xlUp
Cells(r - 2, c + 4).ClearContents
Cells(5, c + 3).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],R5C3:R103C4,2,0)"
Cells(5, c + 2).Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[1])/RC[1]"
Range(Cells(5, c + 2), Cells(5, c + 3)).Select
Selection.AutoFill Destination:=Range(Cells(5, c + 2), Cells(103, c + 3)), Type:=xlFillDefault
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Cells(3, c + 5), Cells(103, c + 9)).Select
Selection.Copy
Cells(3, c + 11).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Sheets("杭州").Select
Cells(5, c).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[杭州.xlsx]数据集1'!R[-3]C[" & 1 - c & "]"
Cells(5, c + 1).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[杭州.xlsx]数据集1'!R[-3]C[" & 1 - c & "]/7"
Range(Cells(5, c), Cells(5, c + 1)).Select
Selection.AutoFill Destination:=Range(Cells(5, c), Cells(104, c + 1))
Cells(r - 2, c + 4) = "=MATCH(""(not provided)"",R[-2]C[-4]:R[102]C[-4],)"
a = Cells(r - 2, c + 4).Value
Range(Cells(a, c), Cells(a, c + 1)).Select
Selection.Delete Shift:=xlUp
Cells(r - 2, c + 4).ClearContents
Cells(5, c + 3).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],R5C3:R103C4,2,0)"
Cells(5, c + 2).Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[1])/RC[1]"
Range(Cells(5, c + 2), Cells(5, c + 3)).Select
Selection.AutoFill Destination:=Range(Cells(5, c + 2), Cells(103, c + 3)), Type:=xlFillDefault
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Cells(3, c + 5), Cells(103, c + 9)).Select
Selection.Copy
Cells(3, c + 11).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Sheets("西安").Select
Cells(5, c).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[西安.xlsx]数据集1'!R[-3]C[" & 1 - c & "]"
Cells(5, c + 1).Select
ActiveCell.FormulaR1C1 = "='C:\Users\NJ\Desktop\常规数据\SEO关键词中间数据\[西安.xlsx]数据集1'!R[-3]C[" & 1 - c & "]/7"
Range(Cells(5, c), Cells(5, c + 1)).Select
Selection.AutoFill Destination:=Range(Cells(5, c), Cells(104, c + 1))
Cells(r - 2, c + 4) = "=MATCH(""(not provided)"",R[-2]C[-4]:R[102]C[-4],)"
a = Cells(r - 2, c + 4).Value
Range(Cells(a, c), Cells(a, c + 1)).Select
Selection.Delete Shift:=xlUp
Cells(r - 2, c + 4).ClearContents
Cells(5, c + 3).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],R5C3:R103C4,2,0)"
Cells(5, c + 2).Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[1])/RC[1]"
Range(Cells(5, c + 2), Cells(5, c + 3)).Select
Selection.AutoFill Destination:=Range(Cells(5, c + 2), Cells(103, c + 3)), Type:=xlFillDefault
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Cells(3, c + 5), Cells(103, c + 9)).Select
Selection.Copy
Cells(3, c + 11).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(3, c - 1), Cells(103, c + 3)).Select
Sheets("南京").Select
End Sub

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

给我留言

留言无头像?