How to set cells in a column equal to formula ONLY if the cell 3 spots to right reads 'X'

[micszekely]

Hi everyone,

So I'm pretty new to VBA but I took an online course and feel pretty excited about it. What I'm trying to accomplish right now normally requires using filters in Excel. If I filter on 'criteria A' then I enter a formula in column A for all rows meeting this criteria. I then filter onto 'criteria B' and enter a different formula in column B for these entries. I've figured out how to use VBA to filter onto a particular criteria but I'm getting stuck with entering these formulas and copying to the bottom of the data after I'm already filtered.

I'm also confused on how to reference a cell that is a couple columns over without also referencing the column (so that I can drag formulas down). Is this solved through looping or a different technique?

Thanks

[dflak]

The following piece of code illustrates several of the points you raised. Like anything else in Excel, this is just one way of doing this. The code decides what formula to put in column D based on the value in column A.

Since you are a self-proclaimed novice at VBA, I will be explicit in explaining the code, staring with: Option Explicit.

I’ll just come out and say, start all your modules with this statement. Option Explicit means that you must declare your variables. This might seem like a hassle but it pays off in the long run. What it does is act as a “spell checker” for your variables. So if you declare a variable S0 (S zero) and somewhere else in the code you set SO (S and the letter O) equal to something. Option Explicit will tell you SO is not declared. Without it, you might not notice the mistyped variable.

Also declaring variables is a good idea if you get into other programming languages: most are not as forgiving as VBA when it comes to data types.

Always add comments and be liberal with them. When you come back to look at your code 6 months from now you won’t be scratching your head over, “What does that variable mean?” or “What was I thinking of when I wrote this part of the code?”

So into the code. The first thing I declared was a worksheet “pointer.” One thing about VBA is that your idea of where you are and the program’s idea of where you are may be two different things. Always tell Excel where you want to be.

About the only time you can get away with this are when you launch the code from a button or link on a sheet or if you are running code in a sheet module.

Avoid using ActiveCell and ActiveSheet whenever you can.

While I’m on the topic. If you record a macro and it produces code like:

Please Login or Register  to view this content.

You can almost always replace it with

Please Login or Register  to view this content.

Sometimes you have to select something like a chart or object and then work with the ActiveChart or object, but these are exceptions.

So tell Excel which sheet you want it to be working on. There are two ways of doing this. One is Sheets(“Sheet Name”) and the other is to set a pointer like I did: Set sh = Sheets(“Sheet Name”).

There are two advantages to doing the latter: less typing later on in the program and pointers know which workbook they belong to. This will come in very handy when you have more than one workbook open at a time.

The rest of the code is straightforward. It demonstrates a for loop and a select statement. It also demonstrates use of the Offset command and one way of constructing a formula.

I really didn’t have to use offset. I could have just as well used sh.cells(i,”D”), but then there would be no example.

Another way, particularly if you are working with a complex formula is to set it up somewhere else and copy it in. So suppose I had a sheet 2 and in cell D2 I had the formula =B2+C2. Then I could modify the code to read.

Please Login or Register  to view this content.

[micszekely]

This was awesome! Much appreciated!