Posts by bryce

Re: How to run vba faster with this code

Dynamic? What part should be dynamic? How should it be dynamic? Please provide as much background information that you can so that we can help.

Re: How to run vba faster with this code

"I need to add also that the fieldname has multiple category per one vendor lookup will not be able to capture."

Can you post a revised sample showing this?

Re: Reference Worksheet based on cell value

Cool, thanks KjBox.

Re: Reference Worksheet based on cell value

KjBox - Do you use the bracket notation to shortcut using .Range?

Re: How to run vba faster with this code

Hi dbestal - Some thoughts after having looked through your file. My first impression is that you can do most of what you want to do with vlookups and a pivot table.

The vlookups - You look through the field name looking for key words. If there is a match on them, then Critical, else Non-Critical. All you would need to do would be to add another tab with the words to look for and use formulas to return Critical or Non-Critical.

Pivot tables will return exactly the look you want. Concerning the count, you would have to add Vendor Number to the values section of the pivot table to get a count but you can get it.

General comments about the code. Much of your code exists just to rearrange the data tab. Unless you had some specific requirement that the data tab must look some way, then you would be better off using the find function to find the column headers you want to find and work with them in memory instead of physically re-arranging your data. Also, I get why you color code (so that you can identify Critical if one color and Non-Critical if another color) but there is no need to do that either. You have options.

1) Store the words to find on another tab and reference that table in code
2) Use an array or a a dictionary. I am partial to the dictionary because I don't have to worry about its size and the .exists function is clutch.

If the pivot table wont suite your needs for some reason, please let me know and I will help on the code.

Re: How to find and replace multiple values at once in Excel based on date

Your revised sample has duplicate entries. There is no way for me to know which of these three items should be used as a replacement value. For instance, in columns A & B, there are 3 instances of 2/21/2017 & container B. There is no information for me to know which of the values in columns E through H are a match. I will need extra information to provide a match.

DATE mention cell Replace Range Time
2/21/2017 container B GRAPE345 10:00
2/21/2017 container B APPLLE678 11:00
2/21/2017 container B GRAPE345 13:00

Re: How to run vba faster with this code

Can you please post the file this code works on? There is a lot that can be done just cleaning up this code so that it will be faster. Never mind the 15k rows. Lets clean the macro first

Re: Using INDEX MATCH functions via VBA

Lol...Carim. I just found your worksheet change event. For a second I was blown away that you managed to get 2 lists to return the corresponding matching value with nothing but 2 named ranges:)

Re: loop through all pages in pdf

Check out this link. There is code in there for looping through the pages.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1101

Re: Extra Folder location from User window

Why wouldn't the user simply select the correct path? There is no need to try and get to your desired paths because they should be explicitly chosen.

Re: How to find and replace multiple values at once in Excel based on date

If I understand, you want to replace the values in column B with values from column G, if A&B = E&F. Is that right?

Give this a shot.

Sub findCategory()
    Dim ws As Worksheet
    Dim category As Object
    Dim x As Long
    
    Application.ScreenUpdating = False
    
    Set ws = Sheet1
    Set category = CreateObject("Scripting.Dictionary")
    
    ' Create a dictionary from the values in column E & F. The key is E & F, the item is G
    For x = 2 To ws.Range("E" & Rows.Count).End(xlUp).Row
        If Not category.exists(ws.Range("E" & x).Value & ws.Range("F" & x).Value) Then
            category.Add ws.Range("E" & x).Value & ws.Range("F" & x).Value, ws.Range("G" & x).Value
        End If
    Next x
    
    ' Go back through column A and look for matches in the dictionary. If a match exists, replace container with Value from G
    For x = 2 To ws.Range("A" & Rows.Count).End(xlUp).Row
        If category.exists(ws.Range("A" & x).Value & ws.Range("B" & x).Value) Then
            ws.Range("B" & x).Value = category(ws.Range("A" & x).Value & ws.Range("B" & x).Value)
        End If
    Next x
    
End Sub

Re: Run-time error 424 - object required

Glad you got it sorted out but I would suggest that you get into the habit of declaring variables.

check this link out. It discusses Option Explicit. http://www.vbforums.com/showth…t-and-why-should-I-use-it

Re: Run-time error 424 - object required

Can you post the other code that works? Also, what is r? It isnt declared or initialized. What is in cell(r,4)? What is sh1? It isnt declared or initialized either.

Re: Code to calculate total length installed based on number of pages that are entere

Post a sample workbook.

Re: Counting appearance of non-input and else count multiple input

Hi Spoons. You can give this a shot.

Sub countNonAContent()
    Dim wsCode As Worksheet, wsCount As Worksheet
    Dim countOfNonA As Long, lastRow As Long, y As Long, z As Long
    Dim x As Variant
    
    Set wsCode = Blad1
    Set wsCount = Blad2
    
    lastRow = wsCode.Range("H" & Rows.Count).End(xlUp).Row
    
    For y = 6 To lastRow
        If InStr(wsCode.Range("H" & y).Value, ",") Then
        
            x = Split(wsCode.Range("H" & y).Value, ",")


            For z = LBound(x) To UBound(x)
                If InStr(1, x(z), "A", vbTextCompare) = 0 Then
                    countOfNonA = countOfNonA + 1
                End If
            Next z
        Else
            If InStr(1, wsCode.Range("H" & y).Value, "A", vbTextCompare) = 0 Then
                countOfNonA = countOfNonA + 1
            End If
        End If
    Next y
    
    wsCount.Range("N32").Value = countOfNonA
        
End Sub

Re: Automation error when opening shared file with VB

This might help you.

http://www.ozgrid.com/forum/showthread.php?t=49229

Re: Copy Rows from Sheet 2 to Sheet 1 based on column values

Try this out. I added a helper function called Find_Range that is used to locate the index numbers

Re: Copy Rows from Sheet 2 to Sheet 1 based on column values

I get it. Thanks for the extra info. Ill be back soon with a possible solution.

Re: Copy Rows from Sheet 2 to Sheet 1 based on column values

For me, it is not clear where this row would be inserted. Sheet2 might have a parent ID of 2 in 2 rows but Sheet1 doesn't have a parent ID. The only common field between Sheet1 and Sheet2 is the index which is unique on both sheets, there are no repeats.

Maybe it would be easier to help if you told us more about what the goal is. What is the purpose of combining sheets?

Re: Trying to use Index Match with Named Ranges in VBA

I think if you add .address to the end of your named ranges it will work. If you are going to use VBA you might look into creating code to return the values your formulas return. It might be faster.