Number DIMs automatically

Ochimus · Apr 4th 2013

Never faced this before, but sure someone has the answer.

Form currently has 10 'named' cells in each of 4 rows. So row 1 has "Open1", row 2 "Open2", etc.

Which means I have forty DIM statements already.

User NOW wants an "automated option" where a click of a button adds more rows. Which means ten more DIMs per row!

Is there a "shortcut" where I can set a "master DIM" (e.g. DIM "Open & i", then set i from 1 to whatever so it cycles through them for as many rows as necessary?

Or has anyone got a better way to solve this?

All inputs welcome whatever approach you can suggest.

Ochimus

AAE · Apr 5th 2013

Re: Number DIMs automatically

Explain more clearly.
By "open" do you mean show hidden rows, insert rows . . . ?
What, exactly, are you "opening"?

Perhaps you just need to dimension a Long type variable, and use an input box to prompt the user to enter a number for the quantity of rows.
Pass this entry to the variable, say we call it >> i

Create a loop

Code

Dim i as Long, j as Long
Dim MyString as String


j = Application.Inputbox("Enter a number",type:1)


For i = 1 to j
      MyString = "Open" & i
      'other code
next i

Display More

Ochimus · Apr 5th 2013

Re: Number DIMs automatically

Morning AAE,

Sorry if the outline was unclear. Should have given the attached worksheet forum.ozgrid.com/index.php?attachment/52682/ which shows the relevant section of the full file.

As you see "Open" is one of the 10 named cells in each row. Each one holds the "Opening Balance" for its' row just as "Close1" and "Close2" are the named cells holding the closing balances for thier relevant rows.

The Sub "Rowset" in the attachment is a 'Private Sub Workbook_Open()' in the original ensuring all existing names are removed and renewed automatically.

Hopefully you will see what I have, what I need to achieve - and why I would value a shortcut. If the client decides they want 100 rows that DIM block will be longer than War and Peace!

If anyone has a simpler way of setting the range name suite be delighted to learn.

Ochimus

**royUK** · Apr 5th 2013

Re: Number DIMs automatically

Use a Table & spend a few minutes explaining to the user how to tab through a Table when entering data

Ochimus · Apr 5th 2013

Re: Number DIMs automatically

Roy,
Bear with me if I have misunderstood this, but I thought that in tables you reference table "names" in a formula by using "structured references" instead of cell references such as A1 and R1C1?
Which means I would STILL have to "Dim" each table "Name" - putting me straight back to Square 1?

Ochimus

**norie** · Apr 5th 2013

Re: Number DIMs automatically

Why have you declared a whole bunch of variables that you never use, and never need to use?

I can remove all these declarations and the code will still compile.

Code

Dim CHOOSE As Range


Dim CHECKSUM As Range


Dim FRESH As Range


Dim ADDED As Range


Dim ASSIGN1 As Range
Dim ASSIGN2 As Range
Dim ASSIGN3 As Range
Dim ASSIGN4 As Range


Dim CASCADE1 As Range
Dim CASCADE2 As Range
Dim CASCADE3 As Range
Dim CASCADE4 As Range


Dim CLOSING1 As Range
Dim CLOSING2 As Range
Dim CLOSING3 As Range
Dim CLOSING4 As Range


Dim CYCLEP1 As Range
Dim CYCLEP2 As Range
Dim CYCLEP3 As Range
Dim CYCLEP4 As Range


Dim CYCLEC1 As Range
Dim CYCLEC2 As Range
Dim CYCLEC3 As Range
Dim CYCLEC4 As Range


Dim LIMIT1 As Range
Dim LIMIT2 As Range
Dim LIMIT3 As Range
Dim LIMIT4 As Range


Dim MTOPP1 As Range
Dim MTOPP2 As Range
Dim MTOPP3 As Range
Dim MTOPP4 As Range


Dim MTOPC1 As Range
Dim MTOPC2 As Range
Dim MTOPC3 As Range
Dim MTOPC4 As Range


Dim OPEN1 As Range
Dim OPEN2 As Range
Dim OPEN3 As Range
Dim OPEN4 As Range


Dim WITHDRAW1 As Range
Dim WITHDRAW2 As Range
Dim WITHDRAW3 As Range
Dim WITHDRAW4 As Range

Display More

Ochimus · Apr 5th 2013

Re: Number DIMs automatically

Norie,

The variables are declared because that is how the entire file works -e.g.

Code

For Q = 1 To 4
 
'***************************************************************
                        'Block has NO Overall Limit
'***************************************************************
   
'1. If there is NO Overall, NO Monthly and NO Cycle Limits:
    If Range("LIMIT" & Q) = "" And Range("MTOPC" & Q) = "" And Range("CYCLEC" & Q) = "" Then
'Take everything:
       Range("ASSIGN" & Q) = Range("CHECKSUM")
       Range("ADDM" & Q) = Range("ADDM" & Q) + Range("ASSIGN" & Q)
       Range("CLOSING" & Q) = Range("CLOSING" & Q) + Range("ASSIGN" & Q)

Display More

Ochimus

**norie** · Apr 5th 2013

Re: Number DIMs automatically

You haven't used any variables in that code apart from Q.

"LIMIT" & Q is just a string, and Range("LIMIT" & Q) is referring to a named range called LIMITQ, where Q is 1,2,3,4.

Ochimus · Apr 5th 2013

Re: Number DIMs automatically

Norie,

Readily admit I cannot see how the Code will work if I have not declared every variable?

The extract below was one of my earliest efforts at "setting the Ranges automatically". I tried to have a "generic" range name copied into X rows (set by variable "Q") in the rows set by variable "j". So B4 would become "Limit1" and E7 would become "CYCLEC4"

It didn't work then, but if anyone can guide me on what needs to change to MAKE it work I would be extremely grateful.

Code

Sub TestDim()


Dim Q As Integer
Dim j As Integer


For j = 4 To 7


For Q = 1 To 4


Range("LIMIT " & Q) = Worksheets(Sheet1).Range("B" & j)
Range("MTOPC" & Q) = Worksheets(Sheet1).Range("C" & j)
Range("CYCLEC" & Q) = Worksheets(Sheet1).Range("E" & j)


     '***************************************************************
     'Block has NO Overall Limit
     '***************************************************************
     
'1. If there is NO Overall, NO Monthly and NO Cycle Limits:
    If Range("LIMIT" & Q) = "" And Range("MTOPC" & Q) = "" And Range("CYCLEC" & Q) = "" Then
         'Take everything:
        Range("ASSIGN" & Q) = Range("CHECKSUM")
        Range("ADDM" & Q) = Range("ADDM" & Q) + Range("ASSIGN" & Q)
        Range("CLOSING" & Q) = Range("CLOSING" & Q) + Range("ASSIGN" & Q)
End If


Next Q


Next j


End Sub

Display More

Ochimus

**norie** · Apr 6th 2013

Re: Number DIMs automatically

That code just isn't right, mind you I'm not sure what it's meant to do.

If the first part is meant to be naming ranges it should be like this.

Code

For Q = 1 To 4 
             
   Worksheets("Sheet1").Range("B" & j).Name ="LIMIT " & Q   
   Worksheets("Sheet1").Range("C" & j).Name = "MTOPC" & Q  
   Worksheets("Sheet1").Range("E" & j).Name = "CYCLEC" & Q

Note I've assumed Worksheets(Sheet1) is meant to refer to Sheet1, otherwise the code would fail unless you've declared a variable called Sheet1 somewhere else and given
it a value that is the name of an existing worksheet.

Not sure what's happening here, are ASSIGN1, ADDM1, CLOSING1, ASSIGN2, ADDM2, CLOSING2 etc existing named ranges

Code

Range("ASSIGN" & Q) = Range("CHECKSUM") 
Range("ADDM" & Q) = Range("ADDM" & Q) + Range("ASSIGN" & Q) 
Range("CLOSING" & Q) = Range("CLOSING" & Q) + Range("ASSIGN" & Q)

Also, why are you naming individual cells?

Why not name entire columns or rows of data?

Or even name the entire range.

Ochimus · Apr 6th 2013

Re: Number DIMs automatically

norie,

Thanks for the input.

1. I knew the code wasn't right, which is why I said it didn't work.

2. Worksheets(Sheet1) DOES refer to Sheet1 - simplest way to prevent problems when the End User decides unilaterally to rename the sheets (don't ask. . . I had hair before)

3. I didn't include the code setting "Assign", AddM" or "Closing" catagories because they would only have followed the same principle as the three examples I gave.

4. I ended up naming cells individually because in part of the 'full' programme the End User wanted specific cells in the SAME 'catagory' to do different "things" to the others. (E.g, "Closing1" does something different to Closings 2 - 4. Also I find it's easier to write and check code using range name(s) than having to remember cell references such as A1 and R1C1.

5.Haven't tried your code yet, but every confidence you have solved my problems. I will set the code so "j" starts at row whatever, and give the end user a cell that sets 'Q'.

Thanks to everyone who responded and hope others find the thread of use.

Ochimus

**norie** · Apr 6th 2013

Re: Number DIMs automatically

2 Worksheets(Sheet1) will not work unless their is a variable named Sheet1, if you are trying to refer to a worksheet with the codename Sheet1 then you would just use Sheet1, you don't need the Worksheets part.

4 That still doesn't quite explain why individual cells. You could have a column named CLOSED and you could refer to and treat each of the cells in that column individually.

So you would still have your range names but you would have a lot less of them to deal with.

Ochimus · Apr 7th 2013

Re: Number DIMs automatically

norie,

Many thanks for the updated response.

You are as usual spot on about Sheet 1. Must have been one of my increasingly "senior moments".

Will try the Column concept tomorrow. If I had looked at it before the notes must have been eaten by the granddaughter's hampster.

Thanks again

Ochimus

Number DIMs automatically

Participate now!

Share