Posts by whatisVBA

    Re: left function compile error


    Unfortunately I can't upload the file as it contains confidential information. Is it possible for me to use a different function such as find? The cells I'm looking at contain a 7 digit number. If it starts with 945 I want to delete the row.

    Re: left function compile error


    So I'm using Excel 2007. I searched my modules for another function called "left" and I couldn't find anything. The worksheet I'm using could have been created in a different version of excel. I tried capitalizing the L and it had no effect. :( . I don't know what a UDF is.... user defined function?


    Thanks for the responses!

    Hi, this code makes sense to me but it makes VB angry.



    I get a compile error, highlighting the word "left", saying, "wrong number of arguments or invalid property assignment".
    I've used this code before but this is the first time I'm using it with the left function.


    Thank You!

    Hi, I'm using this VBA code to search through a range and to delete entries that have the word "HOLD" in column E. The problem is that it appears to be "skipping" rows...I know this is caused by the row numbers being offset when the delete/xlUP happens. How can I correct this?


    Code
    1. Sub expFilterHLD()
    2. Dim hld As Long
    3. For hld = 15 To Sheets("Expedite Report").Range("A15").CurrentRegion.Rows.Count + 13
    4. If Sheets("Expedite Report").Range("E" & hld) = "HOLD" Then
    5. Rows(hld & ":" & hld).Select
    6. Selection.Delete Shift:=xlUp
    7. End If
    8. Next
    9. End Sub



    Thank You!

    Hi, I did this exercise years ago and for some reason cannot remember how I did it.


    I have a list of about 10000 parts. Each part has a letter assigned to it in column B. Column C has a price. I need to calculate a new price by using a multiplier stored in another table. The multiplier to use is determined by the letter in column B.


    This is a very basic example of what I'm dealing with. I'm looking at 10000 parts and about 50 different multipliers. Codes are generally three characters.
    [TABLE="width: 384"]

    [tr]


    [td]

    Part

    [/td]


    [td]

    Code

    [/td]


    [td]

    Price

    [/td]


    [td][/td]


    [TD="colspan: 2"]Multiplier
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]12345
    [/TD]

    [td]

    A

    [/td]


    [TD="align: right"]2.00
    [/TD]

    [td][/td]


    [td]

    A

    [/td]


    [TD="align: right"]0.1
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]12346
    [/TD]

    [td]

    B

    [/td]


    [TD="align: right"]2.00
    [/TD]

    [td][/td]


    [td]

    B

    [/td]


    [TD="align: right"]0.2
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]12347
    [/TD]

    [td]

    C

    [/td]


    [TD="align: right"]2.00
    [/TD]

    [td][/td]


    [td]

    C

    [/td]


    [TD="align: right"]0.3
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]12348
    [/TD]

    [td]

    B

    [/td]


    [TD="align: right"]2.00
    [/TD]

    [td][/td]


    [td]

    D

    [/td]


    [TD="align: right"]0.4
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]12349
    [/TD]

    [td]

    B

    [/td]


    [TD="align: right"]2.00
    [/TD]

    [td][/td]


    [td]

    G

    [/td]


    [TD="align: right"]0.5
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]12350
    [/TD]

    [td]

    A

    [/td]


    [TD="align: right"]2.00
    [/TD]

    [td][/td]


    [td]

    Z

    [/td]


    [TD="align: right"]0.6
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]12351
    [/TD]

    [td]

    D

    [/td]


    [TD="align: right"]2.00
    [/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]12352
    [/TD]

    [td]

    Z

    [/td]


    [TD="align: right"]2.00
    [/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]12353
    [/TD]

    [td]

    G

    [/td]


    [TD="align: right"]2.00
    [/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]Thank you!
    [/TD]

    [td][/td]


    [TD="align: right"][/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    Re: Find, FindALL, VBA


    Ah sorry, I don't recall changing anything but you never know.


    Re: Find, FindALL, VBA


    It works but I think we're getting an infinite loop. The line of code that gets highlighted is: If Not rngFound Is Nothing And strFirst <> rngFound.Address Then.


    Error: Runtime error 91. Object variable or with block variable not set.


    [ATTACH=CONFIG]68661[/ATTACH]

    Re: Find, FindALL, VBA


    Okay, here goes. I had to remove some tabs and confidential info. Column A is the search criteria. Clicking commandbutton9 executes the scripts...this is just a test to make sure the scripts work. I'm going to use them elsewhere in the sheet. It is also drawing the vendor number from K11. Some of the buttons will error because of the tabs I removed.


    Thank you!

    Re: Find, FindALL, VBA


    Okay so I tried to loop it so I can run the script on a list of part number and it's only working for the first entry and then stops. I must need to add or delete a line of code here.



    Re: Find, FindALL, VBA



    Now that I think about it, let's return the value in columns A and B. The reason we're doing this is because the same part falls under multiple vendors and I'm searching for a match with one specific vendor. :) thank you.

    Re: Find, FindALL, VBA


    Quote from skywriter;767774

    What's z?


    The next column over, same row?


    An array?



    z is going to be a string that contains the returned value from the find. Column A.


    Thanks!

    Hello, I'm looking to code a sub that will find values in a sheet based on the part number and vendor number.
    Part number = x
    Vendor number = y
    Result = z


    When I click a command button the sub will:


    1. search for variable X on another sheet in column A
    2. once value is found in column A, the corresponding row in column B will be checked to see if it matches Y.
    3. If it matches then store the result in z. If it doesn't match find the next match in A and repeat the check to see if B matches Y.
    4. Loop until something is found that matches X and Y. If nothing is found then input "no results" into z.


    Any suggestions?


    Thanks!