Skip to main content

Posts

Showing posts with the label VBA

Excel Macro get cell values but skip other rows

Raw data that comes in pattern is easy to handle for further processing.
For example, if you have a data that has final value in every 7th row, so basically 1 to 6 is not needed and 7th row is needed, likewise 8th row to 13th row is not needed and 14th row is needed and the pattern goes on that every 7th row is the final data from the previous 6 rows.
If the pattern continues, it will be like: 1-6, 7 final value, 7 -13, 14 final value, 15 to 20, 21 final value, 22 to 27, 28 final value and the pattern goes on up to hundreds or thousands. It will be time consuming to get every 7th row manually.
For graphical representation it will be like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
From the image above every 7th row (green color) is needed and the rest is discarded.
Other issue comes in if the data has to be processed further in another sheet, doing this manually is so tedious and prone to error.
This is where VBA or Excel macro comes into play and simplify the process. The “step” keyword in VBA or even VB…

Disable Maximize and Minimize control in a form using Visual Studio

How to disable minimizing and maximizing a form in C# or VB.net?
How to create a border less form in Visual Studio?
Images below shows how to disable maximize and minimize control in a form.
Image below shows how to deactivate maximize option in a form:


Image of a form that does not allow maximizing:


Image below shows how to create a border less form, creating a border less form should provide an option within the form how to close the application.


Image of a border less form:



Till next time.. Have fun creating apps in Visual Studio...

================================ Free Android Apps:
Click  links below to find out more:
Excel Keyboard guide: https://play.google.com/store/apps/details?id=chrisjoms.myexcelapplicationguide

Heaven's Dew Fall  Prayer app for Android :
https://play.google.com/store/apps/details?id=soulrefresh.beautiful.prayer
Catholic Rosary Guide  for Android:
https://play.google.com/store/apps/details?id=com.myrosaryapp
http://quickbytesstuff.blogspot.sg/2014/09/how-to-reci…

Outlook VBA search for string in email body

How to search for a string in the body of Outlook emails?

How to search for a string in Outlook emails using VBA?

How to find a string in Outlook email body using VBA?

Outlook VBA code below searches a string on the email body and move the email to a specified folder.

Replace variables below with the actual folders and string to search in Outlook:

Destination_Folder_01
Folder_to_be_Searched
string to be searched

Folder_to_be_Searched - all emails in this folder will be searched and if the string matches the will be moved automatically to the destination folder. 


string to be searched - this string will match on the body of the emails

Sub MailItemContent_Move_Search()
Dim olItem As Outlook.MailItem Dim sText As String Dim myNameSpace As Outlook.NameSpace Dim myInbox As Outlook.MAPIFolder Dim myDestFolder2 As Outlook.MAPIFolder Dim mySearchFolder As Outlook.MAPIFolder
Set myNameSpace = Application.GetNamespace("MAPI") Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
‘Repla…

Word header does not print correctly

Ever printed a document and set the header as 1 inch. After printing the header is adjusted and it’s not the same with the specified settings on the header.
There are numerous solutions to this single issue. Updating printer driver may help to solve this problem. Selecting the proper size of the document before printing that is same size with the paper that is on the printer. And a lot of possible solutions.
Another solution to solve this issue is to check the headers and footers on page setup. On the document click on “File”, click “Print”. On the Print window, click “Page Setup” at the bottom on this window. See image below:




On “Page Setup” window, click the “Layout” tab and adjust the “Headers and Footers” as per requirement. See image below:


After adjust to a desired setting, click “Print” and if everything goes well. Then the printing will be as expected.

Cheers..till next time.
================================ Free Android Apps:
Click  links below to find out more:
Excel Keyboard gu…

How to search for string in Excel

There’s quite a lot of ways to do this in Excel, using Macro or Excel formula will be able to get the desired output.
Macro I think is good if the number of rows is not so large, but if the number of rows is quite large then a buffer overflow error might occur.
Formula can take quite large of data and able to deliver the result as expected.
ISNUMBER is a function that can check whether value is a number or not, the function itself tells what the formula will be doing.
Adding a search function to ISNUMBER then it can be used to search for a string with in a cell or the specified range in the formula.
Example below shows how ISNUMBER can be used to search for a specified string whether it exists or not. The position of the string doesn’t matter as long as the string exist within the cell, the formula will return TRUE, otherwise it will return FALSE if the string does not exist.
Here’s the example:
=ISNUMBER(SEARCH("search string", A5))
The “search string” is the string that will be se…

Excel filter option cannot be found

Working in a large data set in Excel but filter option cannot be seen or found even clicking the sort and filter option multiple times, the drop-down filtering options is nowhere to be found.
Well, just get back to the very basic steps. If it can’t be seen then it might be hidden, right?
So, when working in a large data set but some other rows is not needed and need to be hidden. Just leave the first row visible and do not hide it, then hide other rows.
The drop down filter option is in row 1, so if it is hidden then of course the drop down filter will also go with it.
To unhide row 1 or cell a1, press Ctrl + G. The go to options window will pop-up, type: A1 to go to cell A1. (see pic below)


Then, press ctrl+shift+9 to unhide the row. Or click on the format menu options and select unhide. (see pic below for excel 2016). Then the filter option will be displayed.



The keyboard shortcut I think will work on other versions of Excel.
If you need the filter option in Excel, row 1 must never be hid…

Excel footer or header location

How to insert a header or footer in Excel 2016?
How to insert a custom header or footer in Excel 2016?
To insert a custom header or footer with a picture or other sort of customization, can only be done of course using the custom header or footer option.
The easiest way to find header and footer is clicking on Insert on the main menu and selecting or clicking header and footer options on the ribbon tab as shown in the image below.


If the author of the file has inserted a custom footer or header, sometimes the footer it’s either the header or footer will not be shown.
If you need to delete, remove, change or alter the custom footer or custom header, it can be done by the steps below. Steps is shown in the image below. Click on “File”, click “Print”, click “Page setup” a small text found on the bottom right side of the print window. After clicking “Page Setup”, a window will open, then click on “Header/Footer” option.


Cheers, till next time! Hope it helps to make life easier.



=================…

Excel not enough free memory

Opening files in Outlook shows an error message that there is not enough free memory to run the program.
But the computer has enough RAM installed and no other application is running on the computer except Outlook.
Office programs by default don't trust or block files originating from the Internet.
This is of course a good security settings but if this setting is getting to your nerves because even if the document that is coming from your own domain you are not able to open.
Or the file is originating from your own personal email account other than your company email but when it goes to your Outlook you cannot open it. This is the scenario where good and harsh words will start crossing your mind. You will need to ask what the heck is going on?

The Microsoft link below provide some solution on how this thing can be resolved.
The article title of the link below is:

Error message in Outlook: "There is not enough free memory to run this program. Quit one or more programs, and th…

Loop thru custom Textboxes name in VBA or VB.Net

Having a custom name in Textboxes, Listbox, ComboBox in VBA or VB.Net is a good strategy. So, it will be easy to debug the code or analyze the data.

If just relying on the default names is also good but it will get messy and it will be hard to control when there’s quite a few of textboxes, and list boxes. And the next person who will maintain the code will find it easier to debug or check the program when proper custom name is assigned to the controls. And that next person might be yourself. So, be gentle and be explicit in giving comments and assigning names to controls.

It doesn’t hurt to put a clear comment or an overview of what the piece of code does or what’s the input that the control is accepting. Because greediness breeds greediness, and it will bite back.

Anyway, to loop to all the custom or default names is quite easy.


Example, if there are 30 or more textboxes in a form. It can be done by a simple for loop like:

For x = 1 to 30

GetTboxvalue =    Me.Controls(“Textbox” & …