Something Wrong With Label Using Macro Excel -
first of all, sorry post question here. question more mathematics, not programming don't know other place seek help. basically, code works label selection highlighted user using alphabet , number, 1a, 1b, 1c, 1d , etc. there 20 different styles (1 1 label 20 20 label) label selected cells. problem occurs when choose bigger number 5 first selection, , first selection finish @ 3a (the labeling this: start: 1a 1b 1c 1d 1e 2a 2b 2c 2d 2e 3a, next selection choose 2 second selection, labeling incorrect this: 3b 3c 3d 3e until 3z , continue till last cells number 8 correct labeling should this: 4a 4b 5a 5b 6a 6b. don't know wrong code , need you. thank in advance. these code , select style 1 2 only, different thing in code number:
public a, b integer sub autolabel() 'to label cell in term of 1a , etc = 1 b = 1 end sub sub labeltest() dim cell range selection ' allign text centered between top , bottom of cell .horizontalalignment = xlcenter ' allign text centered between left , right of cell .verticalalignment = xlcenter sr = .row sc = .column lr = sr + .rows.count - 1 lc = sc + .columns.count - 1 end ' input first cell 1a, , next cell 1b , etc each cell in selection cell.value = b & mid("abcdefghijklmnopqrstuvwxyz", a, 1) = + 1 if = 2 = 1: b = b + 1 next cell ' run next selected cell user end sub sub labeltest2() dim cell range selection ' allign text centered between top , bottom of cell .horizontalalignment = xlcenter ' allign text centered between left , right of cell .verticalalignment = xlcenter sr = .row sc = .column lr = sr + .rows.count - 1 lc = sc + .columns.count - 1 end ' input first cell 1a, , next cell 1b , etc each cell in selection cell.value = b & mid("abcdefghijklmnopqrstuvwxyz", a, 1) = + 1 if = 3 = 1: b = b + 1 next cell ' run next selected cell user end sub
firstly, don't think question off-topic stack overflow @ all.
if i've understood problem correctly, following should work:
const alphabet string = "abcdefghijklmnopqrstuvwxyz" function getlabelnumber(byval labelnumber integer, byval labelsperpage integer) string getlabelnumber = ((labelnumber - 1) \ labelsperpage) + 1 & mid(alphabet, ((labelnumber - 1) mod labelsperpage) + 1, 1) end function then, want cells labelled 1a 1h, 2a 2h , forth 10h, need keep track of single counter (let's call i) , each label string getlabelnumber(i, 8).
your code might able make use of follows:
dim cellcount integer, numcols integer numcols = selection.columns.count each cell in selection cellcount = cellcount + 1 cell.value = getlabelnumber(cellcount, numcols) next cell
Comments
Post a Comment