Search results

Wow. That is interesting. I never knew I could get away with 1 line of code for this. Thank you Hareesh.

Hi Belleke, Thank you so much for the code. This works great. Appreciate your help.

Hello Gurus, Can you please help me with the code? Problem: I would like to shade cells in r2 (A1:B10) with grey color based on value of cells in r1 (C1:D10, empty or non-empty). eg: If C1 is empty, A1 should be shaded grey If D5 is empty, B5 should be shaded grey The code below works but it...

Amazing. Thank you so much. This works perfectly.

Hello All, I have an Excel table problem. It seems easy to solve but I cannot find a solution. SOURCE TABLE: A Rose A Pen A Pencil B Apple C Cake C Orange C Mango C Lime REQUIRED FORMAT: A Rose Pen Pencil B Apple C Cake Orange Mango Lime GURUS please help. Thanks

The grep solution for my problem is welcome but it no faster than the vba macro solution. Also since I am so close to the vba macro solution and this being a excel related forum I would still like to pursue my issue here. One issue that I have is that the display alerts is not turning off. This...

Thanks for your reply Chihiro. I have started looking into grep as well. Since I am not an expert, can i ask you if it is possible to do the same thing using grep? I have a list of strings (~700) that I need to search in files within a folder (with subfolders). Can we use loop in grep to do...

Hi Chihiro, Thanks for the link. I tried to play with the code and came up with the following that works but with a few glitches. The combined macro works for a small folder but in my case I have a complicated file system (1 major folder with ~1000 folders containing 10-20 sub-folders, ~10-20...

Hi Chihiro, thanks for the link. It does the same as the code that I have posted. The macro looks into files in a folder but it is unable to check sub folders. Thank you, hope you can help modify so that it can check sub folders.

Thanks for the suggestion, I will look into it. I would like to pursue a vba solution if possible.

Hello XL Gurus, I have a list of IDs in a file and I need a macro to loop through the xls* files in a directory (which has ~100 folders, each folder has ~10 sub folders). Literally there are 1000s of files and each ID is in a folder. To make it faster, I could have the IDs and the Folder Name in...

Hello, I was searching for macro to do something similar and the solution by BBD does it perfectly. However, I would like the macro to stop at the first instance of finding the "text" in a file. Can you please help me by letting me know how I can modify the macro to stop after it finds the...

Hi Hui, Thanks so much for the the formula, it works great. I have been using formulas (with helper columns) until now to do this analysis but not as efficient as yours. I would like to build up on my VBA skills and would like to accomplish this using variables but do not know how. Can you...

Hi Gurus, Can you please help me with the following problem. I am new to VBA and do not know how to use variables. I have a sheet (Rawdata) with fingerprints (generated by an assay) for each sample. Each sample has a parent ID linked to it and the fingerprint should match the parent...

Hi SirJB7, Thanks. Your formula still gave an error. You were right about the quotes. The problem was when the formula had to look up another worksheet (Master SNP Database). When I enclosed it with "" it worked. Thanks for your help.

Hi SirJB7, Thanks for your suggestion. I tried to convert each of the internal quotes into double quote but the code still gives an error.

Hello Gurus, I am new to excel VBA. I am not familiar with variables and loop, hence trying to use activecell.formula. I know this is not the efficient way to do it, but as I am new and just learning found this easy to do. The following formula works when manually typing in a cell. Formula...

Hi SirJB7, Thanks for your reply. We all have read and write access to the particular folder in the server. Is there a way to save a file with the same name in the same location even though it is open elsewhere. Thanks

Just wanted to reword my request. I have a file on the server which I update by a VBA macaro every day. End users use this file to lookup information. I would like to be the "owner" of the file and attribute "read only" access to end users. The problem is, if end-users forget to close the file...

Hello All, I have a macro which updates a file on the server on a daily basis. This is a shared file which many users will access. 1) I would like this file to open as "Read Only" (without prompt) when an end-user accesses it. 2)And I would like the macro to be able to update even if someone...

Add humor to the list, wonderful. Thanks SirJB7.

Thanks All. I am at work and will take a look at the suggested reading material and get back later this evening (PST). This community is rich with a lot of knowledgeable and helpful people. The combination of which makes it unique.

Thanks LukeM, I tried that but it does not work. set table = Workbooks("FilesReport.csv").Worksheets("Report").Range("a1:au1525"). As soon as I change to full path the UDF fails (whether the workbook is open or closed does not matter)

Hi SirJB7, An update, solution (c) works as long as I have the server file open and I don't define the path. Can this UDF work with the reference file closed? TIA

Hi SirJB7, Thanks for helping out. I tried the your solution (c) but still got an error (#Value). Could this be because I am trying to access an file on the server and have not defined the path? Can you please help me define the path? For eg: "FilesReport"