Search results

Haha, it's not homework, it's part of the data I need to deal with. It doesn't matter what I use, but formula is what I know. Can you explain more about your method? I don't quite understand, thank you!

I have the below data on 3 different cell and I would like to extract the text string Book, Booklet & CD from each cell. Data: A1 = 1 sound disc (ca. 17 min.) : digital ; 4 3/4 in. + 1 book (31 p. : col. ill. ; 18 cm.) A2 = 1 sound disc (ca. 14 min.) : digital ; 4 3/4 in. + 1 booklet A3 = 1...

Thanks for your tip. The above code basically is a file re-namer that changes the file name by using certain cell value from that workbook. I used ExecuteExcel4Macro to retrieve value from the close workbook. However, I have no idea how to pull value from a close workbook and then check if the...

I would like to write a VBA to check the files in a folder that contains a certain phrase (say ABC) in filename and has a certain value in cell A1 (like 1) in sheet1. I can get the first part (contains a certain phrase ) working but fail on adding the second criteria (checking the cell value)...

Hi, shrivallabha, you are right, my team doesn't open the workbook on a regular basis, and that folder only need to open within certain hours. So, that's the solution I am looking for, it works like a charm. Thank you so much!

Hi, I recently put a code in my workbook to run whenever it opens - i.e., open a specific folder. However, I soon find out the code is only needed between 0800-1600 hour. Is there any code that checks the local machine time before executing? I searched online and found some code about code to...

One following up question, if I want to set the range F2:F500 to a dynamic range, how can I do it?

Thank you for pointing out the problem behind my logic, I got it now.

Was trying to clean up my clumsy code with the idea below, however, I run into the a new problem. Can someone tell me why it behaves like so? Thanks! Dim x As Integer, y As Integer, z As Integer 'y and z are newly added For x = 2 To 500 For y = 1 To 10 z = 6 If Cells(x, z).Interior.Color...

Thank you for such thorough explanation, I get a better idea of this common function. :)

It works so well! Can you explain a bit about the logic behind this? I have a hard time understanding it. Thanks again!

Hi, I would like to write VBA that lists out the number (column A) of multiple date on the same column (column B) from a worksheet. Since the number of date may vary, I am having a hard time to get it down. I know how to get the total number of all the date, but I can't count the number by...

I just change OR to AND, however the result remains the same. Any thoughts on this?

Hi, I am trying to reference a cell's value based on the date I enter. However, I couldn't get it work. BankInRef.text = the date I enter For example, 8/5/2017 is the date I enter and it is greater than D2 (8/1/2017) but small than E2 (8/7/2017) then the active cell value should be...

Thank you so much for your help! I learned a lot, thanks again!

Hi, I would like to tweak the following code so it would list files only with the prefix of "NCIR*", is that possible? I am having a hard time to fit it the code, thanks! Sub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean) Dim xFileSystemObject As Object Dim...

It works! Thank you so much!

The following code intends to rename a file name based on certain prefix of the file, however, when another file with the intended name already exist in the folder, the macro will stop. I tried to insert "if error" command to skip to the next file but it doesn't work. Can someone help? Thanks...

I got the code responding by changing If Sheets(1).Range("A2").Value = "*EBR"Then to If Sheets(1).Range("A2").Value = Sheets(3).Range("A1") Then The data on Sheet 3 is the name list in for the drop down box on A2 in Sheet1. Thanks for the insight.

The last code you wrote is working great, thank you for all the help, it gets me very close to where I want to be.

Hi, I am working on a vba that auto filter a range (A10:I5000) based on a cell value in A2 (which is a dropdown list) on the same sheet The code is deprived from a macro that I recorded, however, nothing happens when I run it. Attached is the file that I am working on, thank you. Public...

Hi jindon, Here are the files and code for you to test with. The following is the code that I have been tweaking with. Besides file with *_A_*, I was also trying to rename files that started with: NCIR024_D_YMT_, NCIR024_D_YMT_B_, and NCIR024_D_YMT_E_ as well. But I couldn't get it to...

Hi jindon, More finding on this "file not found" error, when it was renaming files in multiple folders. The error shown on the last file in the first file. When it was trying to rename files in a single layer folder, the error shown on the last file. Thanks.

Hi, jindon The follow code at the bottom of the vba receives an Error 53: File not found, but all the files are renamed. Name a(1,i) As a(2,i) &

No effect, it must be something other than xml.