英文:
Copying formulas in one column and pasting every nth column
问题
我试图复制包含成千上万行每行都有vlook-up的B列，并粘贴到每隔3列的后续列中。
我找到了这段代码，它可以工作，但它会将其粘贴为数值，我需要将vlook-up公式粘贴进去。
Sub Sco__copy()
Dim cpval As Range
Dim LastRow As Long
With Worksheets("Sheet8")
LastRow = .Cells(Rows.Count, "B:B").End(xlUp).Row
Set cpval = .Range("B12:B" & LastRow)
For colx = 2 To 40 Step 3
.Range(.Cells(12, colx), .Cells(LastRow, colx)).Formula = cpval.Formula
Next
End With
End Sub
英文:
I am trying to copy column B which contains thousands of rows with vlook-ups in each row and paste it into every following 3rd column.
I found this code which works but it's pasting as values, I need the vlook-up formulas to be pasted.
Sub Sco__copy()
Dim cpval As Range
Dim LastRow As Long
With Worksheets("Sheet8")
LastRow = .Cells(Rows.Count, "B:B").End(xlUp).Row
Set cpval = .Range("B12:B" & LastRow)
For colx = 2 To 40 Step 3
.Range(.Cells(12, colx), .Cells(LastRow, colx)).Value = cpval.Value
Next
End With
End Sub
答案1
得分: 1
这应该适用于您，只需将“value”更改为“formula”。
Sub Sco__copy()
Dim cpval As Range
Dim LastRow As Long
With Worksheets("Sheet8")
LastRow = .Cells(Rows.Count, "B:B").End(xlUp).Row
Set cpval = .Range("B12:B" & LastRow)
For colx = 2 To 40 Step 3
.Range(.Cells(12, colx), .Cells(LastRow, colx)).Formula = cpval.Formula
Next
End With
End Sub
英文:
This should work for you, just change "value" to "formula".
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-html -->
Sub Sco__copy()
Dim cpval As Range
Dim LastRow As Long
With Worksheets("Sheet8")
LastRow = .Cells(Rows.Count, "B:B").End(xlUp).Row
Set cpval = .Range("B12:B" & LastRow)
For colx = 2 To 40 Step 3
.Range(.Cells(12, colx), .Cells(LastRow, colx)).Formula = cpval.Formula
Next
End With
End Sub
<!-- end snippet -->
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论