• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Type mismatch Run Time Error 13 while running Loop

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,


I am getting Run Time Error 13 Type mismatch while running a loop.


rn.Offset(0, 2).Value = rn + Time(rn.Offset(0, 1).Value, 0, 0)


I am trying to run this line, but there is some bug in the code.


This is the entire code


Dim rng19 As Range

Set rng19 = Range("ap2:ap" & endrow)


'Adding value 5 in GMT column

For Each rn In rng19

rn.Offset(0, 2).Value = rn + Time(rn.Offset(0, 1).Value, 0, 0)


Next rn


I have three columns, AP2 is original time

AQ 2, value as 5 or 0


Result in AR2 should be AP2+ AQ2.


Value in AP2 is time for example 12:00:00

Value in AQ2 is 5


So value in AR2 should be 17:00:00


Can anyone help me in this please.
 
ThrottleWorks


Are you able to post a sample file including some data

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hui Sir,


Thanks for the help.


Sorry for not uploading, we are not allowed to upload from office.

I will post the file tommorow from home.


Have a nice day.
 
Sir, I am facing a formatting problem here.


Once the loop is completed, the ideal value should be somethink like 6:40:24 AM.


But for out of 7200 records, 23 values are 1/1/1900 1:02:41 AM in this format.


I want data to be in 6:40:24 AM format only.


Could you please tell what will be causing these 23 records to change the format.


P.S. - Sir I am using


Columns("AR:AR").Select


Selection.Replace What:="1/1/1900 ", Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False


To remove 1/1/1900 & it seems to be working.
 
Hi, ThrottleWorks!


Without having access to your data it's difficult to diagnose correctly the problem but maybe this help you: Excel handles dates as integer numbers starting with 0 for 31/12/1899 (in fact with 1 for 01/01/1900 but it accepts zero as well) and times as fractional numbers from 0 to 0,999988426 (from 00:00:00 to 23:59:59).


So a cell formatted as "dd/mm/yyyy hh:mm:ss AM/PM" (unquoted) which displays 01/01/1900 01:02:41 AM is actually containing the number 1,043530093. So the integer part (1) is considered as the 1st day 01/01/1900 and the decimal part (0,043530093) as the 01:02:41 AM time.


If that cell actually holds a number of hours, minutes and seconds, i.e., elapsed time, that you want to keep in hh:mm:ss format you should change the cell format to [h]:mm:ss, but take note that the cell contents will still and always be a number: 1,043530093.


If this is not the case and your calculations shouldn't exceed a day or 24h value, then you should check your data and VBA code, or upload a sample file as requested earlier.


Instead of the replacing method you've posted, you can subtract 1 from each cell en AR column if it's greater than 1 and it'll be the same. Actually you should write something alike, just in case you're getting values greater or equal than 02/01/1900:

Selection.Value = Selection.Value - Int(Selection.Value)

so as to keep the fractional part, i.e., hh:mm:ss.


Hope it helps.


Regards!


EDITED


PS: You'd better use a structure like this, specially if you have a lot of entries in that column:

-----

[pre]
Code: 
Sub x()
Dim I As Long
With Range("AR:AR")
I = 1
Do Until .Cells(I, 1).Value = ""
.Cells(I, 1).Value = .Cells(I, 1).Value - Int(.Cells(I, 1).Value)
I = I + 1
Loop
End With
End Sub
[/pre]
-----
 
Hi SirJB7, sorry for late reply.


I will use the code given by you and share the results.


Sir I agree with you "Without having access to your data it's difficult to diagnose correctly the problem".


We are not allowed to upload from office, but from now on I will try to upload sample data from home, thanks a lot for your valuable time & support.


Have a nice day.
 
You must log in or register to reply here.
Back
Top