excel - Finishing Code - VBA Function to Skip Blank Cells and Begin A Procedure Whenever It Comes Across Text -
i have spreadsheet has broad groups in column , specific data points in column b (with more data throughout specific rows). looks this:
b c d e f group name weight gross net contribution equity 25% 10% 8% .25 ibm 5% 15% 12% aapl 7% 23% 18% fixed income 25% 5% 4% .17 10 yr bond 10% 7% 5% emerging mrkts
i want macro scroll through column until finds "group" (e.g. equity) , have spit out name other data each specific holding within every group.
i have started writing macro, , have procedure (that works) , function. can't function work. can take , tell me see wrong? thanks
here code:
sub demo1() dim ptr integer ptr = 12 activesheet.cells(1, ptr).select dim wb workbook set wb = thisworkbook dim wb2 workbook each wb2 in application.workbooks wb2.activate next set wb2 = activeworkbook ' set wb = activeworkbook wb.activate 'sheets("attribution").select activesheet.cells(ptr, 2).select selection.copy ' sheets("f2 perf chart").range("e7") = activesheet.cells(12, 2).value 'set wb2 = activeworkbook wb2.activate activesheet.cells(1, ptr).select wb2.sheets("f2 perf chart").range("e7").select activesheet.paste set wb = activeworkbook range("m12").copy set wb2 = activeworkbook wb2.sheets("f2 perf chart").range("f7").select activesheet.paste set wb = activeworkbook range("d12").copy set wb2 = activeworkbook wb2.sheets("f2 perf chart").range("g7").select activesheet.paste end sub
and function:
function numblankcells(rownum integer) integer dim retvar integer set retvar = 0 dim rrng object rrng = sheets("attribution").cells(rownum + 1, 1) while isempty(rrng.value) retvar = retvar + 1 rrng = sheets("attribution").cells(rownum + retvar + 1, 1) loop msgbox retvar numblankcells = retvar end function
edit:
so workbook 1 ("attribution") data has been exported program. workbook 2 ("f2 perf chart") template use self-populate graph used in quarterly report. example of 1 of data subsets trying pull name, weight, , net return("ibm, 5%, 12%") - , pasting workbook 2, in different order ("ibm, 12%, 5%"). trying run through , copy-paste each data point under name column, , function attempt use blank spaces in between each group tell should skip these blank rows.
Comments
Post a Comment