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

¤£¦Pªºµ{¦¡¼gªk

¤£¦Pªºµ{¦¡¼gªk

·Q½Ð±Ð¥H¤U³o¤@¬qµ{¦¡¬O§_¦³°õ¦æ°_¨Ó¤ñ¸û§Öªº¼gªk ?
§Ú¥u¬O·Q°Ñ¦Ò¬Ý¬Ý¤£¦Pªºµ{¦¡¼gªk.
·PÁÂ...
  For fcsty = 4 To put_rownum
            If .Cells(fcsty, "B") <> "" And Not .Cells(fcsty, "A") Like "*¦X­p" Then
               ' ¼W¥[§PÂ_ "B"Ä欰ªÅ¥Õ, ©Î¬O«O¯d "¦X­p" ªº¦C.
                If .Cells(fcsty, hid_colnum) < put_maxnum And .Cells(fcsty, hid_colnum) > put_minnum Then
                    .Rows(fcsty).Hidden = True
                End If
            End If
        Next
    End With


_________________________________________
¾ã¬qµ{¦¡
Sub ProductsQ1FP()
ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1").PivotFields("Group").ClearAllFilters
ActiveWindow.FreezePanes = False ' ¨ú®ø­áµ²µøµ¡
Dim put_rownum As Integer
Dim hid_colnum As Integer
ActiveSheet.Rows.Hidden = False '¨ú®ø©Ò¦³ªºÁôÂÃ
ActiveSheet.Range("c4").Select  '´å¼Ð©ñ¦bc4
ActiveWindow.FreezePanes = True '­áµ²µøµ¡
ActiveWindow.ScrollColumn = 3   '­áµ²¦C»PÄ檺µøµ¡
ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1").PivotFields("Group").AutoSort xlDescending, _
    "¥[Á` ªºQ1F-P"
put_rownum = ActiveSheet.Range("c2").Value  '³Ì«á¤@¦C
put_maxnum = ActiveSheet.Range("b1").Value  '¤j©ó
put_minnum = ActiveSheet.Range("b2").Value  '¤p©ó
hid_colnum = ActiveSheet.Range("f1").Value  '±Æ§ÇÄæ
With ActiveSheet
        .Cells(4, hid_colnum).Select            '´å¼Ð©ñ¸mÂI
        For fcsty = 4 To put_rownum
            If .Cells(fcsty, "B") <> "" And Not .Cells(fcsty, "A") Like "*¦X­p" Then
               ' ¼W¥[§PÂ_ "B"Ä欰ªÅ¥Õ, ©Î¬O«O¯d "¦X­p" ªº¦C.
                If .Cells(fcsty, hid_colnum) < put_maxnum And .Cells(fcsty, hid_colnum) > put_minnum Then
                    .Rows(fcsty).Hidden = True
                End If
            End If
        Next
    End With
End Sub
fangac

¦^´_ 1# fangsc

¤@¦¸ÁôÂÃ
  1. With ActiveSheet
  2.   For fcsty = 4 To put_rownum
  3.     If .Cells(fcsty, "B") <> "" And Not .Cells(fcsty, "A") Like "*¦X­p" Then
  4.        If .Cells(fcsty, hid_colnum) < put_maxnum And .Cells(fcsty, hid_colnum) > put_minnum Then
  5.           Rng = IIf(Rng = "", "A" & fcsty, Rng & "," & "A" & fcsty)
  6.        End If
  7.     End If
  8.   Next
  9. End With
  10. Rows("4:" & put_rownum).Hidden = False
  11. Range(Rng).EntireRow.Hidden = True
  12. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-6-17 13:38 ½s¿è

¦^´_ 2# register313


    °õ¦æ¦³¤@¨Ç°ÝÃD.
¬O¤£¬O»Ý­n«Å§iRngÅܼÆ, ¦ý§Ú¥[¤J¤FRngÅܼƫŧi,¤]µL®Ä.
·q½Ð«üÂI. ÁÂÁÂ.

Sales by Org.rar (875.75 KB)

fangac

TOP

Rng¦r¦êÅܼƤ§¦r¤¸¼Æ¨ü­­©ó255­Ó¦r¤¸
³y¦¨°õ¦æ¿ù»~,¤£ª¾¦³¦ó¤èªk¥i¸Ñ¨M?

TOP

¦^´_ 4# register313
  1. Sub ProductsQ1FP()
  2. Dim Rng As Range
  3. ' 12Q1Fcst Vs. Q1Plan
  4. ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1").PivotFields("Group").ClearAllFilters
  5. ActiveWindow.FreezePanes = False ' ¨ú®ø­áµ²µøµ¡
  6. Dim fcsty, put_rownum, hid_colnum, put_maxnum, put_minnum As Integer
  7. ActiveSheet.Rows.Hidden = False '¨ú®ø©Ò¦³ªºÁôÂÃ
  8. ActiveSheet.Range("c4").Select  '´å¼Ð©ñ¦bc4
  9. ActiveWindow.FreezePanes = True '­áµ²µøµ¡
  10. ActiveWindow.ScrollColumn = 3   '­áµ²¦C»PÄ檺µøµ¡
  11. ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1").PivotFields("Group").AutoSort xlDescending, _
  12.     "¥[Á` ªºQ1F-P"
  13. ' ¼Ï¯Ã¤ÀªR¥H¥[Á`ªºQ1F-P°µ±Æ§Ç
  14. put_rownum = ActiveSheet.Range("a1").Value  '³Ì«á¤@¦C
  15. put_maxnum = ActiveSheet.Range("b1").Value  '¤j©ó
  16. put_minnum = ActiveSheet.Range("b2").Value  '¤p©ó
  17. hid_colnum = ActiveSheet.Range("f1").Value  '±Æ§ÇÄæ
  18. With ActiveSheet
  19.   For fcsty = 4 To put_rownum
  20.     If .Cells(fcsty, "B") <> "" And Not .Cells(fcsty, "A") Like "*¦X­p" Then
  21.        If .Cells(fcsty, hid_colnum) < put_maxnum And .Cells(fcsty, hid_colnum) > put_minnum Then
  22.          If Rng Is Nothing Then
  23.             Set Rng = .Range("A" & fcsty)
  24.         Else
  25.             Set Rng = Union(Rng, .Range("A" & fcsty))
  26.         End If
  27.        End If
  28.     End If
  29.   Next
  30.   .Rows("4:" & put_rownum).Hidden = False
  31. End With
  32. Rng.EntireRow.Hidden = True
  33. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# GBKEE

¤@¦¸©Ê°Ê§@

ªk1:»yªk²³æ,¦ýRng¦r¦ê¨ü­­255­Ó¦r¤¸(Àx¦s®æ½d³ò¨ü­­¨î)
Rng = IIf(Rng = "", "A" & fcsty, Rng & "," & "A" & fcsty)

ªk2:»yªk¸ûªø,¦ýRngÀx¦s®æ½d³ò¤£¨ü­­¨î
If Rng Is Nothing Then
    Set Rng = .Range("A" & fcsty)
Else
    Set Rng = Union(Rng, .Range("A" & fcsty))
End If
  
¦p¦¹¸ÑÄÀ¬O§_¥¿½T

TOP

¦^´_ 6# register313
½d³ò¦p¥i¹wª¾    Rng = IIf(Rng = "", "A" & fcsty, Rng & "," & "A" & fcsty) ¬O²³æ¨Ç

TOP

·PÁ¤G¦ì«ü¾É.
µ{¦¡¬Ý¤£¤ÓÀ´,°ò¥»¥\ÁÙ¬O»Ý­n¦h½m²ß,¦n¦n²z¸Ñ¤@¤U,¥i¥HÀ³¥Î¦b§Oªº¦a¤è.
fangac

TOP

        ÀR«ä¦Û¦b : ¹ï¤÷¥À­nª¾®¦¡A·P®¦¡B³ø®¦¡C
ªð¦^¦Cªí ¤W¤@¥DÃD