在值更改时动态运行宏| VBA | Excel | [英] run macros dynamically on value change |VBA|Excel|

问题描述

我有工作表1和工作表

Sheet 1 => https://paste.pics/6d48d50d54592eb1bdcb31e727b44958

Sheet 1 => https://paste.pics/6d48d50d54592eb1bdcb31e727b44958

Sheet 2 => https://paste.pics/80f4230bf6819a80ed36e1b6415ece08

Sheet 2 => https://paste.pics/80f4230bf6819a80ed36e1b6415ece08

工作表1的值=> F5 =>'50'=>在工作表2中被引用=> E4 =>'50'

Value of Sheet 1 => F5 => '50'  => is referenced in Sheet 2  => E4 => '50'

当我在工作表1中输入值代替F5时=>在工作表2中值获得更改=> E4 =>自动

When i enter Value in Sheet 1 in place of F5 => Value gets Change in Sheet 2 => E4 => Automatically

现在的问题是我要打印工作表2的DATA 1和DATA 2 =>多少次=>代替工作表2显示多少值=> E4 [位置] =>代替列=>'I'和'J'

Now the issue is I want to print the DATA 1 and DATA 2 of Sheet 2 => That many times => how much value which is shown in place of Sheet 2 => E4 [Position] => in place of COLUMN => 'I' and 'J'

就像下面的输出所示: https://paste.pics/494c856d2908a83ca031ee20bb706a09

Like this Below Output :https://paste.pics/494c856d2908a83ca031ee20bb706a09

我的代码仅部分打印一个列输出，但需要打印'RAM'和'RAJ'=> COLUMN =>'I'和'J'

My code which partially print only one Column output but need to PRINT 'RAM' and 'RAJ' => COLUMN => 'I' and 'J'

模块:子mac()

Dim ws As Worksheet
Dim rDest As Range
Dim lCount As Long
Dim sValue As String

Set ws = ThisWorkbook.Sheets("Sheet2")
Set rDest = ws.Range("I2")

With ws.Range(rDest, ws.Cells(ws.Rows.Count, rDest.Column).End(xlUp))
    If .Row >= rDest.Row Then .ClearContents
End With

lCount = Val(ws.Range("E4").Value)
sValue = ws.Range("E8").Value

If lCount > 0 Then rDest.Resize(lCount) = sValue

End Sub

第1张代码:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Sheets("Sheet1").Range("F5"), Target) Is Nothing Then
       Call mac
    End If
End Sub

推荐答案

如果print表示仅填充单元格，则可以使用以下子项.修改代码以通过 Worksheet_Change 事件

If print means just filling the cells, then you can use following sub. Modify the code to adjust it with Worksheet_Change event

Sub FillNames()
Dim ws As Worksheet
Dim i, destLen As Long

Set ws = Worksheets("Sheet2")
destLen = ws.Range("E4")

    For i = 1 To destLen
        ws.Cells(i + 1, "I") = ws.Range("E8")
        ws.Cells(i + 1, "J") = ws.Range("E12")
    Next

Set ws = Nothing
End Sub

这篇关于在值更改时动态运行宏| VBA | Excel |的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！