i've had macro button built. i'm getting error after pressing button designed work on pc.
the file asking location of excel file format. need work on mac can change code allow opening of files mac environment instead of windows one?
this visual basic editor;
sub johnny_calculations() dim totalrow string dim wb string dim ws string application.filedialog(msofiledialogopen) .show .title = "choose file" .allowmultiselect = false if .selecteditems.count = 1 path = .selecteditems(1) end if end workbooks.opentext filename:= _ path _ , origin:=xlwindows, startrow:=1, datatype:=xldelimited, textqualifier _ :=xldoublequote, consecutivedelimiter:=false, tab:=false, semicolon:= _ true, comma:=false, space:=false, other:=false, fieldinfo:=array(array( _ 1, 1), array(2, 1), array(3, 1), array(4, 1), array(5, 1), array(6, 1), array(7, 1), array(8, _ 1), array(9, 1), array(10, 1), array(11, 1), array(12, 1), array(13, 1), array(14, 1), array _ (15, 1), array(16, 1), array(17, 1), array(18, 1), array(19, 1), array(20, 1), array(21, 1), _ array(22, 1), array(23, 1)), trailingminusnumbers:=true wb = activewindow.caption ws = activesheet.name totalrow = worksheetfunction.match("total", range("a:a"), 0) rows(totalrow & ":" & totalrow).select selection.clearcontents file = "johnny calculations.xlsm" workbooks(file).worksheets("sheet2").activate workbooks(file).worksheets("sheet1").range("b2:c22").copy windows(wb). _ activate sheets.add after:=activesheet range("b2").select activesheet.paste columns("b:c").select selection.columnwidth = 16.86 range("c3").select application.cutcopymode = false 'calculations activecell.formular1c1 = "=sum('" & wb & "'!c)" range("c6").select activecell.formular1c1 = "=sum('" & wb & "'!c[3])" range("c7").select activecell.formular1c1 = "=r[-1]c/r[-4]c" range("c7").select range("c8").select activecell.formular1c1 = _ "=sum('" & wb & "'!c[4])/sum('" & wb & "'!c)" range("c10").select activecell.formular1c1 = "=sum('" & wb & "'!c[5])" range("c11").select activecell.formular1c1 = _ "=sum('" & wb & "'!c[5])/sum('" & wb & "'!c[4])" range("c12").select activecell.formular1c1 = _ "=sum('" & wb & "'!c[6])/sum('" & wb & "'!c[3])" range("c13").select activecell.formular1c1 = "=r[-3]c/r[-7]c" range("c15").select activecell.formular1c1 = "=sum('" & wb & "'!c[7])" range("c16").select activecell.formular1c1 = "=r[-1]c/r[-6]c" range("c17").select activecell.formular1c1 = "=sum('" & wb & "'!c[10])" range("c19").select activecell.formular1c1 = "=sum('" & wb & "'!c[8])" range("c20").select activecell.formular1c1 = "=iferror(r[-1]c/r[-5]c,""no complaints"")" range("c21").select activecell.formular1c1 = "=sum('" & wb & "'!c[9])" range("c22").select application.cutcopymode = false activecell.formular1c1 = "=r[-1]c/r[-19]c" range("c23").select end sub
Comments
Post a Comment