VBA Set Up Variable and loop

[sswcharlie]

Hi

I am setting a variable etc. The loc1 is a dynamic named range, storing integers starting at G5 there is a changing total number of entries in it.
Another Macro will have to get the value in each row and then use it in a subroutine

Sub CheckingVariable

Dim LastRow As Long
Dim i As Long
Dim vegetables  'variable changes as per each row
vegetables = integer
LastRow = Range("loc1" & Rows.Count).End(xlUp)
vegetables = 0  ' initialise starting value. Does this need to be done?
Range("loc1").Value = vegetables
MsgBox (" " & vegetables)  ' to check to see if correct number is shown for each row


For i = 1 To LastRow

Next i

End Sub

[/CODE}

What I am trying to do is use a loop to check each row until end of list, and the number in the variable may change as it proceeds down the list.

With the 'Dynamic Named Range'  loc1 it shows up as an error,  do I have to declare it, but should the macro find it anyway?
In the For i = 1 To LastRow  the 1 shows up as an error?

Comments would be appreciated.

Charles

 

[Smallman]

Hi Charlie

What are you attempting to do?

I would prefer to see a file before and with data after the procedure has run.

If you just say what you are trying to do there may be a better way than looping through your cells. It is not very clear so please restate and take the time to provide a before and after file.

Take care

Smallman

 

[sswcharlie]

Hi Smallman

Thanks for your comments. My apologies for not making clear enough. I am attaching the workbook again, this time it has a lot of comments and details on before and after.

The main thing I am looking for at the moment is to know if the macro is using the correct numbers, my learning curve. It is a hobby project (I am retired) for model railroad.
Appreciate your patience
Regards
Charles

 

[NARAYANK991]

Hi Charles ,

I suggest you first read up on VBA ; this will help avoid frustration when you cannot get things done.

The first step is to learn about the different data types in VBA , how to declare them and how to assign values to them.

1. vegetables = integer ; I do not know know what your actual intention here is , but if it is to declare a variable named vegetables as of type Integer , then the correct way to do this is :

Dim vegetables As Integer

However , though this is a hobby project , by the looks of it , it is going to be quite a complex application , with at least a hundred lines of code , if not more. In such a case , especially if you will be modifying the code for quite some time in the future , it is good programming practice to name variables meaningfully. Naming a variable vegetables does not fall in this category.

2. LastRow = Range("loc1" & Rows.Count).End(xlUp)

LastRow has been declared as a variable of type Long ; what the right side is doing is returning a range reference ; to assign the actual row number of the last row to the LastRow variable , you need to do :

LastRow = Range("loc1" & Rows.Count).End(xlUp).Row

3. Range("loc1").Value = vegetables

The string loc1 is not to be used here ; in fact what this will do is assign the value 0 ( since the variable vegetables has been initialized to 0 in the previous statement ) to the worksheet cell LOC1 , since with Excel 2007 and later , this is a valid worksheet cell address. However , since your file is a .xls file , the reference to LOC1 will generate an error.

You will need to change the above line to :

Range(loc1).Value = vegetables

where loc1 will be a variable , to be declared as of type String , and assigned some relevant value.

You can see that the actual project is some way off ; try to first put down on paper the first thing to be done , not the complete project description.

Try to convert this description to what is called pseudocode ; thereafter move on to converting the pseudocode to actual VBA statements.

Help is always available on this forum.

Narayan