Ask Your Question

XanderN's profile - activity

2018-12-12 20:52:25 +0100 received badge  Famous Question (source)
2018-11-19 05:01:43 +0100 received badge  Famous Question (source)
2018-10-22 13:18:39 +0100 marked best answer Calc Basic - Access filtered data from macro code

Today I stood into another problem while helping in the creating of a Calc App powered by Basic macros.

The thing is, I'm using calc as a database and I have a sheet where I save all the input data as if it was a database table, and here lies the problem: I search row by row "manually" to find the row from where I get the rest of the data saved, but it is painfully slow, so I though using Calc Filters to speed up the search to only filtered data. Making the filters themselves is relatively "easy" but accessing to that filtered data on the macro is getting really tedious.

I have been reading the helping document called CalcAsASimpleDatabase and in page 17 it mentions how to copy the filtered data to another part of the document, but it never mentions how to just keep the Cell Range as a variable from which I can then rescue the data I need to show in another place.

Here is a simplified version of the db-like sheet I'm using as table for storing the data: Simplified table with autofilters

And here the filtered data I would like to rescue from it:

Filtered Data I would like to access in basic

That said, I would rescue more than 1 row of data in the real one, but for easiness here is just 1 row.

Thanks in advance for the help, I hope I have been clear, if not please let me know.

==== EDIT ====

First, here's a reinterpretation of the simplified version of what I'm making, which should make it easier to understand what I'm trying to achieve:

Simplified table adjusted to the "Form" Sheet

The code then read the "Form" Year, Month and Zone and uses functions to fill first the users data and days making a fillable calendar of some sort, which then is filled with data that was already saved, if it existed that is. With the info we have in Data, it would look like this:

Form sheet after loading data from Data sheet

As @Mike Kaganski suggested, here's the code that fills the data for each user (the user list is first generated from another sheet using another function) and that I'm pretty sure isn't optimized at all, it uses other functions and the macro should have globals which actually doesn't have, but at least gives a vision of how I'm loading the data right now, fullfilling Mike's suggestion:

Sub fillSavedData(month as string, year as string,zone as string)
dim origin,destiny, southzone as string
dim i,j,k,n,col, row, endrow,int,startrow,startcol, endcol, totrows as integer
'Where we are going to start filling data in the sheet
startcol=2
startrow=7
'Row numbers in Form sheet
i=startrow
do while isLastRegister(0,i,0)=false 'sheet, row, column; return true if the cell has data or false if not
 endrow=endrow+1
 i=i+1
loop
endrow=endrow+startrow
'we go through the Data sheet
k=startrow
southzone=zone
totrows=nrows(2,0,0)
endcol=daysOnMonth(month,year) 'month ...
(more)
2018-10-22 13:18:39 +0100 received badge  Scholar (source)
2018-10-22 13:18:15 +0100 commented answer Calc Basic - Access filtered data from macro code

The data in the sheet will be only touched by macros, so it would be safe to filter as in your admendment example. I wi

2018-10-22 13:09:41 +0100 commented answer Calc Basic - Access filtered data from macro code

I first considered to make an "app" that made use of Calc and Base, as Spreadsheets were needed for some operations, but

2018-10-22 13:02:24 +0100 edited question Calc Basic - Access filtered data from macro code

Calc Basic - Access filtered data from macro code Today I stood into another problem while helping in the creating of a

2018-10-19 18:39:56 +0100 received badge  Notable Question (source)
2018-10-19 09:26:00 +0100 commented answer Calc Basic - Access filtered data from macro code

My bad for not specifying how I select the range, I meant to select the range using Sheet(x).getCellRangeByPosition(a, b

2018-10-19 09:21:44 +0100 received badge  Popular Question (source)
2018-10-18 13:25:35 +0100 commented answer Calc Basic - Access filtered data from macro code

Thanks a lot for the advices! Yeah that aproaches to what I want to achieve, but I guess there is no way to get only vis

2018-10-18 12:21:17 +0100 commented question Calc Basic - Access filtered data from macro code

Thanks for the fast response. Well my plan was to use sheet filters described on the document linked above, yet I don't

2018-10-18 11:57:49 +0100 asked a question Calc Basic - Access filtered data from macro code

Calc Basic - Access filtered data from macro code Today I stood into another problem while helping in the creating of a

2018-09-13 22:21:14 +0100 received badge  Notable Question (source)
2018-09-13 12:14:06 +0100 received badge  Popular Question (source)
2018-09-13 12:12:31 +0100 edited question Libre Office Calc - How to write calc-functions inside a cell using Basic

Libre Office Calc - How to write calc-functions inside a cell using Basic I'm trying to add calc-functions inside cells

2018-09-13 12:07:02 +0100 received badge  Editor (source)
2018-09-13 12:07:02 +0100 edited question Libre Office Calc - How to write calc-functions inside a cell using Basic

Libre Office Calc - How to write calc-functions inside a cell using Basic I'm trying to add calc-functions inside cells

2018-09-13 12:04:26 +0100 commented question Libre Office Calc - How to write calc-functions inside a cell using Basic

Yeah indeed there should be a warning in .Formula method about not using localized versions, that was what was giving me

2018-09-13 11:42:30 +0100 edited question Libre Office Calc - How to write calc-functions inside a cell using Basic

Libre Office Calc - How to write calc-functions inside a cell using Basic I'm trying to add calc-functions inside cells

2018-09-13 11:22:25 +0100 commented question Libre Office Calc - How to write calc-functions inside a cell using Basic

My bad I didn't put my version, it is 5.3.6.1. It is also a localized one, in Spanish, so it is wrote "=SUMA(A3;A5)", c

2018-09-13 10:13:30 +0100 asked a question Libre Office Calc - How to write calc-functions inside a cell using Basic

Libre Office Calc - How to write calc-functions inside a cell using Basic I'm trying to add calc-functions inside cells