This Excel VBA function allows you to take a column of values (e.g. ID numbers) in one worksheet, and use that to get corresponding values that are in a second worksheet. If you understand SQL, it’s something like:
SELECT b.country FROM idtable as a, countrytable as b WHERE a.id=b.id
I’ll explain the scenario further with screenshots, but basically we have one worksheet having client IDs, and another worksheet having client IDs with their corresponding countries.
The first worksheet looks like this. Notice the country column is column A and we’re looking to fill that up. Our IDs are all in column C:
And this is how the second worksheet looks like. Notice the headers are removed for convenience’s sake. We’re going to search column A using the IDs in worksheet 1, then grab every row’s country value.
Here’s the VBA code. Just stick it into your VBA editor, and run it to see what it does. A few things to note:
- Find this line For Each Cell In Range(“C2:C8”), and change C2:C8 to the range of cells that contain the IDs that you want to search for.
- Find this line With Sheets(“Sheet2”).Range(“A:A”), and change A:A to whichever column that you’re trying to search in.
Dim FindString As String
Dim Rng As Range
For Each Cell In Range("C2:C8")
FindString = Cell.Value
If Trim(FindString) <> "" Then
'The 2nd worksheet is assumed to be Sheet2. Change this if it is not the case
Set Rng = .Find(What:=FindString, _
If Not Rng Is Nothing Then
Application.Goto Rng, True
'In Sheet 2: This line shifts 5 cells to the right and gets the country value
TheCountry = ActiveCell.Offset(0, 5).Value
'In Sheet 1: The country value is pasted into the cell 2 cells to the left of the cell containing the ID
Cell.Offset(0, -2).Value = TheCountry
MsgBox "Nothing found"
I was using the Logitech K340 keyboard for around a year. Back then, the Logitech Wireless Solar Keyboard K750 was not released yet.
While the K340 keyboard was great at the start, after some time the keys would get stuck. E.g. I would be pressing down on the backspace key, and it would remain stuck, deleting not just a couple of characters, but the entire line of text. Needless to say, this was infuriating. So I went around, looking for another keyboard.
I found my answer in the Logitech K750. It seemed like the right one to get, but my biggest concern before buying this keyboard was that it doesn’t use batteries at all. On one hand, that’s great because it’s green, good for the environment, and saves me the hassle of changing batteries every few months.
On the other hand, I was afraid that it might just die too often because something that doesn’t use batteries just doesn’t seem to have the same sense of reliabilty to me.
It turns out though, that my worries were for naught. My room has a single window that’s usually covered by the curtain and my keyboard can last through several hours of use daily and I have never turned the keyboard off. Using the Logitech Solar App shows that the power level of the keyboard never dropped below 100%.
The keys on the Logitech K750 are perfect to me. They are comfortable to type on, and spaced out nicely. Pressing down on the keys give you a small amount of feedback, akin to typing on a laptop, but with much sturdier keys.
The reason why I chose this particular keyboard was because of Logitech’s unifying receiver technology. I use both a unifying mouse (read my review on the the Logitech MX Anywhere mouse) and keyboard, so I only have to use one Logitech USB receiver. That’s the thing that really sold me on to Logitech mice and keyboards using the unifying technology.
Even after a couple of weeks of usage, I’m amazed at how well the Logitech Wireless Solar Keyboard K750 types and performs. I’m very impressed with it, and have no qualms recommending it highly.
If you try to map a network drive in Windows, and use different user credentials, you’ll realise that you’ll need to always type in the username and password every single session. If you shut down your computer everyday, that means you’ll need to do this on a daily basis.
This is horrible too, if you have programs that automatically try to connect to your network drive, as it’ll most likely just fail.
The official solution from Microsoft is to make sure your user credentials on your local computer matches with that of the network drive that you’re trying to connect to. However, that’s hardly practical and probable in real-life.
The following solution is a simple line of code that you can save as a batch file that when run, creates a drive (Z: in this case) and automatically logs in for you. Set it to run whenever you log in by placing this batch file in your Start Menu/Startup folder.
net use Z: “\\192.168.1.1\yourfolder” PASSWORD /USER:”yourusername” /PERSISTENT:YES
Note: You’ll have to disconnect your existing drives and make sure that the drive letter isn’t being in use for this batch file to work properly.
If you’re developing an application that makes use of SQL statements, you may come across a time when you’ll require to count() values from a table. You may also come across a time when you’ll need to subtract a count() value from another. The good news is that you don’t have to retrieve both count() values separately and then subtract them in your application’s code – you can actually just use a single SQL to obtain this value, and this is the SQL format that you should use:
SELECT (SELECT COUNT(*) FROM table1) - (SELECT COUNT(*) FROM table2)
One of the first problems that I came across the Mac was the “File in use” error that occurred whenever I tried to unmount a hard drive, or unmount a USB thumb drive. It was annoying because I didn’t know how to find the source. In Windows, I would face the same problem too, but I had a program that I used to tell me the offending application. It was handy because it would show me what exactly was locking up my file, and I could unlock/remove its hold.
On the Mac, I had to search for something that offered the same functionality, and I found it in the form of a terminal command:
sudo lsof | grep -i
Basically, the command will look for your filename in the list of open processes, and display all relevant information about it on screen. I find this unwieldy though, so I searched around somemore and found a GUI replacement to this terminal command! It’s called What’s Keeping Me, and it does what the terminal command does, except in a pretty GUI.
So now you can find out what’s keeping a file locked on the Mac. Go grab What’s Keeping Me now – it’s donationware, so you can use it for free, and make a donation if you find it useful. Go to the What’s Keeping Me website.