Posts

***Add New Item in ComboBox in a Cool Way***

Step 1: In any column on a sheet let's assume Column "J". in Cell J1 type header of the combo box and in Cell J2 type "Add New Item"

Step 2: Select the Cell J2 --> Goto Formulas Tab --> Click on Name Manager --> Click on "New" --> Type any name you required, Let's say you typed "MY LIST"

=OFFSET(KDATA!$J$2,0,0,COUNTA(KDATA!$J:$J)-1,1)

Step 3: Now, U can design the user form and add the combo box in it. type the following line in the userform_initialize event

Userform1.ComboBox1.RowSource = "MY LIST"

Step 4: Double click on the combo box and write following VBA codes into it. and that's it.

Private Sub D3_Change()

    If U.D3.Value = "Create New Type" Then
        With shKDATA
            .Range("J" & .Cells(.Rows.Count, "J").End(xlUp).Row + 1).Value = _
            Application.InputBox("Please Type New Supplier Type", "Add New Supplier Type")
            With .Range(&qu…

Look for the String in the Cell of Multiple Column Table and if not found then Filter the whole row

Suppose, You have 10 columns table in excel and want to filter out such rows which do not have String that you are looking for. If any cell of the row has such string then it should not be filtered out. 



Want your Excel File/Application will RUN smoothly in everywhere?

Hello Developers, 
With this post, I'm going to share with you a very handy and useful trick which will help you to make you Excel or Macro Enable Excel files DISTRIBUTABLE or PORTABLE.
The main issue usually excels developer faces when their highly invested spreadsheets stop working or generate of bugs in the initialization.
There are lots of causes of that problem but the main issues a developer usually face a Catastrophic Run Time Error. Which occurs when you have used a function or formula in your excel sheet which doesn't support in that office version.
Then what, developers nightmare starts from the same moment. He needs to go through each formula and code from the top to the bottom to find the unsupported functions.
But I have the solution and I'm gonna share it with you. :)
There is a website or we should call it a lifesaver website called
https://www.techonthenet.com
Now, when you gonna use any formula which you have doubts whether it will support in another syste…

How to create Values as Developer?

If we talking about the development of an application then there are following things concerns the most.

1. Handy, Clean, Sober and Easy to understandable, User Input interface. which is simply known as a user interface or a front-end.

2. The Backend, which has lots of data processing procedures, functions, and formulas, which works on user inputs, should be very well written, optimized, updated and should be very well commented.

3. The Reporting or Outputs or main objective of the application should be How effectively, efficiently and quickly can be delivered without causing legging.

4. The Database structure, where the user's highly important data has been saved to connect, all the missing dots of the application.

So, a developer with a good vision, will consider all the above points and design a whole software based on the same consideration.

When we help the user to input the required field within their comfort it creates value.

1. When we help the user to process their data t…

Fastest Method to Copy Filtered Data in Excel VBA

Image
Yesterday in my post,

https://www.facebook.com/groups/ExcelFreelancersGroup/permalink/2469040236459183/

related to 'Copy the "Filtered" Data from Sheet1 to the Sheet2' and Mr. Mehmet, asked the following thing in addition to the provided solution and here was his query

"Many of Excel users are using excel as Database and they are facing huge datasets such as 500k rows and 100 columns. Could you please provide a faster way to copy and paste data such as arrays or advanced filter or anything that you would suggest us."

****SOLUTION****

Now, I think Mr. Paul Kelly has your answer with such great detail as never seen or experimented before. I would like to thank him for his effort to get such figures which are mentioned in the image attached to this post.

So the basic query of my friend Mehmet is to know all about which method is Fastest of all.

So Mr. Paul has conducted his own speed test to know which method is really quick in the average scenario.

Here is h…

Run your VBA Program Daily & Automatically!

Image
My Client has a Query!



He wants to receive an email as a notification just before a 15 days before his employee's vacation period starts.

here is his explanation in his words : "I am thinking about a script that opens the spreadsheet once a day iterate through all the employees (about 20) then check if today's date is equal to the day that each employee will complete one year minus 15 days, and, if the result is true, send the notification. But I don't know how to implement that with code."

Here is the solution how to make this automation happened.

1. Create a workbook

2. Add Worksheet

3. Name it as Employee Manager

4. Create a Table and enter the Employees Details in line by line

5. Create a VB Program which finds the Joining Date of Employee and if it matches the criteria then It will Email you just before the vacation period starts for him/her.

6. Supposed, Employee joined on 1st Jan 2018 then on the 15th December 2018, you will receive an email about the empl…

Future of VBA

It's necessary to think of exactly 10 years ahead of now because it makes your actions more based on future. well, when you are doing good something anything, the best thing you received out of it is a community of Good People. Yes, the best asset. I met one guy on LinkedIn named Vincent ISOZ and he suggested me to provide the same VBA solution on power query. because according to him it is the future. in the next 10 years, every corporation will moving towards cloud office. Then he wrote one comment and I really felt, I should post that comment here in front of you guys, because if VBA is the tree then we are the leaves of it. well here is his comment... "I also worked for Microsoft as an external consultant during 4 years. VBA will be stopped in 2024 (according to the roadmap I saw but you can probably add the 3 usual years of delay) as only Office Online will still be maintained (cloud strategy of Microsoft). We already know that the replacement of VBA is Power Query (with…