使用循环将某些sheet1单元格的粘贴值分配或复制到sheet2的确切列中，该计数器是在列中输入的值，例如:A1单元格 [英] assign or copy paste values of some sheet1 cells to exact columns in sheet2 using loop which counter is value entered in column eg:A1 cell

问题描述

希望有人可以提供帮助 我有下面的代码可以正常工作，因为我确定了范围并精确地给出了A1数值作为循环计数器和起点.

Private Sub Worksheet_Change(ByVal Target As Range)

    If (Range("A1") <> "") And (IsNumeric(Range("A1"))) And (Range("A1") > 0) Then
         Dim X As Integer
         If Not Intersect(Target, Range("A1")) Is Nothing Then
             For X = 1 To Range("A1").Value
                 Sheet4.Range("b" & X).Value = Range("A1").Value
             Next X
             MsgBox "done"
         Else
         End If
     Else
         MsgBox "no numeric"
     End If
End Sub

现在

我想在上面扩展此代码，以便当用户将sheet1 A1填充5时，然后将值从sheet2中的第一个空单元格开始粘贴到sheet2中的5个单元格中，例如:b1:b5或b10:b15. 在下一次我不知道将在sheet1的哪个单元格中填充A2'A3'A10'A80或任何A列单元格，因此在下次填充时，对输入的值时间执行相同的操作并粘贴或分配值到sheet2 b的第一个空单元格，然后是循环计数单元格.

解决方案

解决方案是

显式选项 私人子Worksheet_Change(按目标的ByVal目标)

将KeyCell视作范围 设置KeyCells = Range("H:H") 如果(Target.Value<>")和(IsNumeric(Target.Value))和(Target.Value> 0)和((Target.HasFormula)= False)然后

如果不是Application.Intersect(KeyCells，Range(Target.Address))那么没有 Dim X作为整数 Sheets("sheet2").激活 对于X = Sheets("sheet2").Range("G100").End(xlUp).Row To Sheets("sheet2").Range("G100").End(xlUp).Row + Target.Value-1 Sheets("sheet2").Range("B"& X +1).Value = Sheets("sheet1").Range("B"& Target.Row) Sheets("sheet2").Range("C"& X +1).Value = Sheets("sheet1").Range("C"& Target.Row) Sheets("sheet2").Range("D"& X +1).Value = Sheets("sheet1").Range("D"& Target.Row) Sheets("sheet2").Range("E"& X +1).Value = Sheets("sheet1").Range("E"&Target.Row)

Sheets("sheet2").Range("G"& X +1).Value =输入序列号" 下一个X 'MsgBox Target.Address MsgBox完成"& X 别的 万一 别的 MsgBox错误的值！您必须输入大于0的数字" 万一 结束

但是现在如果用户更改sheet1上的值，我如何更新sheet2中的行Range("H:H") 如果用户输入的值大于他输入的第一个值，我需要一种插入新行的方法. 或者 如果用户输入的值小于输入的值，我需要一种删除多余行的方法.

hope someone can help i have this code bellow working fine because i determined the range and exactly gave A1 numeric value as loop counter and starting point.

Private Sub Worksheet_Change(ByVal Target As Range)

    If (Range("A1") <> "") And (IsNumeric(Range("A1"))) And (Range("A1") > 0) Then
         Dim X As Integer
         If Not Intersect(Target, Range("A1")) Is Nothing Then
             For X = 1 To Range("A1").Value
                 Sheet4.Range("b" & X).Value = Range("A1").Value
             Next X
             MsgBox "done"
         Else
         End If
     Else
         MsgBox "no numeric"
     End If
End Sub

now

i want to expand this code above so when user fill sheet1 A1 by 5 then paste values to 5 cells in sheet2 starting from first empty cell in sheet2 eg: b1:b5 or b10:b15 respectifly. in next time i dont know in which cell in sheet1 column A will be filled may be A2' A3'A10'A80 or any A column cells so when it filled next time do the same thing loop for entered value times and paste or assign values to sheet2 b first empty cell and next to loop count cells.

解决方案

the solution is

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range Set KeyCells = Range("H:H") If (Target.Value <> "") And (IsNumeric(Target.Value)) And (Target.Value > 0) And ((Target.HasFormula) = False) Then

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then Dim X As Integer Sheets("sheet2").Activate For X = Sheets("sheet2").Range("G100").End(xlUp).Row To Sheets("sheet2").Range("G100").End(xlUp).Row + Target.Value - 1 Sheets("sheet2").Range("B" & X + 1).Value = Sheets("sheet1").Range("B" & Target.Row) Sheets("sheet2").Range("C" & X + 1).Value = Sheets("sheet1").Range("C" & Target.Row) Sheets("sheet2").Range("D" & X + 1).Value = Sheets("sheet1").Range("D" & Target.Row) Sheets("sheet2").Range("E" & X + 1).Value = Sheets("sheet1").Range("E" & Target.Row)

Sheets("sheet2").Range("G" & X + 1).Value = "Enter serial" Next X 'MsgBox Target.Address MsgBox "done" & X Else End If Else MsgBox "Wrong Value! You Must Enter Number greater Than 0 " End If End Sub

but now how can i update the rows in sheet2 if a user change the value on sheet1 Range("H:H") i need a way to insert new rows if the user entered greater value than the first he entered. or i need a way to delete extra rows if the user entered smaller value than the first he entered.

这篇关于使用循环将某些sheet1单元格的粘贴值分配或复制到sheet2的确切列中，该计数器是在列中输入的值，例如:A1单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！