如何根据用户输入更改列?

huangapple go评论54阅读模式
英文:

How to change column depending on user input?

问题

"Ok, so I have some code in Excel VBA and what I'm struggling with is making it so that when I input a number let's say 3, the code firstly makes 3 new columns where to column R is and then makes 3 columns where the column W moved to. Is this possible? I was thinking something like column W + i but I don't know how to do that in VBA

Private Sub CommandButton1_Click()
Dim i As Integer
Dim n As Integer
n = InputBox("How many columns do you want to add?")
For i = 1 To n
Columns("R:R").Select
Columns("R:R").Copy
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Next i
Columns("W:W").Select
Columns("W:W").Copy
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Next i
End Sub

I don't know much about VBA so I just tried some experimenting with putting Columns("W + i:W + i").Select this as a line but it didn't work and I'm not even surprised."

英文:

Ok, so I have some code in Excel VBA and what I'm struggling with is making it so that when I input a number let's say 3, the code firstly makes 3 new columns where to column R is and then makes 3 columns where the column W moved to. Is this possible? I was thinking something like column W + i but i don't know how to do that in VBA

Private Sub CommandButton1_Click()
Dim i As Integer
Dim n As Integer
n = InputBox("How many columns do you want to add?")
For i = 1 To n
Columns("R:R").Select
Columns("R:R").Copy
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Next i
Columns("W:W").Select
Columns("W:W").Copy
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Next i
End Sub

I dont know much about VBA so I just tried some experimenting with putting Columns("W + i:W + i").Select this as a line but it didn't work and I'm not even surprised.

答案1

得分: 2

私有子CommandButton1_Click()

Dim n As Long, s
n = InputBox("要添加多少列?")
If n < 1 Then Exit Sub

对于每个s在数组("W:W", "R:R")中
    与列(s)一起
        复制
        .Resize(, n).Insert Shift:=xlToRight, _
           CopyOrigin:=xlFormatFromLeftOrAbove
    结束与
下一个
Application.CutCopyMode = False

结束子

英文:
Option Explicit

Private Sub CommandButton1_Click()

    Dim n As Long, s
    n = InputBox(&quot;How many columns do you want to add?&quot;)
    If n &lt; 1 Then Exit Sub
    
    For Each s In Array(&quot;W:W&quot;, &quot;R:R&quot;)
        With Columns(s)
            .Copy
            .Resize(, n).Insert Shift:=xlToRight, _
               CopyOrigin:=xlFormatFromLeftOrAbove
        End With
    Next
    Application.CutCopyMode = False
    
End Sub

</details>



huangapple
  • 本文由 发表于 2023年3月9日 21:38:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/75685371.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定