vba sumif

[lundbhaiz]

Sub test()
Dim LLRR as Long

LLRR = Range("A" & Rows.Count).End(xlUp).Row
    Range("B2:B" & LLRR).Formula = "=IF(RC[-1]<>"""",SUMIF(Sheet2!C2,MySheet!RC1,Sheet2!C21),"""")"

End Sub

is this wrong way to write it ? when i run this macro, it calculates who excel sheet of 1million rows. but i want to make it work only till last value of column A. i also gave the speeding code like this:

With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayAlerts = False
        
    End With

but still it doent change and calculate who 1 million rows. if this wrong please help me write the code so it copies formula only till last value of column A. plz kind help plz. thanks you

 

[Michael M]

Your test sub should do the trick unless you have data right down to the last row in Col "A"
The "speeding up" code will also work fine, but it won't have any effect on the number of formulae placed in column "B"
Just as a test, do a CTRL + End and see what cell it takes you to ?
If it's waaay past your expected last cell, you will need to delete all the excess rows and columns, save the workbook, close and reopen.
your code should then work as expected.
Also don't forget to put

With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = true
        .DisplayAlerts = true
    End With

at the end of your sub

 

[lundbhaiz]

yes thanks you

yes i put the calculation to automatic again after my code. but the above code is only a part of code. because this macro runs on a newly added worksheet in my file. i add a new worksheet and then the above macro runs on that sheet. so i cannot delete the rows and columns, close and reopen file because of this reason. any way other i can follow to make good code so it does not calculate the whole 1 million rows ? i have around 18000 rows with values in col A. this number keeps changing and it important to me that the macro copy the sumif function only to last value of column A.

plz help. thanks you

 

[lundbhaiz]

ok i press ctrl + end and it only take me to last value of column A. but i dont know why the macro does not stop after reaching last value of col A. the cursor in busy mode keep going on and macro dont stop. any idea why this happening ?

 

[lundbhaiz]

trying again and again but macro does not stop. what i doing wrong ?? very frustrated now

 

[lundbhaiz]

plz help

 

[shadow12345]

have you tried selecting the sheet where col A is located?
Sheets("Sheet2").Select

it might be best if you uploaded a sample sheet so we can look at the data itself (intructions for doing that are on Micheal M signature above)

 

[lundbhaiz]

thanks you for reply. here is code:

Sub Marker_Punch()

Dim LRS As Long

Sheets("MySheet").Select
    
   With Application
        .Calculation = xlCalculationManual
    '    .ScreenUpdating = False
    '   .DisplayAlerts = False
        .EnableEvents = False
        
   End With
        

    LRS = Range("A" & Rows.Count).End(xlUp).Row
    Range("B2:B" & LRS).Formula = "=IF(RC[-1]<>"""",SUMIF(Sheet2!C2,MySheet!RC1,Sheet2!C21),"""")"
    
    With Application
        .Calculation = xlCalculationAutomatic
   '     .ScreenUpdating = True
   '     .DisplayAlerts = True
        .EnableEvents = True
        
        End With

End Sub

Is problem with me giving full range in sumif formula in this code ? i am trying without giving full range in formula right now.

 

[lundbhaiz]

no luck. problem is only that macro does not stop. it keep going on and i dont know why. the busy cursor dont stop even after 15 mins i leave it. i am so frustrate. some plz help me.

 

[lundbhaiz]

plz help some. my macro is not stopping. it goes on endless. plz help.