Changing Column values with VB code

[00Able]

I dont understand vb at all apparently....

ok this code works:

Please Login or Register  to view this content.

And in column B it displays "1" in b2, "2" in b3, "3" in b4, etc all the way to "18" in b19...
I guess that makes sense...
but what I want is to say: "51" in b2, "52" in b3, "53" in b4, all the way to "67" in b18, but in b19, I want it to say "73",

so I change the code to say:

Please Login or Register  to view this content.

OK, I know I know, I did not address the "73" in b19, but I just wanted to tackle the 51 - 67 issue first, well when I change the code, it says "Object not found"...
what am I doing wrong???

Attached a workbook, you are looking at the "Order Log", sample of desired results highlighted in yellow

[shg]

Plerase take a few minutes to reread the forum rules, and then retitle your thread.

Thanks.

[jaslake]

Hi 00Able

Firstly, your thread title won't be acceptable. I can't tell you to change it but I'd strongly recommend it. I'd think an Administrator will insist on it.

Secondly, show us what your issue is...perhaps we can help. You give us some code but we have no clue what your file looks like. Help us to help you...show us what you have...show us what you want for results. Perhaps we can help...perhaps not.

I've helped you before...take my suggestions...they're for your benefit.

[00Able]

Ok, retitled, and added attachment, just a bit frustrated at my lack of understanding...and not sure how to title this one...sorry

[teylyn]

00Able, that title is no better. Please read the forum rules and try again.

[00Able]

hoping that one is better, also in the attachment, you will notice, that now my userforms in the other worksheets will not work, object not found?

[MarvinP]

Hi 00Able,
I looked at your code for a while trying to find why some forms showed and others didn't. I have some suggestions to help with code.

1. ALWAYS use Option Explicit at the top of each module or Sheet or Form's Code. When you press Compile it will check you code for undefined or incorrect stuff. This has saved me hours of hunting for spelling errors or stupid mistakes.

2. Look at Scopes of Variables. I saw you had Dim statements above any of your code. I've never seen this. Are they supposed to be Global variables? Read http://www.cpearson.com/Excel/Scope.aspx for a good explaination. It seemed to me you might be thinking you were using variables that had not yet been set yet. This may have lead to your form.show errors.

3. Learn to set breakpoints and step through your code. Learn to use the Immediate Window to see what things might be equal to. Learn to hover over variables to see what their values are while stepping through code. http://www.cpearson.com/Excel/Debug.htm

4. On your Userforms I didn't understand how you had put some controls on them. For example looking at the Attendance form and at the Properties window. If I click on the Line Combo Box the Properties shows that I've clicked on cboLine ComboBox. BUT when I click on Zone 1 there is no name in the Properties. What is this? What kind of control? This problem may be related to using MS.Forms controls which may need to be in a reference somewhere.

5. Try to keep your code confined to only what is installed on most everyone's machine. I had activex controls on many programs in the past. As soon as I gave them to a customer they wouldn't work as they didn't have that control on their machine or need to reference it. This is like using an Analysis Pack formula and giving it to your friend where the workbook crashes as he doesn't have the Analysis Pack on his machine.

I hope all the blabber helps. Keep reading other peoples code.

Also I saw your posted code where you had

Please Login or Register  to view this content.

After writing this way too many times I've decided to write it like

Please Login or Register  to view this content.

Which does the same thing but IMHO is more obvious on what column is being used.

Make your code more readable by using names for variables that tell what they are.

Please Login or Register  to view this content.

Is my standard.

Instead of using i and j for counters use the variable name RowCtr and/or ColCtr. That way when you read your code you won't confuse what is being counted.

Pretty soon you will be writing and reading code like a book instead of trying to figure out what that darn i, j and k counter variables are really counting.

I hope this helps. I never did find why frmAttendance wouldn't show. What made me mad is that frmCalendar and frmOrders did show but the other two didn't. WHY?

[00Able]

What made me mad is that frmCalendar and frmOrders did show but the other two didn't. WHY?

I am pretty sure I figured out why they will not show, it was my own fault, when clearing the workbook of sensitive information, I deleted the dynamic named ranges that these 2 forms were using, there for the object was not able to be found. My bad...

It will take me awhile to review your feedback, but I will thoroughly and appreciate your insight, I am like a chimpanze learning this code, alot is watching how others make the code, then trying to make it adapt into my scenerio, so I get a piece here and a piece there, and hope they mesh together...one day, I am hoping to have a full understanding, just afraid that day is way in the future...until then, I will keep banging my head...lol

[MarvinP]

YES - Dynamic Named Ranges would hide deep enough and cause problems. You're way ahead of the normal Excel user if you play with them.

DonkeyOte scolded me one day as DNRs are Volatile and he feels they slow things down a lot. I think they have a use but are real hard to diagnose and track when other things go bad. Read one of his posts and follow his Volatile link in his signature line.

We all have our own priorities. Thanks for letting me know why the forms wouldn't show.

Go read those Pearson web pages.

[00Able]

Please Login or Register  to view this content.

I am struggling with this code, can you tie it to the code so maybe I can see the big picture...,

I like the dynamic ranges, it makes data entry more efficient, ...trying to overcome my volitle issues, I have begun to read up on volitle isses, but I find myself a bit overwhelmed as I think everything I do is "Volatile", maybe that is my self-destructive behaviour showing itself in the form of excel...lol

But I regress, I have faith, I will get there, with lots of help

[MarvinP]

I use the

Please Login or Register  to view this content.

Most every time I write code now.

LastRow is a variable name I have Dim above
Rows.Count is the maximum number of rows in this version of Excel - Way down at the bottom.
Cells ( Row, Column) specifies a range you want to start in.
"A" is the column you are wanting
.End(xlUp) is the same as pressing Ctrl-UpArrow to hit the next non blank above
.Row returns what row was found.

So in English this says
Go to the very last row in my worksheet at Column "A" and press Ctrl-Up and tell me what Row it stopped in. Let the variable LastRow equal to that number.

Read http://www.mvps.org/dmcritchie/excel/cells.htm
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
http://spreadsheetpage.com/index.php...e_size_ranges/ I like this site.

[DonkeyOte]

DonkeyOte scolded me one day as DNRs are Volatile and he feels they slow things down a lot. I think they have a use but are real hard to diagnose and track when other things go bad. Read one of his posts and follow his Volatile link in his signature line.

Not 100% accurate - DNR's constructed with Volatile functions (OFFSET, INDIRECT etc) are Volatile.

To be clear, I don't think I've ever argued that DNR's should not be used - just that they should not be used blindly - often the overhead isn't necessary - as always it comes down to context.

A volatile DNR is not really a concern if being used to source a Pivot or Data Validation.
If the DNR is being used en masse as a precedent range within formulae which are themselves inefficient (Arrays, SUMPRODUCT etc) it's generally a good idea to avoid a Volatile DNR construct [IMO]

Most functions are sufficiently efficient that they work only with the Used Range intersect of the Precedent range and as such the overhead in determining a "filled" range isn't necessary and will actually slow* the calculation
(*whether noticeable or not is an entirely different matter)

Notable others would disagree with me re: the above - it's very much a case of each to their own.

Apologies for sidetrack - just wanted to clarify

[MarvinP]

Hi DO,

In my pee brain DNRs and Offsets are in the same box What is an example of a DNR without using Offset or Index? I've searched looking for a DNR that doesn't use Offset and failed.

One Example is all I need.

Do you consider VBA named ranges that change with code, DNRs?

[DonkeyOte]

What is an example of a DNR without using Offset or Index?

INDEX is not Volatile (since XL97) - it is considered "semi-volatile" in so far as it is Volatile upon Open (ie will calculate) but not thereafter.

So

Please Login or Register  to view this content.

is 100% Volatile whereas

Please Login or Register  to view this content.

is not
(it is semi volatile - and that's misleading term IMO - "occasionally volatile" would be more appropriate)

To reiterate: how big an issue a Volatile construct is all depends on the context so it's not as simple as saying INDEX good OFFSET bad.

It should also be noted that no DNR will work with INDIRECT irrespective of Volatility concerns:

Please Login or Register  to view this content.

neither of the above would work
(in this scenario we are obliged to use an IF, CHOOSE or Evaluate method)

Do you consider VBA named ranges that change with code, DNRs?

If you're saying: VBA alters RefersTo fixed range then No.

A Dynamic Named Range (IMO) is defined by the fact that it is capable of resizing itself by virtue of the RefersTo construct alone.
(per the above examples - as #'s are added to A so the height of the Named Range alters)

It's something of a contentious issue ... the above is my own personal opinion - nothing more - just didn't want to be misconstrued / misquoted.