Syntax of code with coded ranges in "s

[rmunsun1]

I want to change this phrase:

.PrintTitleRows = "$4:$9"

To

.PrintTitleRows = (8 + Vendor_Count) : (12 + Vendor_Count)

But I can't get the syntax of where to keep quotes and all.

Can anybody please help me?

Thank you!

[TMS]

Try:

Please Login or Register  to view this content.

Regards

[rmunsun1]

Wow. Thank you for the very quick reply TMShucks! That is awesome.

I'm trying to apply the same principle with the following piece, but I'm just not smart enough to piece it together on my own...

Please Login or Register  to view this content.

Thank you very much!

[TMS]

I'm not entirely sure what you are trying to do here. What does it look like without the variables? What's with the adddress and the FALSE?

Regards

[rmunsun1]

So what I have is a couple of tables that, in a template, are large. The settings sheet populates the row titles and columns titles of the tables and then cuts down the columns and rows based on the number of vendors and metrics.

The top table gets cut down to a definitive size B4 to column (B+SQC_Count) and row(7+Vendor_Count).

The second table gets cut down but the print area should still be variable. So, I've put in a count function to count the number of data lines in the second table.

I put in that count using:

Please Login or Register  to view this content.

So the goal is to have a print area that has that "defined" first table and then "," and then the second table would be from column B, row (9 + Vendor_Count) to Column (B + Metric_Count) to Row (9 + Vendor_Count + indirect("B" & (11 + Vendor_Count))

I know it's convoluted, but I hope that makes sense?

[rmunsun1]

To answer your other questions: I was trying to use ADDRESS because it was the only way I know how to turn, say, (2 + SQC_Count) into "F" for the type of thing you did for my first question. I was using FALSE to indicate a R1C1 format rather than an A1 format.

[TMS]

I think it would look something like this:

Please Login or Register  to view this content.

Regards

[rmunsun1]

The first part works great. It's getting it bigger, more complicated that is tripping me up now.

Trying to work with something like this:

Please Login or Register  to view this content.

It's the syntax of the "s in arounds the Indirect function that are catching me.

[TMS]

I'm struggling to follow what it is you're trying to achieve. However, a split print area might look something like this:

Please Login or Register  to view this content.

This obviously just uses the existing variables twice but it shows the syntax.

The Print Setup doesn't appear to take formulae like INDIRECT(...) and, although it will accept it when you type it in, it converts it to a fixed address range

Rather than INDIRECT, you need something like this:

Please Login or Register  to view this content.

To go further with this, you really need to post a sample workbook with some typical data and indicate what you hope to achieve based on the data in the workbook.

Regards

[rmunsun1]

Thank you TMShucks. I feel like I've put you through the ringer here, but that last bit was a huge help and got me exactly where I need to be. I know I haven't been very clear either, but it's sensitive information, so I try to err on the side of caution.

In the end, I didn't understand the concept of the _ and the best ways to use the &s and "s. Your last bit nailed it for me though. The Range.Value was a big help.

If anyone can decipher my intent and use it in their own work, this was my final piece of code for the print area, setting it in several dynamic ways:

Please Login or Register  to view this content.

Thanks again for all the help!

[TMS]

Glad you got there in the end. ;-)

The "_" simply allows you to break the line to make it easier to read. When you use it, it is the last thing on the line (but for the new line, obviously). It isn't necessary and can be left out (along with the new line character which would then error the line). As I said, it just makes things easier to read on the screen, especially if they're at a logical break point.

The "&" concatenates elements together. Provided the things you concatenate together make sense in the context that you're trying to use them, you will get a result ... not necessarily the result you wanted, but a result.

Regards

[rmunsun1]

OK, I lied. It did not accomplish exactly what I wanted. While it did provide a multiple print area, separated by comma, the last part became static.

So, the result of the code I posted last was B4:F9,B15:BE21, which is good. However, what I really need is B4:F9,B15:Indirect("B"&(21+C3))

I've tried a lot of combinations of the following:

Please Login or Register  to view this content.

The result of this is a print area of B4:BE15. It seems to skip the comma and the indirect altogether. I have used the following code before to good success:

Please Login or Register  to view this content.

So I know INDIRECT can work in a print area, but for some reason I can't manage to get the Indirect to be put in as a function in the print area box on the first code above that I want.

The biggest change is I want the "letter" part of the Indirect to be dynamic, unlike in the second code above where I could have that set.

Does this make it clearer at all?

[TMS]

Just as an experiment, I modified your example:

Please Login or Register  to view this content.

where A3=5, B3=6, D3=7

This gives a Print Area of: B4:H15,J4:P16,R4:U17 ... which looks pretty static to me ;-)


To be honest though, I'm not sure I understand the point of using Indirect when you're building the Print Area in code.

Regards

[TMS]

When you run this code:

Please Login or Register  to view this content.

What is in cells C3, D3 and E3? What do Vendor_Count and SQC_Count contain?


Regards

[rmunsun1]

I'm driving you nuts, aren't I? I'm sure I am.

Vendor_Count and SQC_Count are counts established in the Module itself early on from a count function in another sheet.

C3, D3, and E3 are just arbitrary cells where I trying to put in the column letter for the last column of the table (it worked), D3 is just a number (doesn't actually really need to be there) and E3 is a count function.

Basically, I need the Indirect to be Indirect("BE" & (21 + Vendor_Count))

BE would be the last column of the table (as assigned by 8 + Metric_Count). All of the _Counts are within the Module, not defined names in the workbook.

[rmunsun1]

Just as an experiment, I modified your example:

Please Login or Register  to view this content.

where A3=5, B3=6, D3=7

This gives a Print Area of: B4:H15,J4:P16,R4:U17 ... which looks pretty static to me ;-)


To be honest though, I'm not sure I understand the point of using Indirect when you're building the Print Area in code.

Regards

Did not see this at first. But the reason I've used Indirect is because it's the only way I know how to give dynamic print areas. So I have a table that regular ole Joe will fill out with data throughout a year. If he wants to print it out, I don't want him printing 500 lines with 400 blank, I want the first 100 to print out. Later, after he's included another 100, he could print out the first 200 lines.

So in that code, the cells referenced a3, b3, and c3 had counts of the data lines in each table. If you use the indirect, it will return the right table and be dynamic based on data entered. Make sense?