Posts by widgetwonka

    Re: VBA setting non adjacent cell formulas equal to each other


    Try this:


    Re: Multiple text string search in cells, and return specific text values in new colu


    Paul,


    Using LCASE() makes the string all lowercase. So, it isn't ignoring it. It is changing the string. That is why I edited the strings to be all lowercase too. I was glad to be able to help.


    I actually just adapted this code to help someone else with a similar issue.


    Hopefully you too will one day be able to pass the code on.


    Have fun.

    Re: VBA setting non adjacent cell formulas equal to each other


    Is this what you are asking for?


    Re: Multiple text string search in cells, and return specific text values in new colu


    Chris,


    The problem is that InStr() is case sensitive. I revised the code to deal with that. Also, I started your range in row 2, since you have header rows. Finally, I combined the macros into one called Combined().


    You'll be better off putting these in a module btw, rather than in the worksheet section.


    Re: Find word from an array in a cell and make it the value of a new cell


    Chris,


    Looks like you are having a hard time getting a response. I was just working on another piece of code that is very similar to this. I modified it to work for you:



    so, just copy:

    Code
    j = InStr(ObjectCell, "blue")
    If j <> 0 Then
    ColorMatch = "blue"
    End If


    replace blue with your color, and add it before ColorMatch = "No matching Colors". Then place this custom function into a module in the VBA editor. Now you should be able to use it like any other function like this:


    =ColorMatch(A1)

    Re: Multiple text string search in cells, and return specific text values in new colu


    Paul,


    I just tested your code with some dummy data, and it worked on my machine.


    I did add:


    Code
    j = InStr(ws.Cells(i, 6).Value, "Coo")
    If j <> 0 Then ws.Cells(i, 7).Value = "Operations"


    You can re-name the Operations to whatever you want. You just left it off it looks like in the code.


    Can you post a sample of your workbook stripped of any sensitive data? There may be another error lurking somewhere.


    Also, They are pretty strict about wrapping VBA in code tags here, so you might want to edit your posts to have ['code] before your VBA and ['/code] after it (minus the apostrophes).

    Re: Multiple text string search in cells, and return specific text values in new colu


    The i is an integer in a step loop. So i = one is the first row in the range. i = 2 is the second row...etc
    j is being used to store the start number of the string ENS.


    Yes, you could augment the code to be d1000000


    Column G, based on searching F:



    Column H, based on searching F:


    Re: how to record CTRL+F in a macro?


    Like this:


    Code
    Cells.Find(What:="Whatever you are looking for", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate

    Re: Reading multiple worksheets and populating a pull down box


    This code will add two sheets to your workbook:
    1.Products
    2.Services


    It then creates named ranges that are populated with the company names that offer each. Finally, it adds data validation to the MasterMatrix that has all the companies that offer those products and services. I noticed that you had some repeated items in your list. This code doesn't bother getting rid of those. I figured maybe this was just sample data, and your real spreadsheet didn't have this limitation.


    I put all the sub routines into a macro called Controller(). That is the only one you need to run.


    For those that are interested, I found a neat way to get around the non-alpha numeric character limitation in Named Ranges. I use an INDIRECT() and an HLOOKUP(), and simply assign each product/service a code. Then I can use Name = Service + i to ensure that I get the right values back.


    I didn't go overboard proofing it, so it may be a bit buggy, especially if you go about deleting rows or something. Let me know how it works with your actual spreadsheet.


    Finally, it may run rather slowly on 79 sheets. So, hold tight while the loops process.


    Re: Convert number with leading zeros to text using VBA


    Here it is without the message box:


    Re: Convert number with leading zeros to text using VBA


    This macro strips the apostrophes from the current selection:


    Re: And, if, then statements in excel 2007


    Pick,


    Per your PM, I have created a spreadsheet that shows the steps I went through. It also shows the filter criteria to limit results.


    I wrote this for Excel 2007. The same steps can be followed for 2003, but the filter criteria you will have to use are slightly different. Doesn't matter though, since you say you have 2007.


    forum.ozgrid.com/index.php?attachment/38487/

    Re: VBA conditional formatting code - works in 2010, not 2003


    I just ran the code on 2003, and it did not get caught in a loop. So, like StephenR, I would look elsewhere in the book.


    Just a few thoughts:
    Do you have some custom functions that are in the file? Does it work if you turn off calculation? Do you have a worksheet Change code in the sheet you are using this macro on?