Excel VBA Basics #33 Importance of Naming Column Variables for Reports

When you're taking a query or info from one sheet to another, it's good to have your columns numbered into variables so it's easier to make changes.

If the date is on column 1 and category is col 2, consider setting up some variables, such as

sDate=1
sCat=2

etc. So when you're using the Cells object, rather than using Cells(1,1) for date and Cells(1,2) for Category, you can use Cells(1,sDate) or Cells(1,sCat). Check out the video for a more in depth explanation!

Fantastic Developer Tools:

[krn]

Great job Dan. I do this all the time now and it is very easy to check and change when needed. Thanks

[ExcelVbaIsFun]

Oh my gosh! You're so true, yes, i'll have to put an annotation to that effect. Thanks MrPeklin!

[ExcelVbaIsFun]

Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan

[paulramirez]

Thank you for the awesome video.  Just a quick question.  Would it have been more efficient to have the macro add an autofilter on doggy and copy/paste the visible cells over to the other sheet?  Although, I know that is besides the point of the video.

[beenay]

Thanks man!
I don't know why nobody else is talking about this?
Can we name the column from formulas>define name, and use for-loop with the named column in vba, so that we wont have to even declare variable and assign numbers.
This way we won't have to change anything in out code even if we insert a column within our source range.

[obytrice]

In an earlier video you used named ranges that would adjust, would these still work in this instance?

[lekshey]

can you tell the shortcut key to indent ?

[MohammadTaha]

A side question: what is the program that you use to record your videos?

[sdveffwvee]

1. how come the macro won't run if I do it from the blank? BUt it runs when I do it from the sheet with data
2. your code at the end was worng, but how come the result was the same/right?
Thanksss

[clairemandley]

How do you differentiate between rows and columns when you only refer to them by their number? How does excel know that you're referring to one and not the other?

[MrPeklin]

In the end of the video you made some mistake. You shold have changed the oanimal to 3 and ohappysad to 2 instead of of saying
osheet.cells(y, ohappysat)=cssheet.cells(x, csanimal)

[sparkysrevenge]

Ok, quit question, instead of looking for 'Doggy' is it possible to have that be a Variable?