Posts by yogeshtalks

Re: Using a value from a cell inside a formula

Quote from NBVC;773034

Do you have an actual date in D1? I tested it and it works fine for me.

The formula you suggested works perfectly in excel but I am afraid the same doesn't work in google sheets which is quite surprising. I made sure that the format of both the ranges used in the formula is the same. Any solutions?

Re: Using a value from a cell inside a formula

Quote from NBVC;773029

You don't need the INDIRECT to reference the date...

Try:

=COUNTIFS(INDIRECT("$C$16:$C$"&$F$1),$Z3,INDIRECT("$T$16:$T$"&$F$1), "<" & D1)

I tried this formula but it is not working. Can you please suggest any alternate formula?

Re: Using a value from a cell inside a formula

Quote from NBVC;773024

Try:

=COUNTIF(INDIRECT("$C$16:$C$" & F1),$Z3)

or better

=COUNTIF(INDEX(C:C,16):INDEX(C:C,F1),$Z3)

Thank you so much NBVC. That worked perfectly.

Now, I am trying to do something similar where I am using another CountIFS formula which is =COUNTIFS(INDIRECT("$C$16:$C$" & $F$1), $Z3, INDIRECT("$T$16:$T$" & $F$1), "<06/24/2016").

Here, instead of mentioning the date directly, I would like to refer it to the cell D1 where I will change the date based on the date range of the report. I tried the below two options which are not giving me the desired results. Please help.

=COUNTIFs(INDIRECT("$C$16:$C$" & $F$1), $Z3, INDIRECT("$T$16:$T$" & $F$1), indirect("<" & D1)

=COUNTIFs(INDIRECT("$C$16:$C$" & $F$1), $Z3, INDIRECT("$T$16:$T$" & $F$1), "<" & indirect(D1))

I have a formula in cell F1 which is =COUNTA(A:A)+1 which returns values depending on the number of rows having values in have in column A, every time I change the data from a CSV file.

I want to use the value returned by this formula in another formula elsewhere.

For example: If the value returned by the formula is 425, I want to use this value in another formula referring to the cell F1.

The formula that I want this to be added to is =COUNTIF($C$16:$C$2000,$Z3 ). I want the value from F1(425) to be included in the place of 2000 hence the formula should effectively return the value of =COUNTIF($C$16:$C$425,$Z3 )

I tried using & to append using the & symbol annd also tried using concatenate function to append. In both the cases, I did not arrive at the same result if I use the formula: =COUNTIF($C$16:$C$425,$Z3 ) , perhaps I am not using them correctly.

Can someone help please!

Re: VBA code to check whether a Check Box is checked or unchecked

Oh!!!!

I was going wrong in the syntax...

Thank you very much for your help.

Now it works great

Cheers
Yogesh

Hi all,

I am trying to use the check boxes from Forms.

I have inserted check boxes and i have code that executes based on whether these check boxes are checked or unchecked.

What would be the macro code that i need to use for achieving this.

I tried something like

If ActiveSheet.Shapes("Check Box 1").Value = xlOn Then
        'Code to be executed

But, i get an error while executing the macro that says:

Run-time error '438':

Object doesn't support this property or method.

Please help.
Thanks

Re: Open the fiels in folder based on last modified date

Thanks a lot fellas.

It as too hard for me to play with arrays and stuff. Therefore, i found my own way with your advice. I made a copy of the file names and their modified date in to a sheet and sorted them according to the date and then while opening the files, i referenced the file names in the order that i sorted. It works great exactly as i needed. I will try to go through the arrays stuff later coz i had to finish this thing off urgently.

Cheers
Yogesh

Re: Open the fiels in folder based on last modified date

Thanks a lot guys...

But, i need some time to spend on it to understand whats going on.....

At this moment, whatever you have posted seems like Greek and Latin for me...

I will try them and get back later....

Dear all,

Thanks for your help all this while. It was very useful all the way.

I am using the following code:

Set SourceFolder = FSO.GetFolder("C:\Path\")
            
  For Each FileItem In SourceFolder.Files
      Application.Workbooks.Open (FileItem)
'Further operations to be performed bu extracting some data from teh opened files one by one

I want to modify the above code such that the file is opened based on the last modified date property(I mean the oldest file to be opened first and sequentially the files are opened with the latest file opened as the last file)

I want to extract some data from opened files and update another list based on date in ascending order.

Please note that the date is updated in columns so that i cannot sort by date after the whole list is created. also, the file that i am updating is a template so I cannot edit it according to my own needs.

Apologize me for such an ambiguous query. Plese let me know if you do not understand what I exactly mean.

thanks

Cheers
Yogesh

Re: Runtime error '3' : Exit without Gosub

Oh thanks a lot. I understood where i was going wrong.

I was using ' Calc() ' to call the function. Its simply ' Calc ' to call the function. I did not know this.

Now, its working all right.

Cheers
Yogesh

Re: Runtime error '3' : Exit without Gosub

I use Calc(void) because i am not passing any arguments to the function. The function is just a block of code which performs some basic operations in excel like copying and pasting and stuff. I am using this function because i want the same operations to be performed at various cell ranges in same sheet.

I am not sure how to go about calling a function without passing any arguements. Thats why i used void.

Hope you understand.

Thanks

Cheers
Yogesh

Re: Runtime error '3' : Exit without Gosub

Thanks for the reply.

But when i remove the code, i get the error message:
"Runtime error '13' - Type mismatch"

and sorry about the tags i used. Will be more careful next time.

Thanks

Cheers:
Yogesh

Hi all,

I am trying to use functions in VBA.

I have a simple fundtion which does some operations. At the end of function, I want the code to resume from the next line of the code where I called this function from. I am getting the error message:
" Runtime error '3'
Exit without GoSub"

Can anyone help me?
The following is the code:

Dim j As Integer


Sub try()
j = 11
Calc (void)
j = 12
Calc (void)
j = 13
Calc (void)
j = 14
Calc (void)


End Sub


Function Calc()


//Operations performed//


j = j + 5
Return
End Function

I am new to using functions. So, pardon me if i am not using the correct syntax. Please enlighten me.

Thanks

Cheers
Yogesh

Re: VBA code to search files based on last modified date

My apologies for the confusing post.

what I meant to say was, the original files are copied to the new destination folder but, i guess the code opens the files from the original location rather than opening the copy at the destination folder.

Quote

FileItem.Copy "C:\temp1\" & FileItem.Name, True 'To copy the selected file use Application.Workbooks.Open (FileItem.Name) ' To open the workbook

As seen above, the files are copied, but still the original files are opened. I may be wrong, but thats my understanding.
But, I managed to tweak the code a little bit to so that after copying the files, the files from the destination folder are opened to perform the operations on it.
Now, it works fine.
Thanks a lot for your guidance.

Cheers
Yogesh

Re: VBA code to search files based on last modified date

I just happened to notice one thing.

After i had performed the search and did the necessary operations on those files, the last updated date/time changes to the current time (somewhat like i had saved the files again which i did not).

Is there a way to get rid of this. I want to keep the original files as it is with the same date/time.

Thanks
Yogesh

Re: VBA code to search files based on last modified date

Thank you so much Smuzoen. It works great. I cannot believe its working for me.

It wasn't that complicated as I imagined. You guys are doing great help.

Cheers
Yogesh

Re: VBA code to search files based on last modified date

Oh boy!!! It looks like a bit too advanced stuff for me. Anyhow, let me see if I can work with it.

thanks a lot for your help.

Hi all,

Another query regarding searching of files. This topic in VBA is not easy, at least to my standards.

This time, I want to search a folder which contains hundreds of data files. I want to search for those files based on the Last modified date/time. I want to search those files and copy them to a different destination and open them to copy a particular range of cells to another new work sheet for processing.

For example: I want to search for those files that were last saved in a particular month/week.

The code i need is to search and copy files at destination folder based on date range that I specify and open them. I hope that I can manage with the rest of the code.
I would like to input the date range at a particular cell in my worksheet which the code can access to perform the search.

Thanks in advance.

Cheers
-Yogesh

Re: VBA code to search files

Thank you very much Jindon.

The code works perfect to get the path of the file that i am looking for. Now, I will try to improvise to copy and paste the searched file to my destination folder.

Cheers

Hi all,

I am quite new to VBA and just started using it. I am finding it very hard to get hold of the file handling concepts. Please help.

I want to search for a file inside a directory which contains many folders and sub-folders. The file that i am looking for is inside any of those folders of sub-folders. I would like to enter the file name in a msgbox or at a specific cell in a work book so that the file is searched and saved at the destination foler that i specify.

Note: the file i am looking for is not always excel workbooks. Most of them are data files which contains text in .csv format.

Thanks