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

[µo°Ý] «O¯d¨S¦³­«½ÆªºÄæ¦ì

ÁÂÁ¤j®a
¤Ó·P°Ê¤F
luhpro ¤j¤jªºµ{¦¡§Ú¤@¶}©l´N¦³¸Õ¹L
¬O§¹¥þ¨S°ÝÃDªº
¥u¬O¥Lªºµ{¦¡¤ÓÃø~§ÚµLªk®ø¤Æ
...
ssooi µoªí©ó 2020-12-3 20:54

¤ÓÃø......¡@O.O
§Ú(·~¾lªÌ)ªºµ{¦¡¤j³£¬O¶§¬K«¬ªº,
«Ü¤Ö²o§è¨ì²¤Æµ{¦¡½Xªº³¡¤À©O.

­n¤F¸Ñµ{¦¡«ç»ò¶]ªº,
¦³­Óº¡¤£¿ùªº¥\¯à¡GÀ˵ø->°Ï°ìÅܼƵøµ¡,
µ½¥Î³æ¨B¼Ò¦¡(F8)
¨Ã§â¥ªÃ䦳+ªº³£ÂI¤@¤U¥´¶}Æ[¹î¨C­Ó«ü¥O¹ï¨ä¦¨­û¤Î¤º§t­ÈªºÅܤÆ,
¦A·f°t©³¤U §Y®É¹Bºâµøµ¡ ¥Î ?ÅܼƦW ¨Ó¨ú­È°µ½T»{.

Dictionary ¤£¬O¤ÓÃøªºªF¦è,
¥¦¦³¨â­Ó³¡¤À Item »P key
Item=vD(key) ' ¤º§t­È = vD(Áä­È)

Cells «h¬O Àx¦s®æªºªí¥Ü§Î¦¡¤§¤@,
Range("B5")=Cells(5, 2) ' Range("Àx¦s®æ¦ì§}")=Cells(¦C¸¹, Ä渹)

IsEmpty ¨ç¼Æ«h¬OÀˬd¬A©·¤ºªº­È¬O§_¬°ªÅ,
§Æ±æ¹ï§A¦³©ÒÀ°§U.

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 8# luhpro


§Ú²ßºD¥Î GOTO , ¥¦¥i²¤Æ©Î´î¤Ö¨Ç§PÂ_,
¨Ò¦p:
IF A=1 AND B=2 AND C=3 THEN
  °õ¦æ°Ê§@
END IF
¨Ì±`²z, ¥¦­n¦P®É§PÂ_3­Ó, ³£²Å¦X¤F¤~°õ¦æ,

­Y¤À¶}§PÂ_
IF A<>1 THEN GOTO 100 '³o±ø¥ó¦¨¥ß, ¸õ¦Ü¤U­Ó°j°é, ¦Ó¤£¶·¦A¶i¦æB¤ÎCªº§PÂ_
IF B<>2 THEN GOTO 100
IF C<>3 THEN GOTO 100

·íµM¤]¥i¥ÎOR
IF A<>1 OR B<>2 OR C<>3 THEN GOTO 100
¦ý³o¤£½T©w¬O§_­nABC³£§PÂ_§¹¤F¤~¸õ¥X

µ{¦¡¼gªk, ¤j®a³£·|¾i¦¨­Ó¤H²ßºD, ¥u­n¤£¼vÅTµ{¦¡¹B¦æ®Ä²v, ¤j³£¯à±µ¨ü§a!!!

TOP

¦^´_  luhpro


§Ú²ßºD¥Î GOTO , ¥¦¥i²¤Æ©Î´î¤Ö¨Ç§PÂ_,
¨Ò¦p:
IF A=1 AND B=2 AND C=3 THEN
  °õ¦æ ...
­ã´£³¡ªL µoªí©ó 2020-12-4 17:23

¥H§A©ÒÁ|ªº¨Ò¤l¦bµ²ºc¤Æµ{¦¡·|¬O³o¼Ëªº§Î¦¡:
  For ...
    If A<>1 Then  '³o±ø¥ó¦¨¥ß, ¸õ¦Ü¤U­Ó°j°é, ¦Ó¤£¶·¦A¶i¦æB¤ÎCªº§PÂ_
      If B<>2 Then
        If C<>3 Then
          ......
       End If
     End If
   End If  
...
  Next

µ{¦¡¼gªk, ¤j®a³£·|¾i¦¨­Ó¤H²ßºD, ¥u­n¤£¼vÅTµ{¦¡¹B¦æ®Ä²v, ¤j³£¯à±µ¨ü§a!!!

¶â......
¬Oªº, ¨Ï¥Î¾A¦X¦Û¤vªº¤è¦¡´N¥i¥H¤F.

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-12-5 16:04 ½s¿è

¦^´_ 14# luhpro

¦pªGVBA¯à¦³ VB.Netªº Continue [For | Do]  »yªk¡A¤@¯ë¤H¥i¯à¤]¤£·|¥Î¨ìGoto

¨ä¹ê§Ú»{¬°Goto¤~¬O³Ì°ò¥»ªº»yªk¡A¦³¾Ç¹L²Õ¦X»y¨¥ ªº¤H³£ª¾¹D¡A«Ü¦h²Õ¦X»y¨¥³£¨S¦³For¡BDo °j°é

³£¬O§Q¥Î¼ÐÅÒ¸õ¦æ°õ¦æ¡A¨Ó¹F¦¨°j°é¥\¯à¡A§Ú§Q¥ÎVBA»yªk¨Ó¼ÒÀÀ²Õ¦X»y¨¥¼gFor ¡B Do°j°éªºÅÞ¿è

¦p¤Uµ{¦¡ °Ñ¦Ò



Sub ADD100Sum()
'­pºâ1¥[¨ì100ªº²Ö¥[­È
I = 0
N = 100
°j°é:
I = I + 1
S = S + I
If I < N Then GoTo °j°é
MsgBox "1¥[¨ì" & N & "ªºÁ`¦X= " & S
End Sub

Sub ADDTo30000up()
'­pºâ1¥[¨ìNªº²Ö¥[­È¡Aª½¨ì¶W¹L30000
'¦^¶ÇN­È
I = 0
Target = 30000
°j°é:
I = I + 1
S = S + I
If S < Target Then GoTo °j°é
MsgBox "1¥[¨ì'" & I & "'ªº²Ö¥[­È=" & S & "¡A­è¹L30000 "
End Sub
1

µû¤À¤H¼Æ

µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2020-12-8 01:58 ½s¿è

¦^´_ 15# n7822123
¤@¯ë¸g¾ú¹L5.25"ºÏºÐ¤ùªº¤HÀ³¸Ó¤]³£ª¾¹D²Õ¦X»y¨¥§a.(Á`ı±o¨º¨Ç¹CÀ¸µ{¦¡¤S¤p¤S¶W¦nª±ªº)
ÁÙ°O±o¨º®É«JºÆ¨gªº¶R²Õ¦X»y¨¥©MBasicªº®ÑÃä¬ÝÃä¾Ç,(¤@¥xPC­n7¸U¦h,¨º®ÉÁ٬ݤ£¨ì¤¤¤å©O)
¦ý§Ú¤]¥u¯àºâ¬O¸ÑªR¹Lª¾¹D¦ì¤¸²¾¦ì¬O¬Æ»ò·§©À½}¤F,
¯u­n»¡µ{¦¡³]­pÁÙ¬O¦³«Ü¦h­n¾Çªº¡A
³oºô¯¸¤W¥i«õªºÄ_¤Ó¦h¤F,¾Ç¤£§¹ªü......

Goto ·íµM¬O³Ì¦­´Áªº°ò¥»«ü¥O.
¥¦ªºÀ³¥Î¼u©Ê»P½d³ò«D±`¤j,
«o¤]®e©ö¦]¬°«D¹w´Á¹Bºâ¤l±a¤JÅÞ¿è±ø¥ó¦Ó¨«¤J»~°Ï,
©Ò¥H«á¨Ó°ª¶¥»y¨¥¤~·|³]­p¥X±M¦³ªº°j°é«ü¥O.

¹³§Ú¤@¯ë³£¬O¥ý¥´¦n¥~³ò¦¨¹ï«ü¥O¦A¦b¸Ì­±¥[«ü¥O, §Y:
¥ý¥´ Do ... While  ©Î¬O For...Next ¥ç©Î¬O With...End Withµ¥,
¦A¦b¤¤¶¡¥[¤W«ü¥O,
¦n³B¬O§@¥Î½d³ò¤£·|¿ù,
¤]¤£·|µo¥Í¹ïÀ³¤£¨ì©Î¤£¤p¤ß¥æ¿ù¦¨¹ï«ü¥O(¨Ò :  For ...  For...  Next...  Next)ªº±¡§Î.

µ²ºc¤Æµ{¦¡¬O¸û©ö¸ÑªR§PŪ¹ï·Q¾Ç²ßªº¤H¤]¸û¾A¦X.
·íµM,¦UºØ¤è¦¡»P«ü¥O¥u­n¯àµ½¥Î³£¬O«Ü¦nªº.

§Aªº¨Ò¤l¥Î Do...Loop Until °j°é´N¥i¥H§¹¦¨ªº³á:
  1. Do
  2.   I = I + 1
  3.   S = S + I
  4. Loop Until Not (I < N)
½Æ»s¥N½X
·íµM, While...Wend °j°é¤]¬O¨S°ÝÃDªº:
  1. While I < N
  2.   I = I + 1
  3.   S = S + I
  4. Wend
½Æ»s¥N½X

TOP

ÁÂÁ¦U¦ì«e½ú
¾Ç²ß¨ì«D±`¦h
ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-12-1 18:56 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:


°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, Z, A, i&, j%, R&, Y&, T$
'¡ô«Å§iÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO¦r¨å
Brr = Range([C1], [A65536].End(3))
'¡ô¥OBrrÅܼƬO¸Ë¤JÀx¦s®æ­Èªº¤Gºû°}¦C
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!±q2¨ìBrr°}¦C³Ì¤j¯Á¤Þ¦C¸¹
   T = Brr(i, 1) & "|" & Brr(i, 3)
   '¡ô¥OTÅܼƬO²Ä1Äæ»P²Ä3Äæ°}¦C­È¥H "|"²Å¸¹¦ê±µªº·s¦r¦ê
   Z(T) = Z(T) + 1: Z(T & "/r") = i
   '¡ô¥OTÅܼƬ°keyªºitem­È²Ö¥[1(³o¬O­n°O¿ý¦r¦ê²Õ¥X²{¹L´X¦¸)
   '¡ô¥OTÅܼƳs±µ"/r" ªº·s¦r¦ê¬°key,item¬O¯Á¤Þ¦C¸¹,¯Ç¤JZ¦r¨å¤¤

Next
For Each A In Z.Keys
'¡ô³]³v¶µ°j°é!¥OA¬OZ¦r¨å¸ÌªºKeys¤§¤@
   If Right(A, 2) = "/r" Or Z(A) > 1 Then GoTo A01
   '¡ô¦pªG¬O°O¿ý¦C¸¹©Î ¦r¦ê²Õ¥X²{¹L¦¸¤j©ó1 ªº³£¸õ¹L
   R = R + 1: Y = Z(A & "/r")
   '¡ô¥ORÅܼƲ֥[1 (³o¬O­nÅý²Å¦X±ø¥óªº¸ê®Æ©ñ¸mªº¦C¸¹)
   For j = 1 To 3: Brr(R, j) = Brr(Y, j): Next
   '¡ô¥O²Å¦X±ø¥óªº¸ê®Æ¼g¤J«ü©wªº°}¦C¦ì¸m
A01: Next
If R = 0 Then Exit Sub Else [J:L].ClearContents
'¡ô¦pªG¨S¦³²Å¦X±ø¥óªº¸ê®Æ!´Nµ²§ôµ{¦¡°õ¦æ,§_«h²M°£Âªºµ²ªG®æ¤º®e
[J2].Resize(R, 3) = Brr
'¡ô¥OÂX®iªºÀx¦s®æ°Ï°ì¥HBrr°}¦C­È¼g¤J,¶W¹L¸Ó½d³òªº°}¦C­È©¿²¤
[J1:L1] = [A1:C1].Value
'¡ô¥O·s¼ÐÃD¦ì¸m®æ­Èµ¥©ó ­ì¼ÐÃD­È
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 1# ssooi


¨âºØ¼gªk   ¦Û¤v¬D¤@­Ó
Sub t1()
o = Split("Provider=Microsoft.,,.0;Extended Properties=Excel ,,.0;Data Source=" & ThisWorkbook.FullName, ",")
Set cn = CreateObject("adodb.connection"): If Application.Version > 12 Then o(1) = "ACE.OLEDB.12": _
o(3) = 12: cn.Open Join(o, "") Else o(1) = "Jet.OLEDB.4": o(3) = 8: cn.Open Join(o, "")
    q = "SELECT a.§Ç¸¹, a.®Éµ{, a.µP¤l FROM [¤u§@ªí2$A1:C] as a "
q = q & "WHERE EXISTS ( SELECT 1 FROM [¤u§@ªí2$A1:C] AS b "
q = q & "               WHERE b.§Ç¸¹ = a.§Ç¸¹ AND b.µP¤l = a.µP¤l "
q = q & "               GROUP BY b.§Ç¸¹, b.µP¤l HAVING COUNT(*) = 1)"
[G:J].ClearContents: [G1].CopyFromRecordset cn.Execute(q)
End Sub




Sub t2()
o = Split("Provider=Microsoft.,,.0;Extended Properties=Excel ,,.0;Data Source=" & ThisWorkbook.FullName, ",")
Set cn = CreateObject("adodb.connection"): If Application.Version > 12 Then o(1) = "ACE.OLEDB.12": _
o(3) = 12: cn.Open Join(o, "") Else o(1) = "Jet.OLEDB.4": o(3) = 8: cn.Open Join(o, "")
    q = "SELECT a.§Ç¸¹, a.®Éµ{, a.µP¤l FROM [¤u§@ªí2$A1:C] AS a "
q = q & "WHERE ( SELECT COUNT(*) FROM [¤u§@ªí2$A1:C] AS b "
q = q & "        WHERE b.§Ç¸¹ = a.§Ç¸¹ AND b.µP¤l = a.µP¤l) = 1"
[G:J].ClearContents: [G1].CopyFromRecordset cn.Execute(q)
End Sub

TEST.zip (15.54 KB)

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2023-12-2 07:56 ½s¿è

¦^´_ 19# singo1232001



   Sub t5()
I = Split("Provider=Microsoft.,Jet.OLEDB.4,.0;Extended Properties=Excel ,8,.0;Data Source=", ",")
If Application.Version > 12 Then I(1) = "ACE.OLEDB.12": I(3) = 12
Set cn = CreateObject("adodb.connection"): cn.Open Join(I, "") & ThisWorkbook.FullName
   q = "SELECT a.§Ç¸¹, a.®Éµ{, a.µP¤l FROM [¤u§@ªí2$A1:C] AS a WHERE (  SELECT COUNT(*) "
q = q & "FROM [¤u§@ªí2$A1:C] AS b WHERE b.§Ç¸¹ = a.§Ç¸¹ AND b.µP¤l = a.µP¤l) = 1"
[G:J].ClearContents: [G1].CopyFromRecordset cn.Execute(q)
End Sub

§Úı±o «e¤T¦æªº¤è¦¡ À³¸Ó¬Oadodb.connection ©I¥s³Ì²µuªº¤@ºØ¼gªk¤F

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD