Problems getting started, Defining variables and creating functions

Hello all,

I am an engineering student currently working at an engineering firm until the end of the summer. At work I am asked to create a system so that progress can be reported more easily. The problem should be a fairly easy problem but I have some problems getting started with VBA.

To report the progress I want to create a CommandButton in excel which creates a table where the progress of several (sub)projects are shown. At my company we already have a tool which exports a excel file in which all the need data is shown (if filled in correctly by engineers and project managers). I would like to create a excel file where the data exported form the tool can be stored and a CommandButton which creates a table with the processed data is created.

- I understand that there are nicer ways to solve this problem but for now this is the simplest and quickest way to solve the problem. -

In university I have done some programming in Matlab, this where mathematical operations. Since i have only experience in Matlab I am used to working with scalars, vectors and matrices only. I have asked a colleague to help me get started and he explained a little bit about defining your variables. So I already have my workbook and worksheets defined correctly.

When I start writing my code I keep on bumping on some errors and even google cannot give me the correct answers. I will try to explain my problem as clearly as possible.

To be able to collect the data I need to create a range. Because the exported data can change the range needs to be variable.

Please Login or Register  to view this content.

This gives me the correct numbers of Row to use for my range, however I have trouble using this number as the limit of my range. I cannot find a way to use this a my range.

Please Login or Register  to view this content.

I now have defined H as a Value but I cannot use this in a function? Can't I use a Long variable?

I have been working on this single problem for two days and I seem to be not understanding something. I really like the whole programming thing and hope to understand VBA and create some usefull macros. I am forever grateful to who can help me get started.

Are you using the instruction

Please Login or Register  to view this content.

at the beginning of your code?
What error does the VBA compiler detect?

The following code works fine

Please Login or Register  to view this content.

The instruction

Please Login or Register  to view this content.

will refer to Cells in the currently active sheet.

From what you wrote I guess you are using different Worksheets, so I would suggest to add the name of the Sheet where you want the range to be defined, for instance

Please Login or Register  to view this content.

Actually my previous code will fail (with the same 1004 error) if you add a new Sheet and run the macro from there.

Your second instruction instead is working because

Please Login or Register  to view this content.

is fully qualified by

Please Login or Register  to view this content.

Originally Posted by thyspaleis

For what I understood I already stated that the range should be taken from Worksheets("One") before the .Range

The objects Range and Cells had me stumped quite often at the beginning, and I still fail at them sometimes.
You can find a lot of tutorials online but the main lesson is that Cells needs to be qualified, otherwise your code will behave erratically.

Originally Posted by thyspaleis

So if I create a variable I have to assign this to a Worksheet. Isn't it possible to create a variable which is not linked to a worksheet?

Well, a variable (e.g. the number of rows) can be defined globally. But a range is a specific area linked to a worksheet. For instance the following code

Please Login or Register  to view this content.

creates two ranges in two different sheets using the same variable. The macro will run from both sheets. Please note however that I'm not checking for their existence.
The With statement is used to save typing, otherwise we should write

Please Login or Register  to view this content.

HTH