I'm trying to develop an Excel application that asks our 4D database for information. To do that, I built a query builder and it works. Now I want to make it more generic so that when I call the query builder, I can pass it a range in which the tables and fields the query is based on are stored. Here is a line where I call the sub and pass it the parameters:
QueryDatabase Worksheets("TablesAndFields").Range("A2:R20"), Worksheets("TablesAndFields"), Worksheets("Import")
Here is the first line in the sub:
Sub QueryDatabase(QuerySpecs As Range, QuerySheet As Worksheet, TargetSheet As Worksheet)
One of the things I need to do is have the VBA figure out the row of the last fields in the various columns. Here is my current code:
LastRowReportTables = QuerySpecs.Offset(0, 3).End(xlDown).Row
LastRowQuery = QuerySpecs.Offset(0, 6).End(xlDown).Row
LastRowSort = QuerySpecs.Offset(0, 14).End(xlDown).Row
This returns the same value for all 3 of them (the second line of the range). It seems to do this regardless of which cells have values in them. For instance, in the case of the range specified above it will return 3. If the range is "A22:R40" it returns 23. What I really need is for it to return the row relative to it's position in the range, but I could fake that if necessary by subtracting the largest multiple of 20 less than the result. (I'm formatting my query builder to fit in 19 rows + a buffer row.) So far, I haven't even been able to get it to return different results for the LastRow variables.
In addition to the Offset method you see above, I've also tried putting it in a With QuerySpecs... End With block. I don't remember the exact result, but I couldn't get that to work either.
The next thing I will need to do is pull values out of the various cells kind of like this:
strStartCell = QuerySpecs.Offset(0, 18).Value
This throws a Run time error 13: Type mismatch. Does anybody have any advice on how to accomplish my goals? Thank-you!
I suggest reading the documentation of the resources you are trying to use.
This can be done by selecting the word which you want to search in the documentation, then press the [F1] key, that will take you to the Microsoft documentation page (i.e. if you select Row and press [F1] will take you to the page Range.Row Property (Excel).
Reading the documentation for Row and Offset will help you understand these Range.Properties and the returns you are getting from your code.
To get the last non-empty row in a range of one column, assuming all data in the range is continuous (i.e. there are no empty cells between rows with content), use these lines:
With WorksheetFunction
LastRowReportTables = .CountA(QuerySpecs.Columns(4))
LastRowQuery = .CountA(QuerySpecs.Columns(7))
LastRowSort = .CountA(QuerySpecs.Columns(15))
End With
Note: If the "QuerySpecs" range is a kind of database, all columns in the fields should have the same number of records, if so, it should be sufficient to obtain the last row for one field only.
To obtain the value of a cell within a range, use this line:
This returns the value of the field (column) 18,row 13 of the QuerySpecs range
strStartCell = QuerySpecs.Cells(13, 18).Value2
The Run-time error 13: Type mismatch is generated when trying to assign an array to a string variable.