×

Loading...
Ad by
  • 予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！
Ad by
  • 予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！

@PEI

Try out the following code;

maile(FinchEast)
本文发表在 rolia.net 枫下论坛In sheet 3, put;
H1: product name you wish
J1: color code you wish
L1: quality level you wish

Then run the following code.

****************************************8

Sub PickupCorrectProduct()
Dim ProductName, ColorCode, QualityLevel As String
Dim TotalLines As Integer
Dim i As Integer
Dim LineNoSheet1 As Integer
Dim LinesFound As Integer


LineNoSheet1 = 2
LinesFound = 0

ProductName = Trim(Sheet3.Range("H1")) 'get the desired product name
ColorCode = Trim(Sheet3.Range("J1")) ' get the desired color code
QualityLevel = Trim(Sheet3.Range("L1")) ' get the desired quality level

TotalLines = Sheet3.Range("A2").CurrentRegion.Rows.Count 'get the total lines

If ProductName = "" Then
MsgBox "Void product name!"
Beep
Exit Sub
ElseIf ColorCode = "" Then
MsgBox "Void color code!"
Beep
Exit Sub
ElseIf ProductName = "" Then
MsgBox "Void quality level!"
Beep
Exit Sub
End If


For i = 2 To TotalLines
If Sheet3.Cells(i, 1) = ProductName And Sheet3.Cells(i, 2) = ColorCode And Sheet3.Cells(i, 4) = QualityLevel Then
Sheet1.Cells(i, 1) = Sheet3.Cells(i, 1)
Sheet1.Cells(i, 2) = Sheet3.Cells(i, 2)
Sheet1.Cells(i, 3) = Sheet3.Cells(i, 3)
Sheet1.Cells(i, 4) = Sheet3.Cells(i, 4)
LineNoSheet1 = LineNoSheet1 + 1
LinesFound = LinesFound + 1
End If
Next i

Sheet1.Cells(LineNoSheet1, 1) = "Product:"
Sheet1.Cells(LineNoSheet1, 2) = ProductName
Sheet1.Cells(LineNoSheet1, 3) = "Color:"
Sheet1.Cells(LineNoSheet1, 4) = ColorCode
Sheet1.Cells(LineNoSheet1, 5) = "Quality:"
Sheet1.Cells(LineNoSheet1, 6) = QualityLevel
Sheet1.Cells(LineNoSheet1, 7) = "Lines Found:"
Sheet1.Cells(LineNoSheet1, 8) = LinesFound

End Sub更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#1048051@0)
Last Updated: 2003-2-18
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 枫下家园 / 电脑用户 / 怎样把Excel中Sheet3中Auto Filter的结果(唯一)自动赋值到Sheet1里Range("A1:H1")?或者怎样在Sheet1里做Sheet3里数据的Auto filter? -goodgoodstudying(daydayup); 2003-2-17 {78} (#1047109@0)
    不用auto filter 用VBA也行，关键是怎样把Sheet3里的数据list, sort 出来。先谢了。
    • Can you elaborate a bit more? an example will help us understand the issue. -schen(欲语还休); 2003-2-18 (#1047475@0)
      • Thanks. Here comes the examle, -goodgoodstudying(daydayup); 2003-2-18 {1083} (#1047937@0)
        本文发表在 rolia.net 枫下论坛Let's say in sheet3, we have the worksheet,
        Sheet3
        Name color code notes
        product1 white 1200-1403 good quality
        product1 white 1200-1404 good quality
        product1 white 1200-1405 lower quality
        product1 white 1200-1406 acceptable quality
        product1 yellow 1200-1403 good quality
        product1 yellow 1200-1404 good quality
        product1 yellow 1200-1405 lower quality
        product1 yellow 1200-1406 acceptable quality
        product2 white 1300-2101 good quality
        product2 white 1300-2102 lower quality
        product2 white 1300-2103 acceptable quality
        product2 white 1300-2104 lower quality
        product2 yellow 1300-4101 acceptable quality
        product2 yellow 1300-4102 good quality
        product2 yellow 1300-4103 lower quality
        product2 yellow 1300-4104 acceptable quality

        I'd like to use these data in sheet1 and sort out the right one from sheet3. For example, I want to check out product2->white->1300-2102 in sheet1.Range("A2:D2"). How can I achieve this goal ? Keep in mind that no one can really remember the code. We go by product, color, and quality.

        Thank you all again for your valuable input.更多精彩文章及讨论，请光临枫下论坛 rolia.net
        • Try out the following code; -maile(FinchEast); 2003-2-18 {1780} (#1048051@0)
          本文发表在 rolia.net 枫下论坛In sheet 3, put;
          H1: product name you wish
          J1: color code you wish
          L1: quality level you wish

          Then run the following code.

          ****************************************8

          Sub PickupCorrectProduct()
          Dim ProductName, ColorCode, QualityLevel As String
          Dim TotalLines As Integer
          Dim i As Integer
          Dim LineNoSheet1 As Integer
          Dim LinesFound As Integer


          LineNoSheet1 = 2
          LinesFound = 0

          ProductName = Trim(Sheet3.Range("H1")) 'get the desired product name
          ColorCode = Trim(Sheet3.Range("J1")) ' get the desired color code
          QualityLevel = Trim(Sheet3.Range("L1")) ' get the desired quality level

          TotalLines = Sheet3.Range("A2").CurrentRegion.Rows.Count 'get the total lines

          If ProductName = "" Then
          MsgBox "Void product name!"
          Beep
          Exit Sub
          ElseIf ColorCode = "" Then
          MsgBox "Void color code!"
          Beep
          Exit Sub
          ElseIf ProductName = "" Then
          MsgBox "Void quality level!"
          Beep
          Exit Sub
          End If


          For i = 2 To TotalLines
          If Sheet3.Cells(i, 1) = ProductName And Sheet3.Cells(i, 2) = ColorCode And Sheet3.Cells(i, 4) = QualityLevel Then
          Sheet1.Cells(i, 1) = Sheet3.Cells(i, 1)
          Sheet1.Cells(i, 2) = Sheet3.Cells(i, 2)
          Sheet1.Cells(i, 3) = Sheet3.Cells(i, 3)
          Sheet1.Cells(i, 4) = Sheet3.Cells(i, 4)
          LineNoSheet1 = LineNoSheet1 + 1
          LinesFound = LinesFound + 1
          End If
          Next i

          Sheet1.Cells(LineNoSheet1, 1) = "Product:"
          Sheet1.Cells(LineNoSheet1, 2) = ProductName
          Sheet1.Cells(LineNoSheet1, 3) = "Color:"
          Sheet1.Cells(LineNoSheet1, 4) = ColorCode
          Sheet1.Cells(LineNoSheet1, 5) = "Quality:"
          Sheet1.Cells(LineNoSheet1, 6) = QualityLevel
          Sheet1.Cells(LineNoSheet1, 7) = "Lines Found:"
          Sheet1.Cells(LineNoSheet1, 8) = LinesFound

          End Sub更多精彩文章及讨论，请光临枫下论坛 rolia.net
          • Thank you, Maile. I'm trying on your codes. Will let you know if I figure it out. -goodgoodstudying(daydayup); 2003-2-18 (#1049126@0)
            • It works great ! However, I still need something that can list and select. List box, combobox, or whatever. -goodgoodstudying(daydayup); 2003-2-18 {150} (#1049174@0)
              I can't ask my boss to input the "product name", "Color", and so on. In case he mis-spells a word...The whole idea is to make the analysis easy.
              • If you can make sure the result will be only 1 row then the rest will be easy. You just copy the second row in sheet3 to sheet1 using part of the code you previously developed. -schen(欲语还休); 2003-2-19 (#1049505@0)
                • set the auto filters on and let your boss select from the filters. However, the best way is to make a window form for your boss to interact and you use VBA to move data around. -schen(欲语还休); 2003-2-19 (#1049515@0)
              • That will cost you an arm and a leg. -maile(FinchEast); 2003-2-20 {64} (#1051981@0)
                It takes a bit time. I will look into it and post here sometime.
                • Great thanks! I'm looking forward to your codes. By the way, do you think using database is better than just Excel? I've no experience on Database at all. -goodgoodstudying(daydayup); 2003-2-20 (#1053086@0)

More Topics

  • 你怎么看？‘’AR眼镜终将取代智能手机‘’
  • 请教现在回国想看yahoo email (Gmail) 啥的必须用VPN吗？谢谢！
  • 我买了一个laptop，他说还送一个好像是usb类的外存。我在包装盒里就找到这个东西，谁知道这是什么东西，怎么用？谢谢🙏
  • 微信用Facebook注册，是不是不要电话号码了？
  • 我现在的手机计划写着UNLIMITED international text and picture messaging。是不是到欧洲美国等不需要开roaming也可以接到text messages?
    • 枫下论坛主坛 /  枫下家园 /  电脑用户