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.