Miscellaneous

How To Search For ID Values In A Different Worksheet With Excel VBA

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:

excel vba search for value

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.

excel vba search for 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.
Sub findCountry()
    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
            With Sheets("Sheet2").Range("A:A")
                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                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
                Else
                    MsgBox "Nothing found"
                End If
            End With
        End If

    Next
End Sub
​Read More
Miscellaneous

Logitech K750 Review – Best Wireless Keyboard Review

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.

logitech k750 keyboard review 1

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.

logitech k750 keyboard review 2

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.

logitech k750 keyboard review 3

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.

​Read More
Miscellaneous

How To Solve The Problem Of Always Having To Log On To A Network Drive In Windows

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.

​Read More
Miscellaneous

How To Subtract 2 Count Values Using SQL Statements

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)
​Read More
Miscellaneous

How To Find Out What’s Locking Files or Folders in Mac

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.

whats keeping me screenshot

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.

​Read More
Miscellaneous

How To Use jQuery with MooTools

jQuery and MooTools are 2 great javascript frameworks that allow you to incorporate some really fancy effects on your website. I was using them while I realised that I had to use both of them on a single page at one point. I didn’t realise it then, and took me nearly an hour before I realised that the two frameworks were causing some conflicts. For me, I was trying out the accordion effect, and that caused a few compatibility issues — i.e. the javascript effects did not work at all.





Done! 🙂 By doing this and segregating your javascript effects, you can use both jQuery and MooTools together in a same page.

​Read More