Traveller Trade Tool

[Terry Mixon]

Looking at the unmodified (I gave up on modifying it) version 1.3 sheet, I ran into an error, I think. You'll have to tell me.

First, the setup parameters.



Then the purchase numbers for illegal cybernetics.




Then the sales numbers.




The numbers seemed high, so I went to see what I rolled on the trade goods tab.




Unless I'm missing something, the purchase price should have been 212,500 and the sale price 500,000. I'm assuming streetwise mods are already taken into account, so I'm at a loss as to how the final numbers are different than on the trade goods tab.

What am I missing?

 

[myrmidon009]

Yeah, it's pasting the sale price where the sales DM goes and that is because I added a column. I can't make head or tail out of the macro to even know what to experiment with to try and change it to the correct column. I updated my copy to put the DMs back to the values they should have been and made the one column Profit / Ton to match everything else.

To your earlier question - Yes, you are correct. Adding the column threw off the macro. It's just copying values from a specific column, so if a column gets added, that throws off the code. To fix that, I think I can use named ranges instead of columns. The named ranges should stay linked to the correct data even if columns are added.

Unless I'm missing something, the purchase price should have been 212,500 and the sale price 500,000. I'm assuming streetwise mods are already taken into account, so I'm at a loss as to how the final numbers are different than on the trade goods tab.

Regarding your second question, I can't replicate the problem on my end. I think what is happening is that the file has been recalculated since you ran the macro that copies and pastes the data. Any time you save the file, it recalculates the entire workbook. This means that the trade goods on the 'Trade Goods' tab are recalculated. Once that happens, they will no longer match the copied values on the 'Purchases' and 'Sales' tabs. Try running the 'Calculate Available Goods' macro and then checking the values right after that. If there's still a mismatch then, let me know.

I'll work on updating the tool to use named ranges.

 

[Terry Mixon]

To your earlier question - Yes, you are correct. Adding the column threw off the macro. It's just copying values from a specific column, so if a column gets added, that throws off the code. To fix that, I think I can use named ranges instead of columns. The named ranges should stay linked to the correct data even if columns are added.

I appreciate that. It's hard modifying this great tool when I don't have macro skills and I'm unlikely to develop them.

Regarding your second question, I can't replicate the problem on my end. I think what is happening is that the file has been recalculated since you ran the macro that copies and pastes the data. Any time you save the file, it recalculates the entire workbook. This means that the trade goods on the 'Trade Goods' tab are recalculated. Once that happens, they will no longer match the copied values on the 'Purchases' and 'Sales' tabs. Try running the 'Calculate Available Goods' macro and then checking the values right after that. If there's still a mismatch then, let me know.

I'll work on updating the tool to use named ranges.

I'd readily believe I did it with all my messing around. If I see it going forward, I'll mention it.

 

[myrmidon009]

Here is v1.3.1. The major changes are as follows:

• Updated the tool to use named ranges for all cells from which or into which data is copied.
• Updated code to use named ranges instead of column numbers. (Note: this should allow users to add columns without throwing off the macro. Adding rows may cause issues.)
• Added a note on the 'Instructions' tab about how the values on the 'Purchases' and 'Sales' tab may not match, why that may happen, and explaining that such an occurrence is okay. (I hope this explanation makes sense.)

Thanks, as always, for the feedback!

 

[Terry Mixon]

Here is v1.3.1. The major changes are as follows:

• Updated the tool to use named ranges for all cells from which or into which data is copied.
• Updated code to use named ranges instead of column numbers. (Note: this should allow users to add columns without throwing off the macro. Adding rows may cause issues.)
• Added a note on the 'Instructions' tab about how the values on the 'Purchases' and 'Sales' tab may not match, why that may happen, and explaining that such an occurrence is okay. (I hope this explanation makes sense.)

Thanks, as always, for the feedback!

I'll dig into this more tomorrow. Thanks.

 

[Terry Mixon]

The Trade Goods tab still shows this. It even has the same roll numbers. The goods don't have these numbers. Purchase price 212,500 and sale price 350,000.



I had it recalculate them by calculating the purchases. Got this. Listed purchase price is now 150,000 and sale price is 287,500. Nowhere close to what is on the Trade Goods tab. The numbers I see seem more likely than the Trade Goods tab, so I'm not sure what is going on.



Is it using the law level difference as a DM? That is supposed to be for odd jobs rather than the main illegal categories. It would explain the large roll to sell, but not why it doesn't show on the other tabs.

My input information in case it matters.

 

[myrmidon009]

Is it using the law level difference as a DM? That is supposed to be for odd jobs rather than the main illegal categories. It would explain the large roll to sell, but not why it doesn't show on the other tabs.

It's not using the law level difference. I'm not seeing any issues on my end. Could you send me your file and I'll take a look at it?

 

[Terry Mixon]

It's not using the law level difference. I'm not seeing any issues on my end. Could you send me your file and I'll take a look at it?

Here it is. I just verified the numbers are different on the Trade Goods tab than the other locations.