excel - VBA - Comparison anomalies using variant -
i have found couple of other questions dealing variants none of them seem address issue.
i have simple loops doing comparisons. purpose color excel cell red if there isn't match. results 99% accurate, have noticed couple of seemingly random errors. example, cell containing number 104875 not colored red, indicates there should matching cell in comparison column. there isn't. seems should wrong or correct. of other threads variants have mentioned comparisons have of same type or weird errors. in case, of same type (both integers), isn't problem.
i brand new vba , still trying understand how works.
this relevant part of code:
private sub commandbutton1_click() dim long, j long dim flag boolean dim array1() variant, array2() variant dim column1 double dim column2 double column1 = convertcolumn(textbox1.text) column2 = convertcolumn(textbox2.text) set wb1 = workbooks("advocate july 2017 data.xlsm").sheets(1) set wb2 = workbooks("bi report 8-18-17.xlsm").sheets(1) array1 = intersect(wb1.columns(column1), wb1.usedrange) array2 = intersect(wb2.columns(column2), wb2.usedrange) = 2 ubound(array1) flag = false j = 2 ubound(array2) if isnumeric(array1(i, 1)) , isnumeric(array2(j, 1)) if cdbl(array1(i, 1)) = cdbl(array2(j, 1)) flag = true next j if not flag wb1.cells(i, column1).interior.color = vbred next end sub
edit: turns out code works fine. problem of cells on 1 of sheets hidden , didn't realize it. ~facepalm~ that's being inexperienced in excell
try simplify code, reproductible. e.g., lets want compare first 50 cells in columns a , b in activesheet. put values , this:
option explicit public sub testme() dim array1 variant dim array2 variant dim long dim j long dim flag boolean activesheet array1 = .range("a1:a50") array2 = .range("b1:b50") .range("a1:a10").interior.color = vbwhite = lbound(array1) ubound(array1) flag = false j = lbound(array2) ubound(array2) if array1(i, 1) = array2(j, 1) flag = true next j if not flag .cells(i, 1).interior.color = vbred next end end sub
then try adapt solution yours. should work.
wiki
Comments
Post a Comment