excel - Error 1004 caused by =? -
i have checked lot of different solutions here on stackoverflow, none of them worked me.
when insert value "1" works, when try insert formula runtime error. have explanation? thankful help!
sub test() dim cell excel.range dim wswithsheetnames excel.worksheet dim wbtoaddsheetsto excel.workbook set wswithsheetnames = activesheet set wbtoaddsheetsto = activeworkbook wsname = 1 254 each cell in wswithsheetnames.range("a" & wsname) wbtoaddsheetsto .sheets.add after:=.sheets(.sheets.count) on error resume next activesheet.name = cell.value wsname2 = cell.value if err.number = 1004 debug.print cell.value & " used sheet name" end if on error goto 0 end next cell worksheets("sheet1").range("b1", "b1735").copy worksheets(wsname2).range("a2") (here problem!)worksheets(wsname2).range("b2:agr1736").formulalocal = "=iferror(vlookup($a2,offset([final.xlsb]" & chr(34) & wsname2 & chr(34) & "!$a$1:$d$2000,0, (column(a2)*4-4)),4,0),"")" next wsname end sub
edit: changed line to:
worksheets(wsname2).range("b2:agr1736").formulalocal = "=iferror(vlookup($a2,offset([final.xlsb]" & wsname2 & "!$a$1:$d$2000,0, (column(a2)*4-4)),4,0)," & chr(34) & chr(34) & ")"
if dont write "=" code works fine, why not working "="?
i think need declare wsname , wsname 2, full code this:
sub test() dim cell excel.range dim wswithsheetnames excel.worksheet dim wbtoaddsheetsto excel.workbook dim wsname integer dim wsname2 string set wswithsheetnames = activesheet set wbtoaddsheetsto = activeworkbook wsname = 1 254 each cell in wswithsheetnames.range("a" & wsname) wbtoaddsheetsto .sheets.add after:=.sheets(.sheets.count) on error resume next activesheet.name = cell.value wsname2 = cell.value if err.number = 1004 debug.print cell.value & " used sheet name" end if on error goto 0 end next cell worksheets("sheet1").range("b1", "b1735").copy worksheets(wsname2).range("a2") 'worksheets(wsname2).range("b2:agr1736").formulalocal = '"=iferror(vlookup($a2,offset([final.xlsb]" & chr(34) & wsname2 & chr(34) & "!$a$1:$d$2000,0, '(column(a2)*4-4)),4,0),"")" worksheets(wsname2).range("b2:agr1736") = "=iferror(vlookup($a2,offset([final.xlsb]" & wsname2 & "!$a$1:$d$2000,0," & "(column(a2)*4-4)),4,0)," & chr(34) & chr(34) & ")" next wsname end sub
hope help.
Comments
Post a Comment