SOLVED
Home

runtime error 1004 : Method range of object - 'Global' failed

%3CLINGO-SUB%20id%3D%22lingo-sub-215590%22%20slang%3D%22en-US%22%3Eruntime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215590%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20correct%20this%3F%3C%2FP%3E%3CP%3E(kindly%20see%20images)%3C%2FP%3E%3CP%3Emany%20many%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F37664iAC65AE07117312A9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22global%20error.PNG%22%20title%3D%22global%20error.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F37665i02DA23111A7C07DD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22global-error.PNG%22%20title%3D%22global-error.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-215590%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-216579%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-216579%22%20slang%3D%22en-US%22%3E%3CP%3EMr.%20Chan%3C%2FP%3E%3CP%3EAll%20along%20you%20are%20suggesting%20what%20Mr.%20Mickle%20had%20just%20recommended..%3C%2FP%3E%3CP%3Esorry%20If%20I%20didn't%20%3CSTRONG%3E%22see%22%3C%2FSTRONG%3E%20it%20earlier.%3C%2FP%3E%3CP%3Emany%20many%20thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-216577%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-216577%22%20slang%3D%22en-US%22%3Eyes%2C%20this%20is%20what%20Mr.%20Mickle%20just%20suggested!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-216573%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-216573%22%20slang%3D%22en-US%22%3E%3CP%3EMr.%20Mickle%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20new%20tip.%26nbsp%3B%26nbsp%3BA%20new%20horizon....%3C%2FP%3E%3CP%3Ethe%20final%20SUB%20would%20be%20below.%20(%20It%20worked%20nicely%20)%3C%2FP%3E%3CP%3E(%3CEM%3Eit%20is%20case%20sensitive%3C%2FEM%3E)%3C%2FP%3E%3CP%3Emany%20many%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20RemoveWordTotal()%3CBR%20%2F%3E%20%20%20%20Application.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%20%20%20%20Dim%20i%20As%20Long%3CBR%20%2F%3E%20%20%20%20For%20i%20%3D%20Cells(Rows.Count%2C%20%22B%22).End(xlUp).Row%20To%202%20Step%20-1%3CBR%20%2F%3E%20%20%20%20%20%20%20%20Cells(i%2C%20%22B%22)%20%3D%20Application.Substitute(Cells(i%2C%20%22B%22)%2C%22Total%22%2C%22%22)%3CBR%20%2F%3E%20%20%20%20Next%20i%3CBR%20%2F%3E%20%20%20%20Application.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3EThe%20above%20%20can%20also%20be%20used%20to%20replace%20a%20text%20instead%20of%20just%20blank.%20%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Einstead%20of%20%22%22%20-%20replace%20it%20with%20%22New%20Text%22%20!!%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EVERY%20VERSATILE%20CODE%2C%20Mr.%20Mickle..%20%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EFOR%20OTHERS%20WHO%20MIGHT%20FIND%20THIS%20USEFUL%3A%3CBR%20%2F%3E%3CSTRONG%3Emaybe%20called%20as%20function%20(%3C%2FSTRONG%3EBELOW%3CSTRONG%3E)%20%20%26gt%3B%20%20Call%20ReplaceSomeText(%22Total%22%2C%20%22B%22%2C%20%22%22)%3CBR%20%2F%3E%22Total%22%20(mWRD)%20%3D%20the%20word%20within%20the%20cell%20to%20be%20replaced%3CBR%20%2F%3E%22B%22%20(mcol)%20%3D%20the%20column%20referenced%3CBR%20%2F%3E%22%22%20(newTXT)%20%3D%20blank%20or%20any%20text%20to%20replace%20mWRD%3CBR%20%2F%3E%3C%2FSTRONG%3E%3CBR%20%2F%3ESub%20ReplaceSomeText(mWRD%20As%20String%2C%20mcol%20As%20String%2C%20newTXT%20As%20String)%3CBR%20%2F%3E%20%20%20Application.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%20%20%20Dim%20i%20As%20Long%3CBR%20%2F%3E%20%20%20For%20i%20%3D%20Cells(Rows.Count%2C%20mcol).End(xlUp).Row%20To%202%20Step%20-1%3CBR%20%2F%3E%20%20%20%20%20%20%20Cells(i%2C%20mcol)%20%3D%20Application.Substitute(Cells(i%2C%20mcol)%2CmWRD%2C%20newTXT)%3CBR%20%2F%3E%20%20%20Next%20i%3CBR%20%2F%3E%20%20%20Application.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-216427%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-216427%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20drop%20the%20WorksheetFunction%20portion%20for%20easier%20readability%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CPRE%3ENew_text%20%3D%20Application.Substitute(Cell_reference%2C%22TOTAL%22%2C%22%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215711%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215711%22%20slang%3D%22en-US%22%3E%3CP%3EMr.%20Chan%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%3C%2FP%3E%3CP%3EI%20think%20I%20have%20solved%20the%20problem%20with%20the%20SUB%20below.%3C%2FP%3E%3CP%3EI%20will%20still%20try%20your%20suggestion%20though.%3C%2FP%3E%3CP%3EThank%20you%20very%20very%20for%20your%20time%20and%20unwavering%20assistance.%3C%2FP%3E%3CP%3Emore%20power%20to%20you%20and%20GOOD%20HEALTH!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20RemoveWordTotal()%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Application.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%20Dim%20i%20As%20Long%3CBR%20%2F%3E%20For%20i%20%3D%20Cells(Rows.Count%2C%20%22B%22).End(xlUp).Row%20To%202%20Step%20-1%3CBR%20%2F%3E%20mcel%20%3D%20Cells(i%2C%20%22B%22)%3CBR%20%2F%3E%20Cells(i%2C%20%22B%22)%20%3D%20Left(Cells(i%2C%20%22B%22)%2C%20Len(mcel)%20-%205)%3CBR%20%2F%3E%20Next%20i%3CBR%20%2F%3E%20Application.ScreenUpdating%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215710%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215710%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Kim%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20statement%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENew_text%20%3D%20Application.WorksheetFunction.Substitute(Cell_reference%2C%22TOTAL%22%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3EChan%20M%20F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215709%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215709%22%20slang%3D%22en-US%22%3E%3CP%3EMr.%20Chan%3C%2FP%3E%3CP%3Ethank%20you%20for%20your%20prompt%20reply.%3C%2FP%3E%3CP%3Ehow%20do%20I%20write%20in%20the%20SUB%20the%20formula%20below%3F%3C%2FP%3E%3CP%3Emany%20many%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%3ESub%20RemoveWordTotal()%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Application.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%20Dim%20i%20As%20Long%3CBR%20%2F%3E%20For%20i%20%3D%20Cells(Rows.Count%2C%20%22B%22).End(xlUp).Row%20To%202%20Step%20-1%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20'%20remove%20the%20word%20total%20inside%20a%20column%20range%20cell%3CBR%20%2F%3E%20%3CSTRONG%3Esubstitute(cell%20reference%2C%22Total%22%2C%22%22)%20%20***%20How%20do%20I%20write%20this%20code%3CBR%20%2F%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%20Next%20i%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Application.ScreenUpdating%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%20%3CBR%20%2F%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215708%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215708%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Kim%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20heard%20a%20worksheet%20function%3A%20%22SUBSTITUTE%22%20which%20can%20perform%20your%20task%3A%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20SUBSTITUE(A1%2C%22TOTAL%22%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%26nbsp%3B%3C%2FP%3E%3CP%3EChan%20M%20F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215707%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215707%22%20slang%3D%22en-US%22%3Ethe%20word%20%22Total%22%20is%20in%20the%20rightmost%20part%20of%20the%20cell%3CBR%20%2F%3Eex%3A%20%22Customer%20ABC%20Total%22%3B%20%22Item%20123%20Total%22%20etc..%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215706%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215706%22%20slang%3D%22en-US%22%3E%3CP%3EMr.%20Chan%3C%2FP%3E%3CP%3EHere%20I%20am%20again%2C%20pardon%20for%20the%20bother..%3C%2FP%3E%3CP%3Ekindly%20help%20with%20the%20small%20snippet%20below%3C%2FP%3E%3CP%3EI%20wish%20to%20erase%20in%20a%20range%20of%20column%20the%20word%20%22Total%22%20in%20a%20cell.%3C%2FP%3E%3CP%3Ein%20EXCEL%20it%20is%20%3CSTRONG%3E%3Dleft(cellText%2C%20len(cellText)-5)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EHow%20do%20I%20write%20it%20in%20vba%3F%3C%2FP%3E%3CP%3Emany%20many%20many%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20RemoveWordTotal()%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Application.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%20Dim%20i%20As%20Long%3CBR%20%2F%3E%20For%20i%20%3D%20Cells(Rows.Count%2C%20%22B%22).End(xlUp).Row%20To%202%20Step%20-1%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E'%20remove%20the%20word%20total%20inside%20a%20column%20range%20cell%3C%2FSTRONG%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Next%20i%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Application.ScreenUpdating%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215603%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215603%22%20slang%3D%22en-US%22%3EMr.%20Mickle%3CBR%20%2F%3Ethank%20you%20for%20the%20tip..%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215601%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215601%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20a%20good%20example%20of%20when%20it%20pays%20to%20debug%20the%20code%20using%20F8.%26nbsp%3B%20You%20can%20step%20through%20the%20code%20one%20line%20at%20a%20time%20and%20then%20hover%20over%20the%20variable%20in%20order%20to%20get%20it's%20value.....or%20you%20van%20use%20a%20watch%20which%20you%20can%20see%20in%20the%20immediate%20window%20or%20perhaps%20even%20type%20in%20a%20statement%20into%20the%20immediate%20window....%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20will%20save%20you%20a%20ton%20of%20time%20in%20the%20long%20run....%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWatch%20Window%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D19JTjCtFPeI%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D19JTjCtFPeI%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EImmediate%20Window%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excelcampus.com%2Fvba%2Fvba-immediate-window-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.excelcampus.com%2Fvba%2Fvba-immediate-window-excel%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215599%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215599%22%20slang%3D%22en-US%22%3EMr.%20Amairah%3CBR%20%2F%3EThank%20you%20for%20your%20time...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215598%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215598%22%20slang%3D%22en-US%22%3E%3CP%3EMr%20Chan%3C%2FP%3E%3CP%3EI%20think%20it%20is%20--%20the%20lastrow%20was%200!%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215596%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215596%22%20slang%3D%22en-US%22%3E%3CP%3EI%20suspect%20the%20LastRow%20-%201%20gives%20zero%20or%20negative%20number.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215594%22%20slang%3D%22en-US%22%3ERe%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215594%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Lorenzo%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%3CSTRONG%3ECells%3C%2FSTRONG%3E%20function%20requires%20two%20arguments%3A%20%3CSTRONG%3ERowIndex%3C%2FSTRONG%3E%20%26amp%3B%20%3CSTRONG%3EColumnIndex%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EBoth%20must%20be%20a%20number%2C%20not%20a%20text!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EYou%20put%20the%20text%20%22C%22%3C%2FSPAN%3E%20in%20the%26nbsp%3B%3CSTRONG%3EColumnIndex%3C%2FSTRONG%3E%2C%20so%20try%20to%20replace%20it%20with%203%20instead.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20544px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F37666i795C3CDC1C4478B3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22VBA%20Cells%20method.png%22%20title%3D%22VBA%20Cells%20method.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Lorenzo Kim
Valued Contributor

How do I correct this?

(kindly see images)

many many thanks

 

 

global error.PNGglobal-error.PNG

16 Replies

Hi Lorenzo,

 

The Cells function requires two arguments: RowIndex & ColumnIndex

Both must be a number, not a text!

You put the text "C" in the ColumnIndex, so try to replace it with 3 instead.

 

VBA Cells method.png

 

Regards

I suspect the LastRow - 1 gives zero or negative number.

Mr Chan

I think it is -- the lastrow was 0!

Thank you for your reply...

Mr. Amairah
Thank you for your time...

This is a good example of when it pays to debug the code using F8.  You can step through the code one line at a time and then hover over the variable in order to get it's value.....or you van use a watch which you can see in the immediate window or perhaps even type in a statement into the immediate window....

 

This will save you a ton of time in the long run....

 

Watch Window

https://www.youtube.com/watch?v=19JTjCtFPeI

 

Immediate Window

https://www.excelcampus.com/vba/vba-immediate-window-excel/

Mr. Mickle
thank you for the tip..

Mr. Chan

Here I am again, pardon for the bother..

kindly help with the small snippet below

I wish to erase in a range of column the word "Total" in a cell.

in EXCEL it is =left(cellText, len(cellText)-5)

How do I write it in vba?

many many many thanks

 

 

Sub RemoveWordTotal()

Application.ScreenUpdating = False
Dim i As Long
For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1

' remove the word total inside a column range cell

Next i

Application.ScreenUpdating = True

End Sub

the word "Total" is in the rightmost part of the cell
ex: "Customer ABC Total"; "Item 123 Total" etc..

Dear Kim,

 

I heard a worksheet function: "SUBSTITUTE" which can perform your task:
          SUBSTITUE(A1,"TOTAL","")

 

Cheers 

Chan M F

Mr. Chan

thank you for your prompt reply.

how do I write in the SUB the formula below?

many many thanks

 

 

 

Sub RemoveWordTotal()

Application.ScreenUpdating = False
Dim i As Long
For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1

' remove the word total inside a column range cell
substitute(cell reference,"Total","") *** How do I write this code

Next i

Application.ScreenUpdating = True

End Sub

 

 

Dear Kim,

 

The statement is as follows:

 

New_text = Application.WorksheetFunction.Substitute(Cell_reference,"TOTAL","")

 

Cheers,

Chan M F

Mr. Chan

Thank you for your reply.

I think I have solved the problem with the SUB below.

I will still try your suggestion though.

Thank you very very for your time and unwavering assistance.

more power to you and GOOD HEALTH!

 

Sub RemoveWordTotal()

Application.ScreenUpdating = False
Dim i As Long
For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
mcel = Cells(i, "B")
Cells(i, "B") = Left(Cells(i, "B"), Len(mcel) - 5)
Next i
Application.ScreenUpdating = True

End Sub

 

Solution

You can drop the WorksheetFunction portion for easier readability:

New_text = Application.Substitute(Cell_reference,"TOTAL","")

Mr. Mickle

Thank you for the new tip.  A new horizon....

the final SUB would be below. ( It worked nicely )

(it is case sensitive)

many many thanks

 

Sub RemoveWordTotal()
Application.ScreenUpdating = False
Dim i As Long
For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
Cells(i, "B") = Application.Substitute(Cells(i, "B"),"Total","")
Next i
Application.ScreenUpdating = True
End Sub

The above can also be used to replace a text instead of just blank.
instead of "" - replace it with "New Text" !!
VERY VERSATILE CODE, Mr. Mickle..


FOR OTHERS WHO MIGHT FIND THIS USEFUL:
maybe called as function (BELOW) > Call ReplaceSomeText("Total", "B", "")
"Total" (mWRD) = the word within the cell to be replaced
"B" (mcol) = the column referenced
"" (newTXT) = blank or any text to replace mWRD

Sub ReplaceSomeText(mWRD As String, mcol As String, newTXT As String)
Application.ScreenUpdating = False
Dim i As Long
For i = Cells(Rows.Count, mcol).End(xlUp).Row To 2 Step -1
Cells(i, mcol) = Application.Substitute(Cells(i, mcol),mWRD, newTXT)
Next i
Application.ScreenUpdating = True
End Sub

 

yes, this is what Mr. Mickle just suggested!

Mr. Chan

All along you are suggesting what Mr. Mickle had just recommended..

sorry If I didn't "see" it earlier.

many many thanks