OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Problem with formula writing macro

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Jars28
General User
General User


Joined: 24 May 2012
Posts: 10

PostPosted: Thu May 24, 2012 11:46 am    Post subject: [SOLVED] Problem with formula writing macro Reply with quote

I wrote a small macro to write a formula to multiple cells. When I ran the macro all of the cells came up with Err:509. If i click on the individual cell and click the formula wizard button and then click ok, the formula works just fine.

So, the macro writes the formula into the cell just fine, I'm just not sure why I'm getting this error until I "confirm" the formula for each cell. I am on Mac OS-X in LibreOffice 3.5.2.

My code:

Code:

Sub Superpopulate

Dim counter500 as integer
Dim ColumnCounter as integer
Counter500 = 8
ColumnCounter = 0
Do Until ColumnCounter = 8
   Do Until Counter500 = 509
   oCell = ThisComponent.Sheets.Form.GetCellByPosition( ColumnCounter, Counter500 )
   oCell.Formula = "=VLOOKUP(" & Counter500 - 7 & ",$Data.$A1:$H500," & ColumnCounter + 2 & ", )"
   Counter500 = Counter500 + 1
   Loop
ColumnCounter = ColumnCounter + 1
Counter500 = 8
Loop
End Sub


I REALLY don't want to type out 4000 variations of this formula.

Background:

I am a warehouse manager. I wrote a program to import data from an accounting software CSV export file and create a materials form that lists items by room name. There is a column for item#, description, number spec'd, number staged, number sent, number used, number returned. This spreadsheet is to keep track of items that have been sent out to projects so we can properly bill our clients.

I am in the process of making it more user friendly for the techs checking out items. I created a second sheet that lists the items by item name, with a new column for room name, instead of by room. I have all the code written to sort the data and automatically add the room name to the new column. It was difficult to keep track of how many of each item went out when the same item can be found in many rooms.

The formula looks up a cell from a row on a "data" sheet by the index value in the first column. I am sorting the data on a "sorted" sheet (not referenced in the code) by item name which would use the same VLOOKUP formula. I need the information in the specific row on the first sheet "Form" to mirror the information on the "Data" sheet when it is changed. Items are added to this list throughout the multiple appointment installs. I will have to write some more code to add items to the list instead of just adding a row like normal....that will be the next step, lol.
_________________
Jared


Last edited by Jars28 on Fri May 25, 2012 11:26 am; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu May 24, 2012 1:26 pm    Post subject: Reply with quote

Nobody needs to write a program in order to apply one formula to many cells, row by row and/or column by column. This is what spreadsheets can do since 35 years. [Tutorial] Absolute, relative and mixed references
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Jars28
General User
General User


Joined: 24 May 2012
Posts: 10

PostPosted: Thu May 24, 2012 8:58 pm    Post subject: Reply with quote

The problem I am having is that each individual cell looks up a separate individual cell on the other sheet. If I spread the formula out across all of the cells, it adds 1 to each of the variables in the formula incrementally. I only want one of the variables to increase in the formula as I pull down and a different variable as I pull across. I can upload an example of what I'm working with, but I will have to remove confidential info before uploading sample files.
_________________
Jared
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Thu May 24, 2012 11:59 pm    Post subject: Reply with quote

Code:
=VLOOKUP(ROW(A9)-ROW(A$9)+1,$Data.$A$1:$H$500,COLUMN(A9)-COLUMN($A9)+2,0)
in A9 filled down and across.
I'm unsure about the 4th parameter because your code shows no value...
Code:
oCell.Formula = "=VLOOKUP(" & Counter500 - 7 & ",$Data.$A1:$H500," & ColumnCounter + 2 & ", )"
                                                                      Missing 4th parameter^

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Fri May 25, 2012 3:04 am    Post subject: Reply with quote

Hi

The same Formula as above, but without mysterious senseless extra Calculations:

Code:
=VLOOKUP(ROW($A1),$Data.$A$1:$H$500,COLUMN(B$1),0)


Karo
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Fri May 25, 2012 3:20 am    Post subject: Reply with quote

The mysterious sensless parts protect against the formula changes that would otherwise automatically occur after rows are inserted or deleted above row 9 or columns are inserted left of column A.

Ken Johnson
Back to top
View user's profile Send private message
Jars28
General User
General User


Joined: 24 May 2012
Posts: 10

PostPosted: Fri May 25, 2012 6:39 am    Post subject: Thank you Reply with quote

Yes! This worked out without having to run a macro. Thank you for helping me out. The offsets in my code were so the cells were filled below the header and the data was pulled after the fist column. There wouldn't be a case where something was added to the top or a column to the left. 500 rows is the limit I decided on after looking at the largest sales orders, of which the longest was 323.

The header and the columns would always remain the same. New items and their descriptions, etc will be added to the data page on a specific line with a user input box brought up by a button on the sheet. The left column on the data sheet referenced to by VLOOKUP will be rewritten each time new data is added and the 2nd sheet would be resorted. We highlight rows in different colors when items are added or removed, so some more code will be added to make sure all of the highlighted rows act properly. The 4th parameter was blank because the reference number column on the data list would be listed in ascending order. I could have also put "TRUE()".

I'm still not sure why my macro didn't work though...

More backround:
The data page is populated from the CSV export file from the accounting software we use and then formatted with the room names in bold and a blank line between rooms. The Form sheet lists everything as it appears on the invoice and a "sorted" sheet sorts everything out by the item description and adds the room name to the rooms column. I want to use the Form OR the Sorted sheet to update the qty sent on the data page. I will be exporting the form to PDF each time it's filled out, updating the qty sent on the data sheet, and adding a note of the date and the tech that took the item; all with a macro. The tech is going to sign the pdf with a touch-pad and a copy will be saved in a folder for the customer and another copy sent to the tech in an email. Another sheet in this file will be for the accountant that lists all the data she needs to update the invoice and bill the customer, as well as figure out who took what and when.....I am also working with BASE to keep track of all of these records, so I will eventually integrate all of this as a form for a database file.

Programming is what distracts me from what would otherwise be a sort of boring job of counting boxes. My goal is to automate most of my job and increase productivity so I can go back to college. (I am payed on salary, so as long as my job gets done I get payed)
_________________
Jared
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 25, 2012 7:26 am    Post subject: Reply with quote

All this can be solved very easily without a single line of stupid Basic code nor lookup formulas. Does the database of your accounting program allow connections from "outside"? Are there any ODBC or JDBC drivers to access that database?
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Jars28
General User
General User


Joined: 24 May 2012
Posts: 10

PostPosted: Fri May 25, 2012 9:01 am    Post subject: Reply with quote

Well...I already wrote the program to do everything stated above except for the form linking to a database. I already have a short form that links to said db and, for each customer, keeps track of small items that are not on the materials list that was generated from the sales order. You simply scan the UPC and all of the item info populates.

When I took on this position, everything was running on Mac and all of the materials list software was written in VB for excel with links to dead files and incorrect paths. There wasn't any user input to find the CSV file. There wasn't any user input on where to create the customer's folder and save the files when the data was imported. It did all of this automatically...and there was a lot of user error and problems because of this fact. They were running Windows in the background of all the Macs just so they could use the programs written by the former IT guy. They were very badly written anyways. I rewrote everything for LibreOffice and the programs run faster, with less errors, and with more features and without stupid Windows. ( I run Mint Ubuntu at home. )

I am a total newb when it comes to databases, so I will have a lot of studying to do to get everything working the way I want it to.

I am working on a second version of the import materials list that adds just a few more features:
-sorts by item description on a second sheet
-keeps track of who took what out without manual entry
-adds items to the list with the click of a button and a upc scan that populates the data from the items db and formats the row to show it as a change
-exports the pdf file to the customer's folder with a record number
(it already saves updates to the list in ods and pdf format, but I print out the list, have the tech sign, scan the signed doc, and manually enter the data. I need to keep signed records of the techs taking the items out and im trying to eliminate 3 steps of the process.)

Since technically all a database is is a fancy spreadsheet with custom input boxes and custom GUIs to report certain data, I might not even need to link it. I may just use the materials list as the database for the customer. The only downfall is that I'm still relying on the accountant to update inventory quantities when she bills the customer. (It removes the items she bills for from inventory.)

They don't want me to manipulate any of the data in the current accounting software, so that is not an option for me. The accounting software is pretty much bare bones when it comes to inventory tracking. It only changes the inventory count if you generate a sales order, or manually manipulate it. I wouldn't know where to begin if I was going to update quantities by linking to it. I would rather have my own database to keep track of inventory and use my own preferences on what to track. So, at audit time when things don't match up on both ends, I have my butt covered and the inventory was tracked real time when as it left my building.

BTW, my question is solved. Smile
_________________
Jared
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group