Posts by whatisVBA

Re: left function compile error

that worked! Thank you!

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.

Dim ws As Worksheet
Dim lastrow As Long
Dim c As Long
Set ws = ThisWorkbook.Sheets("OpenPO")
lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
For c = lastrow To 3 Step -1
    If left(Range("B" & c).Value, 3) = "945" Then
        ws.Rows(c & ":" & c).Delete shift:=xlUp
    End If
Next
End sub

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!

Re: For loop is skipping lines

Thanks Bryce! I used your version of the code and it works perfectly. Why are you storing the ws variable? Is it for cleanliness or is there a VBA rule?

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?

Sub expFilterHLD()
Dim hld As Long
For hld = 15 To Sheets("Expedite Report").Range("A15").CurrentRegion.Rows.Count + 13
    
    If Sheets("Expedite Report").Range("E" & hld) = "HOLD" Then
        Rows(hld & ":" & hld).Select
        Selection.Delete Shift:=xlUp
    End If
Next
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"]

Part

Code

Price

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

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

A

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

A

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

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

B

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

B

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

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

C

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

C

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

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

B

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

D

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

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

B

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

G

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

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

A

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

Z

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

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

D

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

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

Z

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

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

G

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

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

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

[/TABLE]

Re: Find, FindALL, VBA

It works! Thank you so much!!!

Re: Find, FindALL, VBA

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

Sub sXv()
'search x vendor
Dim i As Integer
    Dim rngFound As Range, strFirst As String
    Dim pNum, vNum, lr As Long
    lr = Worksheets("SearchEng").Cells(Rows.Count, "A").End(xlUp).Row ' Find the last row with data in column A..
    With Worksheets("pmaster").Columns(1)
        For i = 2 To lr
            strFirst = "" 'Clear the value assigned to strFirst.
            If Sheets("SearchEng").Range("A" & i) <> "" Then
                pNum = Sheets("SearchEng").Range("A" & i).Value
                vNum = Sheets("SearchEng").Range("K11").Value
                If IsNumeric(pNum) Then pNum = Val(pNum)
                If IsNumeric(vNum) Then vNum = Val(vNum)
                Set rngFound = .Find(What:=pNum, LookAt:=xlWhole, SearchDirection:=xlNext, After:=.Cells(1), MatchCase:=False)
                If Not rngFound Is Nothing Then ' Checks if an item was found.
                    If rngFound.Offset(, 11).Value = vNum Then
                        Sheets("SearchEng").Range("B" & i) = rngFound.Value
                        Sheets("SearchEng").Range("C" & i) = rngFound.Offset(, 4).Value
                        Sheets("SearchEng").Range("D" & i) = rngFound.Offset(, 2).Value
                        strFirst = rngFound.Address '  Assign the address of the first item found, so code will know if it has finished looking.
                    End If
                    Do
                        Set rngFound = .FindNext(rngFound)
                        If Not rngFound Is Nothing And strFirst <> rngFound.Address Then
                            If rngFound.Offset(, 11).Value = vNum Then
                                Sheets("SearchEng").Range("B" & i) = rngFound.Value
                                Sheets("SearchEng").Range("C" & i) = rngFound.Offset(, 4).Value
                                Sheets("SearchEng").Range("D" & i) = rngFound.Offset(, 2).Value
                            End If
                        Else
                            Exit Do
                        End If
                    Loop
                End If
            End If
        Next i
    End With
End Sub

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.

Sub sXv()
'search x vendor
Dim i As Integer
Dim rngFound As Range, strFirst As String
Dim pNum, vNum
        
For i = 2 To Sheets("SearchEng").Range("A2").CurrentRegion.Rows.Count
    If Sheets("SearchEng").Range("A" & i) <> "" Then
        
        pNum = Sheets("SearchEng").Range("A" & i).Value
        vNum = Sheets("SearchEng").Range("K11").Value
        If IsNumeric(pNum) Then pNum = Val(pNum)
        If IsNumeric(vNum) Then vNum = Val(vNum)
        With Worksheets("pmaster").Columns(1)
            Set rngFound = .Find(What:=pNum, LookAt:=xlWhole, SearchDirection:=xlNext, After:=.Cells(1), MatchCase:=False)
        If Not rngFound Is Nothing Then
            If rngFound.Offset(, 11).Value = vNum Then
                    Sheets("SearchEng").Range("B" & i) = rngFound.Value
                    Sheets("SearchEng").Range("C" & i) = rngFound.Offset(, 4).Value
                    Sheets("SearchEng").Range("D" & i) = rngFound.Offset(, 2).Value
                Exit Sub
            Else
                strFirst = rngFound.Address
            End If
        Else
            MsgBox "No matches found"
            Exit Sub
        End If
        Do
            Set rngFound = .FindNext(rngFound)
            If Not rngFound Is Nothing And strFirst <> rngFound.Address Then
                If rngFound.Offset(, 11).Value = vNum Then
                    Sheets("SearchEng").Range("B" & i) = rngFound.Value
                    Sheets("SearchEng").Range("C" & i) = rngFound.Offset(, 4).Value
                    Sheets("SearchEng").Range("D" & i) = rngFound.Offset(, 2).Value
                    Exit Sub
                End If
            Else
                MsgBox "No matches found"
                Exit Sub
            End If
        Loop
    End With
        
   
    End If
    
Next
 
End Sub

Re: Find, FindALL, VBA

It works! You're a wizard!!! Thanks :sing:

Re: Find, FindALL, VBA

Quote from skywriter;767779

Let's try it again.

We are searching in column A for a part number, if we find it we look into the next column for a match to the vendor number, if we find it, bingo we have a jackpot.

Now again, what do you want to return?

What is in the string?

The word, "yes it was found", the address of the cell in column B that matches the vendor number?

What do you want to return?

Display More

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!