Solved

How To Write This in 2007 Syntax

Posted on 2011-02-28
52
224 Views
Last Modified: 2012-06-27
Attached is a very generic version of a current project. Initially, the first five cells of each row in the UserCost Table in the Costs Worksheet were supplied by another method within the VBA  program.  However, with the need to have the two Tables synced, I manually inserted the appropriate formulas that would allow for the first five cells of the UserCost Table to be synced with the Log Table in the LogFile Worksheet.

Where the difficulty arises is writing the VBA code so that when a new row is inserted, those same formulas can be written into each cell.  I was able to do this in the frmUnits Code, but it's not translating over.

The code is in Sheet3 ("LogFile") and starts "With Sheet4".  Those lines of code in question are commented out, as errors kept arising.  Would anyone know how to write this type of code?

Thanks
TableSyntax.xlsm
0
Comment
Question by:Cook09
  • 27
  • 25
52 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I haven't looked at the logic yet, but I did find syntax errors.

Shouldn't the With Sheet4 code be like this?

Dave
With Sheet4
                      .Activate          'Defined Names
                      .Rows([nSht3VisibleRow]).Hidden = False
                      .Rows([nSht3VisibleRow]).Interior.Color = 4210752
                       Application.EnableEvents = True
                      .Range(.Cells([nSht3NextRow], Range("Log[IDX]").Column), .Cells([nSht3NextRow], [nSht3LastCol])).Interior.Pattern = xlNone
                       Application.EnableEvents = False
                      .Cells([nSht3NextRow], Range("UserCost[IDX]").Column) = .Cells([nSht3LastRow], Range("Log[IDX]").Column) + 1
                       .Cells([nSht3NextRow], Range("UserCost[IDX]").Column).FormulaLocal = "= Log[[#This Row],[IDX]]"
                      .Range(.Cells([nSht3NextRow], Range("UserCost[Name]").Column)).FormulaLocal = "=Log[[#This Row],[LABs]]"
                      .Range(.Cells([nSht3NextRow], Range("UserCost[Project_ID]").Column)).FormulaLocal = "=Log[[#This Row],[Project_ID]]"
                      .Range(.Cells([nSht3NextRow], Range("UserCost[Date Out]").Column)).FormulaLocal = "=Log[[#This Row],[Date Out]]"
                      .Range(.Cells([nSht3NextRow], Range("UserCost[Year]").Column)).FormulaLocal = "=Log[[#This Row],[Year]]"
                     
           End With

Open in new window

0
 

Author Comment

by:Cook09
Comment Utility
Dimille,

I guess I need to take a look at the workbook that I attached. What you have is exactly what my TableSyntax shows.  Maybe, in switching between books, the code got upgraded in one book and not transferred over.

 Part of the issue is the error that it doesn't recognize the UserCost Table.  The VBE seemed to prefer the .Cells versus the .Range.  It didn't have any initial compile errors, but still hiccuped when it came to the .Cells portion.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I added a right parenthesis  - e.g., see below

.Range(.Cells([nSht3NextRow], Range("UserCost[Name]").Column)).FormulaLocal = ...

Before it was .Column).FormulaLocal and that syntax is corrected to

.Column)).formulaLocal

This is no help?

Dave
0
 

Author Comment

by:Cook09
Comment Utility
Okay, I see that for the .Range that you did.  Let me run that.
0
 

Author Comment

by:Cook09
Comment Utility
I keep getting, the item with the specified name wasn't found. I checked the Table Names for both and they appear to be correct.  Maybe I'll try renaming one, unless something else is occurring.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
where is that error occurring for you?  Do you get a clean compile?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I'm now getting that.  I had to declare nIDXValue as Public Variant in a public module without the brackets and change all the [nIDXValue] to nIDXValue.  I never got into the habit of using the [] evaluate brackets.  Am I missing something?  Let me understand that one so I can help.

Dave
0
 

Author Comment

by:Cook09
Comment Utility
For this page yes, there were a couple of left over items, that I cleaned.  But, this was fine.
0
 

Author Comment

by:Cook09
Comment Utility
The biggest advantage of using the [ ]'s that they can be used as a defined name, either tied directly to a cell or range, or by itself.  It's the easiest way of having variables available to all modules without having to declare them in a Public Variable, which can lose their value, if an error occurred, etc.

It's best use would be in a Database sheet, that was thought out and the variables, once assigned, just need the [ ]'s to recall their value.  I have  had one, maybe two, instances, where the Range("var") was needed,  but that was rare.   Unless, they are cleared out, they remain forever.  Which one must remember to do, if the variable could be used for several conditions, like a User Name, etc.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I'll have to look that up sometimes.  I did google on it and they frowned on it: http://www.excelforum.com/excel-new-users/719122-what-do-brackets-mean-in-vba.html



Here's your solution (I believe):
With Sheet4
                      .Activate          'Defined Names
                      .Rows([nSht3VisibleRow]).Hidden = False
                      .Rows([nSht3VisibleRow]).Interior.Color = 4210752
                       Application.EnableEvents = True
                      .Range(.Cells([nsht3nextrow], Range("Log[IDX]").Column), .Cells([nsht3nextrow], [nSht3LastCol])).Interior.Pattern = xlNone
                       Application.EnableEvents = False
                      .Cells([nsht3nextrow], [UserCost[IDX]].Column).Value = .Cells([nSht3LastRow], [Log[IDX]].Column).Value + 1
                       .Cells([nsht3nextrow], [UserCost[IDX]].Column).FormulaLocal = "= Log[[#This Row],[IDX]]"
                       .Cells([nsht3nextrow], [usercost[name]].Column).FormulaLocal = "=Log[[#This Row],[LABs]]"
                      .Cells([nsht3nextrow], [usercost[PID]].Column).FormulaLocal = "=Log[[#This Row],[Project_ID]]"
                      .Cells([nsht3nextrow], [UserCost[Date]].Column).FormulaLocal = "=Log[[#This Row],[Date Out]]"
                      .Cells([nsht3nextrow], [UserCost[Year]].Column).FormulaLocal = "=Log[[#This Row],[Year]]"
                    
           End With

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
nIDXValue is not declared anywhere, nor is it a named range.  I get compile error using it.  As a public variable defined, I get by ok.  The shorthand sounds appealing, but I don't think I'm going to change my habits just yet.  I do see the appeal in using it with tables, however (Very nice, that)- and perhaps range names and cell references.  Seems like a throwback to the old Lotus 1-2-3 programming names.  Wonder if you can build a formula with it?  

I need to read up on how brackets are used.

Is it working for you now?

Dave
0
 

Author Comment

by:Cook09
Comment Utility
Well, I have run into a number of instances where the Range("var") did not work and/or was cumbersome to use. It may be to each their own, but when I can easily manage the variables in one place and not have to worry about them getting lost due to an error or the Sub ending, etc.  It just seems easier.  I'm using defined names more than in the past, and not having to worry about whether they are declared Public or not.

There may be a drawback somewhere, and then one will need to adapt for that situation, but for 90% of one's code the [ ]'s are the way to go.  Defined names (Name Manager) are talked about a lot, not used a lot.  These just seem easier.   Just my opinion though.
0
 

Author Comment

by:Cook09
Comment Utility
Yes, I saw that and named it to a cell within the Database.  It's that easy.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
is [nIDXValue] a defined name?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
ok - it wasn't defined in my copy
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
well, that's one way to see a list of all your variables on the spreadsheet :)  I'm getting your approach.  I'm not there, but I do get it and the variables are very static, yes?

:)

dave
0
 

Author Comment

by:Cook09
Comment Utility
As far as building formula's, you can see that I use one variable to add upon another one.  Yes, it's like some of the older programming that I used to do, where variables where in one place, text in another, etc.  Personally, it seems better to manage them in one location.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I see it.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I've never seen this command either - and it doesn't compile.  Is this another error?  See BOLD:

Public Sub CommandButton1_Click()
'insert selected costs into Costs sheet
Dim i As Integer, ir As Integer, lR As Long, lC As Long
Dim lLastRow As Long, lLastVisRow As Long
Dim rngCostRow As Range, rngCostVal As Range
Dim CostInfo As Variant, rngCostCell As Variant, nIDXValue As Variant, mycell As Variant
Dim arrCost() As String
Dim arrUnits(12) As Double
Dim ctrl As Control


SetApp False
With Sheets("LogFile")
 Set rngCostRow = .Cells(ActiveCell.Row, Range("Log[Cost]").Column)
ir = rngCostRow.Cells.Row
 i = .Cells(ir, Range("Log[IDX]").Column)
End With
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Also, another anomoly - you call Userform_Activate and yet when Load Userform is done, it runs the activate command.  Did you know that the activate routine was being called twice in a row?
0
 

Author Comment

by:Cook09
Comment Utility
No, that actually goes to a function that I forgot to include.  Instead of using the:

With Application
 .EnableEvents = False ...

I just wrote a function, which should have been with the module, but it makes the setting of the App Events easier.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
ok - do you still need my help, or should I keep plowing thru this?

Dave
0
 

Author Comment

by:Cook09
Comment Utility
That may be something to look at.  What I can't figure out is why it won't recognize the Table on the Costs sheet?
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - I wrote that function and added [nIDXValue] to the database tab and now finally get a clean compile
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I get recognition.  Exactly what line is causing a non recognition?  did you see the code I posted a few posts ago - I thought I fixed that.

dave
0
 

Author Comment

by:Cook09
Comment Utility
Can you get the information to the UserCost Table, or I just renamed it CatCost to see if that made a difference?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I can and am in my version I've been patching...
0
 

Author Comment

by:Cook09
Comment Utility
I renamed it back to UserCost and will try what you put together.
0
 

Author Comment

by:Cook09
Comment Utility
Maybe there is something wrong with my Table, or maybe somehow it's tied to another book, but it will run down fine until the .Cells or the .Range, then it says it can't find it.  The other Table is fine, but may need more testing.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
Here's my version:

Dave
TableSyntax-r4.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
These two lines are redundant, one setting value then the next changing to a formula

There's NOTHING at #ThisRow on the Log sheet, so you're going to get #Value errors.

what exactly are you trying to put in the first so many columns of this new row?

Dave
0
 

Author Comment

by:Cook09
Comment Utility
I'm getting an error in MyCell, trying to find it.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Well, look at the table - is it filled with #VALUE???

That could be why myCell is not rendering...

:)

dave
0
 

Author Comment

by:Cook09
Comment Utility
When it was manually put into the cell, it seemed to sync, via #This row, with the other spreadsheet.  As I changed the values in Log, the values were changed in UserCost.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
well, its not the same row now, I don't think...  Its adding an entire new row
0
 

Author Comment

by:Cook09
Comment Utility
That is interesting, and it may be due to my changing the name of the Table, because in the original it was all in Table Syntax, not Sheet!Range.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - it might be useful for me to see the original?
0
 

Author Comment

by:Cook09
Comment Utility
It's suppose to add a row and then place the formula within the cells, if it's row 5 it may not come back with anything as there is no row 5 in the LogFile Log Table.  My Question was how to get the table to accept formulas, without erroring out.  I know it will work if placed correctly, because the others (1-4) worked.  Just kept getting the not recognized errors.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I'm getting the formulas.  Did you try my version of the workbook?

Dave
0
 

Author Comment

by:Cook09
Comment Utility
This is how the original LogFile[Log],[Cost] formula looked like.



=IF(Log[[#This Row],[Year]]<1,0,SUMPRODUCT((Log[[#This Row],[IDX]]=UserCost[IDX])*(Log[[#This Row],[Project_ID]]=UserCost[PID])*UserCost[Total_Costs]))
0
 

Author Comment

by:Cook09
Comment Utility
The MyCell was problematic.  I was going to take yours and place it within my original, and see how that took.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
ok - I'm working it - building knowledge around this table manipulation stuff.  That formula does indeed work, and its in the LogFile tab
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Riddle me this.  Is the cost update supposed to be adding a new row, or modifying an existing row?  Thanks

Dave
0
 

Author Comment

by:Cook09
Comment Utility
The Cost update does not add a new row.  The little oddity to this is some of the code will not work unless one is in the LogFile worksheet.  You may have noticed, I don't know it it's included, but parts of the code specifically Activate Sheet3 or it won't run correctly.  Although, I think those are the one's that transfer other formulas and it needs to have Sheet3 activated.

What I'm attempting to do will also rid the code of that, for the most part.
0
 

Author Comment

by:Cook09
Comment Utility
Dave,
I ran the code on my original and it did not error out and seemed to place the formulas where they need to be.  It looks like a success to me.

Ron
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
So I was working with a partial file?
0
 

Author Comment

by:Cook09
Comment Utility
Yes, but I incorporated all those areas that were needed, as the other parts worked.  I still don't know why it kept erroring out on my partial original.  I had taken quite a bit of time to make sure that the other code, which is used for different purposes, didn't keep this from working.  To have put in the entire book would have really confused things, and parts of it are confidential as well.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - I was wondering why it felt like I was working in the dark.  Yea, I got my version to finally popup the userform and accept changes...

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Well, glad I could help.  And, learned quite a bit about tables, just now.  Kind of feels like Clipper (from the late 80's).

Dave
0
 

Author Comment

by:Cook09
Comment Utility
What I just got a notice on, in deleting the Costs Row, was that it was set up as a Total Row.  I need to check and find out how that happened.
0
 

Author Comment

by:Cook09
Comment Utility
Well, as I just reported, there is still a lot about Tables that I need to learn, but seeing how they are integral to 2007 and 2010, I'm trying to learn as much about the syntax on these as possible.  There really is very little written about them.  

The best help for me was to use the Locals Window and in stepping through the code, seeing all that is available.  That was how I came up up with my lLastRow formula.  Not, that the (xlup) wouldn't work, but this is just as reliable and Table specific.  It also keeps me Table (list Object) focused.
0
 

Author Closing Comment

by:Cook09
Comment Utility
Thanks for the quick response and the continued testing until it was tweaked just right.  Although, I guess you really nailed it the first time.  It just took me a while to figure it out.

Ron
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Outlook Free & Paid Tools
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now