excel vba - Referring to a different cell and extracting Substrings in VBA -
i have 2 sheets, "report" , "data". in "data" sheet values in column follows t-shirt.adidas.25.110 need take raw data , input them "report" sheet separate entities.
ex. cell a1 read "t-shirt" cell b1 read "adidas"
here have far. statement work 1 line. im not sure how loop it.
dim cell object dim data range dim report range set report = worksheets("report").range("a2", range("a2").end(xldown)) set data = worksheets("data").range("a2", range("a2").end(xldown)) report .resize(1, 4) = split(worksheets("data").range("a2"), ".") end
the macro needs work number of objects in data sheet. in advance!
here 1 way loop:
sub test() dim cell object dim data range dim report range dim long, temparray variant set data = worksheets("data").range("a2", worksheets("data").range("a2").end(xldown)) set report = worksheets("report").range("a2") = 1 data.count temparray = split(data.range("a" & i), ".") report.offset(i - 1).resize(1, ubound(temparray) + 1) = temparray next set temparray = nothing end sub
this way use data source , count of iterations, , can generalize columns based on number of periods in given piece of data.
Comments
Post a Comment