ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] VBA°ÝÃD: ³]©w½d³ò­È¦P®ÉÅÜ°Ê®É,­n¦Û°Ê±a¥X¤½¦¡

[µo°Ý] VBA°ÝÃD: ³]©w½d³ò­È¦P®ÉÅÜ°Ê®É,­n¦Û°Ê±a¥X¤½¦¡

¦pªþ¥ó
°ÝÃD1
°²³]­n³]©w¦bA1:A5ªº½d³ò­È ÅÜ°Ê®É
­n¦Û°Ê±a¥X BÄ椽¦¡
¦ý¹ê»Ú¸Õ,AÄæ¤@­Ó¤@­Ókey ¥i¥H±a¥XBÄ椽¦¡
¦ý­Y¦P®É¶K¤WA1~A5ªº­È®É, ¤½¦¡¥u·|¶]B1´N°±¤î¤F  @-@
µLªk¦P®É B1~B5 ³£±a¥X¤½¦¡
½Ð°Ý¥H¤UVBA ­n«ç»ò§ï¤~¯à¦P®ÉA¶KB¦P¥þ³¡±a¥X¤½¦¡
====================
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("A1:A5")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
    Cells(Target.Row, "B") = "=A" & Target.Row & "*5"
    End If
End Sub
=================================
°ÝÃD2
¦pªG­n¼g¦¨
¤@¼ËA1:A5ªº½d³ò­È ÅÜ°Ê®É
"¦b¤£¦P¤u§@ªí (ex ¤u§@ªí2 )" ¦Û°Ê±a¥XBÄ椽¦¡
'À³¸Ó«ç»ò¼g@-@?

test.zip (13.15 KB)

ªþ¥ó

50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

¦^´_ 1# renzhi2020
¦³ÃöExcel VBA ¥i¨ì Excelµ{¦¡°Ï µo°Ý
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Dim KeyCells As Range, E As Range
  4.     Set KeyCells = Range("A1:A5")
  5.     If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
  6.         For Each E In KeyCells
  7.             If IsNumeric(E) Then   '¬O¼Æ­È
  8.                 Sheets("µ²ªG").Cells(E.Row, "B") = E * 5 'µ¹­pºâ«áªº­È
  9.                 '*****  µ¹¤½¦¡  ****"
  10.                 ' Sheets("µ²ªG").Cells(E.Row, "B") = "=" & E.Address(, , , 1, 1) & "*5"
  11.              Else
  12.                 Sheets("µ²ªG").Cells(E.Row, "B") = ""
  13.             End If
  14.         Next
  15.     End If
  16. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ JasonChen576 ©ó 2018-1-29 13:13 ½s¿è

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
        Set KeyCells = Range("A1:A5")
   
    Dim myShtName As String
        myShtName = KeyCells.Worksheet.Name

    Dim myRowsNum As Integer
        myRowsNum = Selection.Rows.Count
            
    Dim myShtµ²ªG As Worksheet
        Set myShtµ²ªG = Worksheets("µ²ªG")
   
    Dim myAddressOfTarget As String
   
    Dim i As Integer
   
    If Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then Exit Sub
   
    For i = 0 To myRowsNum - 1 - Target.Row
        myAddressOfTarget = Target.Resize(1).Offset(i, 0).Address(0, 0, xlA1, 1, 1)
        myShtµ²ªG.Cells(Target.Row + i, "B") = "=" & myAddressOfTarget & "*5"
    Next i
End Sub

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD