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

rangeÂ÷©_°ÝÃD

rangeÂ÷©_°ÝÃD

¤£¦n·N«ä¡A·Q°Ý¤@­Ó«ÜÂ÷©_ªº°ÝÃD
´N¬O«ü©w³o­Ó¹ï¶H®É
Range("A" & i * 100 + 88 & ":l100")
©Ò§e²{ªº½d³ò«o¬O
$A$100:$L$188

¦ý«ü©w³o­Ó¹ï¶H
Range("A" & i * 100 + 88 & ":l1000")
©Ò§e²{ªº½d³ò¤S¬O
$A$188:$L$1000

½Ð°Ý°ÝÃD¥X¦b­þ¸Ì¡H

§Aªº¬O mac ¨t²Î
¬O§_»P¦¹¦³Ãö?

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-7-4 11:40 ½s¿è

¦^´_ 1# HSINLI


    ³o¼Ë¨S°ÝÃD°Ú¡I°²¦p½d³òA1:C10¦n¤F¡A§A¤]¥i¥H¼Ð¥ÜA10:C1ªº¨âºØ¼gªk³£¹ï
½d³ò¦b¤@¯ë¼gªk¼Æ¦r¤pªº¦C·|¦b«e­±¡A¼Æ¦r¤jªº·|¦b«á­±
¨Ì$A$100:$L$188ªº¼gªk©M$A$188:$L$100¨ä¹ê¬O¤@¼Ëªº¡I
¦Ó$A$188:$L$1000¡A188ªº¼Æ¦r¤ñ1000¤p©Ò¥H¹q¸£ª½±µ§PÂ_¬°$A$188:$L$1000¡A¦ý­Y¥Î¤H¤u¼gªº¤]¥i¥H¼g$A$1000:$L$188
  1. Public Sub test()
  2. Range("A10:c1").Select
  3. Range("A1:c10").Select
  4. End Sub
½Æ»s¥N½X
¤W­±¥N½Xµª®×µ²ªG³£¬O¤@¼Ëªº¡I

TOP

¦^´_ 3# lpk187

¦³¹D²z
¥Ø«e±o¨ìªºµ²½×¬O
EXCEL ·|¦Û°Ê§â¤pªº­È©ñ«e­±, ¤jªº­È©ñ«á­±
´N¯à°÷¦P¤@ ¼Ð·Ç
¥Øªº¥i¯à¬O³o¼Ë¤~¯à¤¬¤ñ

¨Ò¦p
¥H¤U¨ä¹ê¬O¬Û¦P°Ï°ìªº  
Range("A2: C100")  EXCEL  ¦pªG¦^¶Ç "A2:C100"
Range("A100: C2")  EXCEL  ¦pªG¦^¶Ç "A100:C2"
IF"A2:C100" <> "A100:C2"¡@Then
    Msgbox "³o¬O¤£¦Pªº¿ï¨ú°Ï"
End iF
­nÁקK³oºØ¿ù»~,  EXCEL ´N¥²¶·²Î¤@¼Ð·Ç

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-7-4 11:59 ½s¿è

¦^´_ 4# jackyq


IF"A2:C100" <> "A100:C2"¡@Then
    Msgbox "³o¬O¤£¦Pªº¿ï¨ú°Ï"
End iF
¤W­±¥N½X¬O¨â²Õ¤£¦Pªº¦r¦ê
½d³ò«h¬O¦ì¸m¡A¬O¬Û¦Pªº¡I¦p¤U¡G
If Range("A10:c1").Address <> Range("A1:c10").Address Then
    MsgBox "³o¬O¤£¦Pªº¿ï¨ú°Ï"'
End If
©Ò¥H³o¤£¬O¿ù»~¡I

TOP

¦^´_ 5# lpk187

§A»~·|§Úªº·N«ä
ª©¤j¥L¨ä¹ê´N¬O°Ý
Range("A10:c1").Address = ¬°¦ó EXCEL ¤£¬O¶Ç¦^ "A10:c1" ¤Ï¦Ó¶Ç¦^  "A1:c10"
Range("A1:c10").Address = "A1:c10"

§Ú­n¸ò¥L»¡ Range("A10:c1") ©M Range("A1:c10") ¨ä¹ê¬O¬Û¦Pªº¿ï¨ú°Ï
¦pªG EXCEL ¥¦³]­p¦¨ ¤@­Ó¶Ç¦^ "A1:c10" ¥t¤@­Ó«o¶Ç¦^"A10:c1"
±N¾É­Pµo¥Í¥H¤Uªº¿ù»~²{¶H
IF "A1:c10" <> "A10:c1"¡@Then   (  if Range("A1:c10").Address  <> Range("A10:c1").Address  then )
    Msgbox "³o¬O¤£¦Pªº¿ï¨ú°Ï"
End iF

©Ò¥H EXCEL ¤~·|§â Range("A10:c1").Address = §ï¦¨¶Ç¦^  "A1:c10"
³o¼Ë´N¤£·|¥X²{¥H¤Wªº°ÝÃD

ª©¤j¥L´N¬OµLªk²z¸Ñ¬°¦ó  Range("A10:c1").Address  ¬°¦ó¤£¬O¶Ç¦^ "A10:c1" ¦Ó¬O¶Ç¦^ "A1:c10"
§Ú­n§i¶D¥Lªº¬O¦pªG  Range("A10:c1").Address   ¶Ç¦^ªº¬O  "A10:c1"
±N·|¤Þµo¤U¦Cªº¿ù»~
IF Range("A1:c10").Address  <> Range("A10:c1").Address  Then
    Msgbox "³o¬O¤£¦Pªº¿ï¨ú°Ï"
End iF
³o¤]´N¬O§Ú»{¬° EXCEL ¬°¦ó¤£¶Ç¦^ "A10:c1" ªº­ì¦]

§A...¨S¬Ý²M·¡§Úªº·N«ä

TOP

¤£¦n·N«ä, ¤W­±¼g±o¤]ÂIÂø
·Q­×§ï¤w¸g¶W¹L3¤ÀÄÁµLªk­×§ï


§A»~·|§Úªº·N«ä
ª©¤j¥L´N¬OµLªk²z¸Ñ¬°¦ó  Range("A10:c1").Address  ¬°¦ó¤£¬O¶Ç¦^ "A10:c1" ¦Ó¬O¶Ç¦^ "A1:c10"
§Ú­n§i¶D¥Lªº¬O¦pªG  Range("A10:c1").Address   ¶Ç¦^ªº¬O  "A10:c1"
±N·|¤Þµo¤U¦Cªº¿ù»~
IF Range("A1:c10").Address  <> Range("A10:c1").Address  Then
    Msgbox "³o¬O¤£¦Pªº¿ï¨ú°Ï"
End iF
§â Range("A1:c10").Address  ª½±µ¥H­È¨Ó´À¥N´NÅܬ°¦p¤U
IF ("A1:c10") <> "A10:c1" Then
    Msgbox "³o¬O¤£¦Pªº¿ï¨ú°Ï"
End iF

³o¤]´N¬O§Ú»{¬° EXCEL ¬°¦ó¤£¶Ç¦^ "A10:c1" ªº­ì¦]

§A...¨S¬Ý²M·¡§Úªº·N«ä

TOP

³á³á¡I¤F¸Ñ¤F¡I
©Ò¥H»¡excel·|¦Û°Ê±N¼Æ¦r¤p±o´«¨ì«e¤è¡A¦ý¬O©Ò¿ï¨úªº½d³ò¤@¼Ë¨S¦³Åܧó
¬O³o¼Ë¶Ü¡H¡H

TOP

¤£¦n·N«ä¡A·Q­n¦A¸ß°Ý¤@¤U
range³o¼Ëªº¼gªk¥X¤F¤°»ò°ÝÃD¶Ü¡H
Range("a" & i - 1 * 100 + 88 & ":l " & i * 100 + 88).Copy j
¬°¤°»ò¨C¦¸°õ¦æªº®É­Ô³£¥X²{1004¿ù»~

¾ã²Õcode¨£ªþ¥ó¡A¥D­n¬O­n¥Îvba¥sie¡A½Æ»s¶K¤W¨ä¤º®e¦Üexcel
(·|³o¼Ë¾Þ§@¬O¦]¬°¸Óºô§}µLªk¥Îquerytable¤U¸ü¡^
  1. Sub Â^¨úyahoofinanceetf()

  2. Dim j As Range
  3. Dim i As Integer
  4. Dim url As String

  5.     Cells.Clear
  6.    
  7.     Application.ScreenUpdating = False
  8.     Set ie = CreateObject("internetexplorer.application")

  9. For i = 1 To 16 '±ý½Æ»s¤U¸üªººô­¶­¶¼Æ¦³16­¶
  10.     With ie
  11.         .Visible = False
  12.         .Navigate "http://finance.yahoo.com/etf/lists/?mod_id=mediaquotesetf&tab=tab4&scol=avgcap&stype=desc&rcnt=100&page=" & i

  13.     Do While .ReadyState <> 4
  14.         DoEvents
  15.     Loop
  16.         .ExecWB 17, 2
  17.         .ExecWB 12, 2
  18.    End With
  19.   If i = 1 Then '¦pªG¬O²Ä¤@­¶ªº¸Ü±qA1¶}©l¶K
  20.         Range("A1").Select
  21.         ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
  22.                 False, NoHTMLFormatting:=True
  23.    
  24.     Range("a87:l187").Copy Range("a1") '¦]¬°¬O¥þ­¶½Æ»s¬G¥u±N©Ò»Ýªºªí®æ½Æ»s¶K¤W¨ì«ü©w¦ì¸m
  25.     Range("a102:l302").Clear '°£¥H¤W©Ò»Ý¥~¤§«áªº¤º®e²M°£

  26. Else '¦pªG¬O²Ä¤G­¶¤§«áªº¤º®e
  27.      Set j = Cells(Rows.Count, "a").End(xlUp).Offset(1) '±Nrange J«ü©w¦b¨Ï¥Î¹LÀx¦s®æªº¤U¤@­ÓªÅ¥ÕÀx¦s®æ
  28.       j.Select
  29.       ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
  30.                 False, NoHTMLFormatting:=True
  31.    Range("a" & i - 1 * 100 + 88 & ":l " & i * 100 + 88).Copy j  '±N©Ò»Ýªº¸ê®Æ¶K¦Ürange j
  32.    j.EntireRow.Delete '±N¨º¤@¦æ¾ã¦æ§R°£¡A¦]¬°¬O¼ÐÃD¦C¡A«e­±¤w¦³¦P¼Ë¤º®e
  33.    Range("a" & i * 100 + 2 & ":l65536").Clear '°£©Ò»Ý³¡¥÷¡A¨ä¾l¤º®e²M°£
  34.    Set j = Nothing '²M°£jªº½á­È¡A¤U¦¸´`Àô¦A­«·s©w¸q·sªºrange j
  35.   End If
  36. Next i

  37. ie.Quit

  38. End Sub
½Æ»s¥N½X

TOP

i - 1 * 100 À³¸Ó¬O¼g¦¨   (i - 1) * 100

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦¨´N¤@¤Á¡j®É¶¡¥i¥H³y´N¤H®æ¡A¥i¥H¦¨´N¨Æ·~¡A¤]¥i¥HÀx¿n¥\¼w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD