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

½Ð±Ð½Æ»s¨ì¤£¦Pªº¤u§@ªíµLªk°_§@¥Î ?¦p¦ó­×§ï?

OFFSET

¿ï¾Ü¬Y½d³ò¡A¦p¦ó¨D±ooffset ªº­È?
¨Ò¦p  A1:B2 §YA1 ²¾¨ì B2 «hµª®×¬° OFFSET(1,1)
Sub t()
    Dim rng As Range
    Set rng = Selection
???= rng.Address
  End Sub

TOP

¦^´_ 1# t8899
¬O³o¼Ë¶Ü?
  1. Option Explicit
  2. Sub EX()
  3.     Dim Rng As Range
  4.     With [a1:b21]
  5.         Set Rng = .Offset(.Rows.Count - 1, .Columns.Count - 1)
  6.         MsgBox Rng.Address
  7.     End With
  8. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  t8899
¬O³o¼Ë¶Ü?
GBKEE µoªí©ó 2014-8-24 10:19


¥¿½T¡AÁÂÁÂ!

TOP

½Ð±Ð½Æ»s¨ì¤£¦Pªº¤u§@ªíµLªk°_§@¥Î ?¦p¦ó­×§ï?

Sub test() '½Ð¥Îmouse¥ý¿ï¾Ü½Æ»sªº½d³ò
    Dim rng As Range, a, z, r, rr
    Set rng = Selection
z = InputBox("¿é¤J¶}©l¦ì§}")
Set r = Range(z).Offset(rng.Rows.Count - 1, rng.Columns.Count - 1)
Set rr = Range(z)
ActiveSheet.Range(rr.Address, r.Address) = Selection.Formula
End Sub

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-8-25 06:00 ½s¿è

¦^´_ 4# t8899

Sub test() '½Ð¥Îmouse¥ý¿ï¾Ü½Æ»sªº½d³ò
    Dim rng As Range, a, z, r, rr
    Set rng = Selection   '³o¥²¬OActiveSheetªºSelection
z = InputBox("¿é¤J¶}©l¦ì§}")
Set r = Range(z).Offset(rng.Rows.Count - 1, rng.Columns.Count - 1)
Set rr = Range(z)
'ActiveSheet.Range(rr.Address, r.Address) = Selection.Formula   '©Ò¥H³o¤£¬O½Æ»s¨ì¤£¦Pªº¤u§@ªíªº
Sheets("TEST").Range(rr.Address, r.Address) = Selection.Formula   '¬O³o¼Ë¶Ü?
End Sub
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  t8899

Sub test() '½Ð¥Îmouse¥ý¿ï¾Ü½Æ»sªº½d³ò
    Dim rng As Range, a, z, r, rr
    Set rn ...
GBKEE µoªí©ó 2014-8-25 05:53

  Set rng = Selection   '³o¥²¬OActiveSheetªºSelection
³o¤@¥y¥i¥H§ó§ï¶Ü? ¦pªGµL¸Ñ¡A¦A¼W¥[¤@­Óinbox ¿é¤J¿ï¾Üªº¤u§@ªí¥i¥H¶Ü??

TOP

¦^´_ 6# t8899
¦³Application,     InputBox ¬°¤èªk,
¨S¦³Application,  InputBox ¬°¨ç¼Æ,
  1. Option Explicit
  2. Sub Ex()
  3.     Dim rng As Range, z As Range
  4.     Set rng = Selection
  5.     Set z = Application.InputBox("¿é¤J¶}©l¦ì§}", Type:=8) '¥i¦b¦U¤u§@ªí¤¤²¾°Ê,¿ï¾ÜÀx¦sÎÝ
  6.     Set z = z.Offset(rng.Rows.Count - 1, rng.Columns.Count - 1)
  7.     z.Formula = rng.Formula   '¬O³o¼Ë¶Ü?
  8. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ t8899 ©ó 2014-8-25 06:43 ½s¿è
¦^´_  t8899
¦³Application,     InputBox ¬°¤èªk,
¨S¦³Application,  InputBox ¬°¨ç¼Æ,
GBKEE µoªí©ó 2014-8-25 06:23


¥i¥H, ¦ý¥u¯àÂлs¤@­ÓÀx¦s®æ

TOP

¦^´_  t8899
¦³Application,     InputBox ¬°¤èªk,
¨S¦³Application,  InputBox ¬°¨ç¼Æ,
GBKEE µoªí©ó 2014-8-25 06:23


Àɮפ¤ Book1.rar (8.46 KB)
¥Ø¼Ð¤£¹ï
1.¿ï¾Üsheet1 c1-c3 ¥Ø¼Ð sheet2 h1 µ²ªG sheet1 c1¶K¨ì sheet2 h3 ¨ä¾lªÅ¥Õ
¥¿½T¥Ø¼ÐÀ³¬° sheet2 h1-h3 Offset(rng.Rows.Count - 1, rng.Columns.Count - 1)¥¢®Ä ?
2.¿ï¾Üsheet1 c1-c3 ¥Ø¼Ð sheet2 h1-h3 µ²ªG  sheet1 c1¶K¨ì sheet2 h3-h5
¥¿½T¥Ø¼ÐÀ³¬° sheet2 h1-h3

TOP

¦^´_ 9# t8899
¬O³o¼Ë¶Ü?
  1. Option Explicit
  2. Sub Ex()
  3.     Dim rng As Range, z As Range
  4.     Set rng = Selection
  5.     Set z = Application.InputBox("¿é¤J¶}©l¦ì§}", Type:=8) '¥i¦b¦U¤u§@ªí¤¤²¾°Ê,¿ï¾ÜÀx¦sÎÝ
  6.     If z Is Nothing Then Exit Sub
  7.     If rng.Rows.Count <> z.Cells.Rows.Count Or rng.Columns.Count <> z.Cells.Columns.Count Then
  8.        '¥Ø¼Ð»P¨Ó·½ªºøó¼Æ,¦C¼Æ¦³¤£¤@¼Ë.
  9.        z.Cells(1).Resize(rng.Rows.Count, rng.Columns.Count).Formula = rng.Formula
  10.     Else  '¤Ï¤§¥Ø¼Ð»P¨Ó·½ªºøó¼Æ,¦C¼Æ¬Û¦P
  11.         z.Formula = rng.Formula
  12.     End If
  13. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD