• 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.

Run Time Error 1004 while using Loop

ThrottleWorks

ThrottleWorks

Excel Ninja
Run Time Error 1004

Application defined or object defined error.


Hi,

I am running following loop, sometimes it works, sometimes it gives me bug.

I am failing to understand the reason.


sub Updating_Monthly_Borrowing_Fee_Column_DG()

Application.ScreenUpdating = True

Dim I As Long

I = Cells(Rows.Count, 1).End(xlUp).Row

Dim rng As Range

Set rng = Range("dg3:dg" & I)

For Each rn In rng

rn.Value = rn + rn.Offset(rn, -2)

Next

MsgBox "Done !"

Application.ScreenUpdating = False

End Sub


Can anyone help me in this please.
 
This line  
Code: 
rn.Value = rn + rn.Offset(rn, -2)   means   rn.Value = rn.Value + rn.Offset(rn.Value, -2)
…

Check it out ‼
 
Hi Sachin,


I dont know.. what you are doing with this code.. but I know.. what this code is doing..


Lets say.. "DG5"'s value is 0.65..

so It will try to change its value to 0.65 + DG5.offset(0.65 DOWN, and then 2 LEFT)


means somewhere in DE.. but not exactly DE5.. are you trying to pick value of DE5 and adding the same with DG5.. and new value in DG5 is DG5 + DE5..


Then re-check.. mentioned part.. and try to solve by your self.. :)


Regards,

Deb
 
Also the ScreenUpdating are inverted !


MsgBox should be at the end after ScreenUpdating = True …
 
Hi Marc & Debraj, thanks a lot for the help.


I have realized that I have made some mistake in writing.


I am trying to rectify it, have a nice day.


Will share the results.
 
Hi Sachin ,


Please understand one important point in writing and debugging code ; there is code which does not do what you want , and there is code which errors out. These are two completely different points.


The reason for the error that you were getting is because of the following statement :


rn.Value = rn + rn.Offset(rn, -2)


Excel expects the OFFSET function to have two parameters viz. a rowoffset and a columnoffset ; the crucial point about these parameters is that they should be valid values , which means two things :


1. They should be integers


2. They should be within the limits of Excel's version , which decides the number of possible rows and columns.


If either of these is violated , you will get the error.


For instance , if rn is a fractional ( decimal ) value , you will get an error. Replace the above statement by :


rn.Value = rn + rn.Offset(1, -2)


where I have replaced the rowoffset parameter of the OFFSET function by an integer value , and your error should not come.


Narayan
 
Hi Narayan Sir,


Thanks a lot for the help.

I was trying to find out what went wrong, thus late reply.


I got confused while writing offset function.


I will write an example what I am trying to do.


Range DE3 original value 100.

Range DG3 original value 10.


After running the code, value in the DE3 should be 110 i.e. DE3 + DG3.


I confused cell address & cell value.


I am trying what you have advised, thanks a lot.


Have a nice weekend.


Will share the results.
 
Hi Sachin ,


Can you clarify whether you wish to apply this logic to only DG3 and DE3 , or do you wish this to be repeated for all the populated cells in columns DG and DE ?


If it is only for DG3 and DE3 , then a loop is not required.


If a loop is not required , then the OFFSET function need not be used. You can just have a statement like :


Range("DE3").Value = Range("DE3").Value + Range("DG3").Value


Narayan
 
Narayan Sir it is working now, I was about to write a reply.

Sir I need this procedure for entire range, at present 800 approx.


Sir am I right in using loop ? please advice, I think it is taking time to complete the loop.

Might be my PC problem.


I am checkig the loop time by the method taught by you in a diferent post.

I was using RN instead of number in the offset function & I did not realized that.


By the way I have to use offset (0,-2) not (1,-2), hope I am right.


Cause cell de1 = dg1 + de1 and so on, thanks a lot for the help.


@ Deebraj Sir, thanks a lot for the help, have a nice weekend.


@ Marc Sir, thanks a lot for the help, I did silly mistake in application screen updating, you saved me, have a nice weekend.


P.S. - Just run the macro again, taking 5-6 minutes approx, checking again, will confirm.
 
Hi Sachin...


Good Night..


BTW.. can you please confirm.. time taken by below code..

[pre]
Code: 
Sub Updating_Monthly_Borrowing_Fee_Column_DG()
Application.ScreenUpdating = True
Dim I As Long
I = Cells(Rows.Count, 1).End(xlUp).Row
Range("DE3:DE" & I).Copy
Range("Dg3:dg" & I).PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
Application.CutCopyMode = False
MsgBox "Done !"
Application.ScreenUpdating = False
End Sub
[/pre]

Regards,

DEEbraj..
 
Hi Sachin ,


If you want that the cells in column DE should have their values and the values in the corresponding cells in column DG added , then you should use something like this :

[pre]
Code: 
Sub Updating_Monthly_Borrowing_Fee_Column_DG()
Application.ScreenUpdating = False
Dim I As Long
I = Cells(Rows.Count, "DE").End(xlUp).Row
Dim rng As Range
Set rng = Range("DE3:DE" & I)
For Each rn In rng
rn.Value = rn.Value + rn.Offset(0, 2).Value
Next
Application.ScreenUpdating = True
MsgBox "Done !"
End Sub
You can use Deb's code with a minor change of the following two statements :

Range("DG3:DG" & I).Copy
Range("DE3:DE" & I).PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
[/pre]
Narayan
 
Do you have calculations / complex formulas. They might interfere with speed of the code.


Try the below code and see if it gives any speed benefit.

[pre]
Code: 
Public Sub AddUp()
Dim lr As Long
Application.Calculation = xlCalculationManual
lr = Range("DE" & Rows.Count).End(xlUp).Row
Range("DE3:DE" & lr).Value = Evaluate("DE3:DE" & lr & "+DG3:DG" & lr)
Application.Calculation = xlCalculationAutomatic
End Sub
[/pre]
 
Hi Narayan Sir, Debraj Sir, Shrivallabha ji, extreamally sorry for late reply.

I am trying this, will share the results.


Thanks a lot for the help.


Have a nice day to all.
 
Hi Narayan Sir, I am using the code given by you.

It is working very good. Thanks for the help.


@ Debraj Sir, the entire macro takes around 5 minutes to run.

I am not sure but this must be due to my system issue.


@ Shrivallabha I am trying to use the code you have given.

But I think I am making some mistake while writing the code.

This is giving me some bug.

I am trying to find my error, will share the results soon.


Thanks a lot to everyone, good night.
 
You must log in or register to reply here.
Back
Top