smozgur

XL2BB - Excel Range to BBCode 1.2.7

No permission to download

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
67
Office Version
365, 2016
Platform
Windows, MacOS
Found the issue. I had a merged empty data validation cell which if selected generates the run-time error. Happens on both Windows and MAC v16.
Interestingly doesn't happen if the cells are no merged.
Screen Shot 2020-06-23 at 08.05.29.jpg

The other issue is the following. Only 2 rows are showing when in fact 4 rows have been selected

Formula support.xlsx
BCDEFGHIJK
33TankWCRO
34Tank nameVolumeReal Depth
TkDD
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
951
@Polanskiman - thanks for reporting the issue.

I'd just like to say that XL2BB is not an Excel Worksheet / Range creator but a converter that transforms the selected range to a custom BBCode to be used in MrExcel Message Board. It doesn't and is not supposed to cover or fulfill all kind of variation of rules / formats / structure that an Excel worksheet might contain. However, we are all trying to fix / implement / suggest anything possible to make it work better. So I will try to find out if this can be fixed.

In the meantime, I am sending you a private message for asking a sample worksheet that is causing the problem(s) you reported. This way I can see the actual problem that you are having with the tool. That would be great if you could share it with me (with no sensitive data of course).

Thank you again.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
951
@Polanskiman - thanks again for reporting these two issues and providing the sample workbook. I wanted to let you know my findings and possible actions.

1- Missing data in the generated XL2BB code.
The new line used in the cell values causing this problem.
This is a bug and it will be fixed in the next update.

2- Data validation in merged cells.
This is actually kind of an Excel inconsistency.
When we have a cell with data validation and merge it with adjacent cell later, merged range doesn't have the same data validation unless two cells are forced to have the same validation. Therefore VBA has nothing to do but fail if it is asked to return any information about the data validation on that range.

To see how Excel tries to handle this issue, please try this: Select G6 (merged with H6 that has the Data Validation) and click on Data Validation on the ribbon. See how Excel tries to solve it with user interaction before executing the actual requested command.
Side note: Confirming the following will end with copying the same data validation to G6. And if cells are un-merged later then there will be two adjacent cells with the same data validation.
1593237416299.png


XL2BB can't ask something like this as it is not supposed to alter anything in the workbook. Therefore, I will force XL2BB to stop execution and tell user about the merged cells which don't have the same validation. It will be surely better interaction than 1004 error.
Necessary change will be applied in the next update.

Some suggestions for the user side to make it work before the update (separate solutions that will avoid this problem):
1- Merge first, then create the data validation.
2- If merge should be done later, then select the merged range and click on Data Validation to have Excel to extend the Data Validation on the cells in the merged area. (above example).
3- Don't use cell merge in Excel (at least for nothing other than labeling) - Of course funny suggestion but personally I wish Excel never provided such thing. It is against whole tabular data logic.

Thanks again!
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,697
Office Version
2010
Platform
Windows
I copied a table from post #7 of this thread. The start time and end time originally are 8:00 and 18:00. It became 9:00 and 17:00. Also, the last column, correct result, is filled with huge numbers. I guess this is due to the European number format.

Another problem is the date format. If I paste the dates (original in UK format) to my system (US format), dates are pasted as text. If I change my system to UK format, then, dates are pasted as true dates (numbers).


Book1
ABCDEFGH
1SUPPORT START TIMESUPORT END TIMEAssign DateOwned Dateformula resultcorrect result
209:0017:0015/01/2020 13:4415/01/2020 14:0000:16:001.11111E+12
309:0017:0011/01/2020 18:5413/01/2020 09:1600:16:005.27778E+12
409:0017:0010/01/2020 17:0310/01/2020 17:0500:02:001.38889E+11
Sheet1
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
951
Thanks for reporting these issues, @yky.

The start time and end time originally are 8:00 and 18:00. It became 9:00 and 17:00.
That's because A column cells which are referred in B cell formulas should have LUFTHANSA but we don't see that data in the mini-sheet range.

the last column, correct result, is filled with huge numbers. I guess this is due to the European number format.
I will need to ask you the actual worksheet sample with the same data (without any sensitive information), so I can reproduce the same behavior in the development environment to see the actual problem and try to fix any possible bug. I'll be sending you my email address as private message. That would be great if you could provide me a sample workbook.

Another problem is the date format. If I paste the dates (original in UK format) to my system (US format), dates are pasted as text. If I change my system to UK format, then, dates are pasted as true dates (numbers).
I am surprised with this one as I believe it was already handled at one of the very early patches of XL2BB. I will check this out in different regional settings to make sure. Your sample workbook will be also great help for this. Because if data is saved as "text" then XL2BB has nothing to do as it cannot format copied value from forum to worksheet (reverse XL2BB => BB2XL as we call it). Please see the sample data below - cell values are date values but formatted in d/m/yy format. When you move your mouse pointer over the cells, you will see the values as in m/d/yy format. This way XL2BB makes sure it is properly exchanged as values to provide portability.
Book1
B
114/1/20
215/1/20
316/1/20
417/1/20
Sheet1

However, when I move the mouse over the cells in your sample or in post #7, I don't see any tooltip that tells me those are not actually entered as date values but as text. Perhaps imported data.

I've been already preparing to release a new patch version by the end of July for the issues that @Polanskiman reported earlier. It is great you posted these ones so we can have more bugs solved in a single release.

Thank you, again.
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,697
Office Version
2010
Platform
Windows
I am surprised with this one as I believe it was already handled at one of the very early patches of XL2BB. I will check this out in different regional settings to make sure. Your sample workbook will be also great help for this. Because if data is saved as "text" then XL2BB has nothing to do as it cannot format copied value from forum to worksheet (reverse XL2BB => BB2XL as we call it). Please see the sample data below - cell values are date values but formatted in d/m/yy format. When you move your mouse pointer over the cells, you will see the values as in m/d/yy format. This way XL2BB makes sure it is properly exchanged as values to provide portability.
Book1
B
114/1/20
215/1/20
316/1/20
417/1/20
Sheet1

However, when I move the mouse over the cells in your sample or in post #7, I don't see any tooltip that tells me those are not actually entered as date values but as text. Perhaps imported data.
I have mailed you the workbook.

You are right. Most of the date/time are text. I copied the table in #7 and pasted it to Excel. I then copied the pasted data and pasted it again by value. Most of the date/time are TEXT. However, D3, D4, and E4 are pasted as number, implying they are true dates. Yet, when I hovered the mouse over them in #7, no tooltip showed up.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
951
However, D3, D4, and E4 are pasted as number, implying they are true dates.
That's right. Because those cells have valid date values no matter the format is d/m/y or m/d/y, so Excel treats them as date values.

Once the range was rendered as a mini-sheet in the forum page, all it has what it was given to it in Excel. If a numeric value has a different look than its formatted version in a cell then it is saved in the tags during the XL2BB process, so it provides portability copying the values back form the mini-sheet with a JavaScript process during the BB2XL.

exclude non-business hours and weekends
This mini-sheet was clearly created that column D and E cells values are text, certainly not date values. That’s why it doesn’t work when it was copied back to Excel, because there is no actual values saved in the background for the formatted values rendered in the mini-sheet cell (as in my sample post in my reply).

Therefore, everything is working how it is supposed to be at this point.

However, I believe that you found a bug about column H values. I will work on it.

Thanks again for sending the workbook and your help, @yky!

Suat
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,880
Office Version
365, 2010
Platform
Windows, Mobile
Click the XL2BB icon in the reply window to take you to the "XL2BB - Excel Range to BBCode 1.2.7" page, there is an Orange box top right that is a download link or there is a link in the Installation section.

 

Watch MrExcel Video

Forum statistics

Threads
1,102,242
Messages
5,485,607
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top