Number Format

[jagrenet]

Hello,

I am having an issue when copying a regular or standard number such as "3" from one spreadsheet to another using VBA code. The information gets transferred between sheets just fine however, the formatting gets changed from "3" to "44726.8677777778".

There are times where the standard number might be 20.07 or something with 2 digits on either side of the decimal. Other times, just whole numbers. These numbers are representing Total hours worked on a project and subsequently, get compiled on a Monthly Report, the "Target sheet".

The column is "C" and the amount of Rows changes from month to month. So, I have the code counting the Rows using -

Dim iTime As Range

Set iTIme = MyData.Range("C", Cells(Rows.Count, "C").End(xlUp).Offset(-2))
iTime.NumberFormat = "##.##"

I have tried various iterations of setting this variable but it always comes out with the "long" number.
Not sure what I am doing wrong.

Thanks,
Jeff

 

[6StringJazzer]

I don't think you are copying from the location that you think you are, or the data you are copying is not what you think it is. The number 44726.8677777778 is a date time value for June 14, 2022, 8:49 PM.

Your code is setting iTime to the cell that is the third to last cell with data in column C. Is that what is intended? Also, you don't show the code where you are copying a value from one place to another.

I suggest you show:

The code that actually copies the number and pastes it somewhere
The data you are copying from, including the actual value (value seen in the formula bar) and displayed value.

 

[jagrenet]

I don't think you are copying from the location that you think you are, or the data you are copying is not what you think it is. The number 44726.8677777778 is a date time value for June 14, 2022, 8:49 PM.

Your code is setting iTime to the cell that is the third to last cell with data in column C. Is that what is intended? Also, you don't show the code where you are copying a value from one place to another.

I suggest you show:

The code that actually copies the number and pastes it somewhere
The data you are copying from, including the actual value (value seen in the formula bar) and displayed value.

Please see the attached.

 

[6StringJazzer]

I'm trying to help but these pictures are bits and pieces.

You have a line of code that refers to Range("AB"). This is an invalid range reference and should be giving you a runtime error. I do not understand how this code even runs. Do you have an On Error statement that you did not show? Pictures of code are of very limited use. I suggest you paste the code from the entire sub into a post, and mark with it code tags by selecting it then clicking the VBA button. The best I can suggest is that you mean for this to refer to AB1.

The way the code is written it is impossible to tell what data is being copied to C6 for your particular data. It refers to ActiveSheet and I don't know what sheet is active when this code is entered. What is Selection at the point where you do the Copy?
Your code refers to Sheet1 and report13.... but I don't know which sheet is shown in which picture.
Your code refers to cell C6 as the destination for a paste but your pictures do not show which cell is C6.
How are you getting the value under Hours Worked column? Is this a formula?

If I had your file I would set a breakpoint at the Copy line of code, and look at what cell is Selection when this point is reached. Please note the sheet and address of the cell and its value.

Generally it is not a good programming practice to depend on ActiveSheet and use Selection and Selection. It is better to explicitly qualify references and operate directly on desired ranges.

 

[jagrenet]

I'm trying to help but these pictures are bits and pieces.

You have a line of code that refers to Range("AB"). This is an invalid range reference and should be giving you a runtime error. I do not understand how this code even runs. Do you have an On Error statement that you did not show? Pictures of code are of very limited use. I suggest you paste the code from the entire sub into a post, and mark with it code tags by selecting it then clicking the VBA button. The best I can suggest is that you mean for this to refer to AB1.

The way the code is written it is impossible to tell what data is being copied to C6 for your particular data. It refers to ActiveSheet and I don't know what sheet is active when this code is entered. What is Selection at the point where you do the Copy?
Your code refers to Sheet1 and report13.... but I don't know which sheet is shown in which picture.
Your code refers to cell C6 as the destination for a paste but your pictures do not show which cell is C6.
How are you getting the value under Hours Worked column? Is this a formula?

If I had your file I would set a breakpoint at the Copy line of code, and look at what cell is Selection when this point is reached. Please note the sheet and address of the cell and its value.

Generally it is not a good programming practice to depend on ActiveSheet and use Selection and Selection. It is better to explicitly qualify references and operate directly on desired ranges.

No formulas are used at all. Simply copying data from one spreadsheet to another.
C6 is the first cell under Hours Worked. The Cell "Hours Worked" is C5.
"AB" is the 28th column of the original spreadsheet and is a valid column address. (a-z = 1-26) AA = 27, AB = 28
The pictures should clearly be labelled "Copied From" and "Copied To".
There is nothing extraneous outside of this. Very straightforward code.
I'm just curious why a regular number is being turned "into" a computer number during a simple copy and paste and how to correct that.

Thanks,
Jeff

 

[6StringJazzer]

"AB" is the 28th column of the original spreadsheet and is a valid column address. (a-z = 1-26) AA = 27, AB = 28

I understand how columns are referenced. AB is a valid column designator but Range("AB") is not a valid Range reference. If you are referring to the entire column it must be Range("AB:AB"). If you are referring to the first cell then Range("AB1"). The code as written will most definitely raise a runtime error. See pictures below. So do you have an On Error line somewhere in your code?

I'm just curious why a regular number is being turned "into" a computer number during a simple copy and paste and how to correct that.

It is simply not possible for one number to be turned into another number in a copy and paste. I'm 100% certain that the problem is that you are not copying from where you think you are copying. That is why I gave you the date/time value in my first post--do you have the value June 14, 2022, 8:49 PM anywhere? That is also why I'm continuing to bring up the issue of the range reference. For example, if you have a line of code like this

On Error Resume Next

the error will be ignored and something entirely different is selected vs. what you think is selected at this point. That is why I'm so insistent on seeing all of your code. That is also I why I suggested the debugging steps above to find out what is really being copied.



Guaranteed to cause a runtime error:

 

[jagrenet]

I understand how columns are referenced. AB is a valid column designator but Range("AB") is not a valid Range reference. If you are referring to the entire column it must be Range("AB:AB"). If you are referring to the first cell then Range("AB1"). The code as written will most definitely raise a runtime error. See pictures below. So do you have an On Error line somewhere in your code?


It is simply not possible for one number to be turned into another number in a copy and paste. I'm 100% certain that the problem is that you are not copying from where you think you are copying. That is why I gave you the date/time value in my first post--do you have the value June 14, 2022, 8:49 PM anywhere? That is also why I'm continuing to bring up the issue of the range reference. For example, if you have a line of code like this

On Error Resume Next

the error will be ignored and something entirely different is selected vs. what you think is selected at this point. That is why I'm so insistent on seeing all of your code. That is also I why I suggested the debugging steps above to find out what is really being copied.



Guaranteed to cause a runtime error:
View attachment 70885

View attachment 70886

***UPDATE***
I found the problem.
Man do I feel stupid .........
I had "TimeWorked" variable declared "As Long" - no wonder it returned the "long" number.
Changed the variable declaration ......
Dim TimeWorked As String
Guess what ???
Works like a champ now !!!!
Thank you for taking the time to look this over Jeff. Sorry to cause any confusion or waste your time.

Regards,
Jeff G.
(Fellow "plank spanker" and "string bender")
Love your Fretboard signature -

 

[6StringJazzer]

Glad I was able to help in some way! Another reason I wanted to see all the code. There was no variable TimeWorked in the code you provided.

Stop by if you are ever in the DC area. www.stringtheoryjazz.com

 

[jagrenet]

Glad I was able to help in some way! Another reason I wanted to see all the code. There was no variable TimeWorked in the code you provided.

Stop by if you are ever in the DC area. www.stringtheoryjazz.com

Likewise, ...... let me know by message if you are ever in the Roswell, N.M. area.
Thanks again !!

 

[6StringJazzer]

Guitar AND aliens! I can't wait!