excel - Asigning array for VBA doesn't work -
i wanted find mismatch of 2 different column in same sheet. using dynamic array store data of these 2 columns. code below having error , can't fix it.
sub mismatch() dim sht worksheet dim integer dim j integer dim k integer dim last integer dim btid() string dim cmf() string ''find mismatch sheets("authorizations issued").select range("a1:bi1").copy sheets("mismatch").select range("a1").select activesheet.paste sheets("authorizations issued").select last = activesheet.usedrange.rows.count = 1 last range("a2").select sheets("authorizations issued").select while selection.value <> "cust bill id" activecell.offset(0, 1).select loop if selection.value = "cust bill id" activecell.offset(i, 0).select btid(i) = selection.value end if next j = 1 last range("a2").select while selection.value <> "sap cmf#" activecell.offset(0, 1).select loop if selection.value = "sap cmf#" activecell.offset(j, 0).select cmf(j) = selection.value end if next j dim l integer l = 2 k = 3 last if btid(k) <> cmf(k) range("$a$" & k & ":$bh$" & k).copy sheets("mismatch").select range("$a$" & l).select activesheet.paste l = l + 1 else: l = l end if next k sheets("mismatch").select activesheet.usedrange.select selection.entirecolumn.entirecolumn.autofit end sub
these 2 lines highlighted.
btid(i) = selection.value cmf(j) = selection.value
use redim dim array
sub mismatch() dim sht worksheet dim integer dim j integer dim k integer dim last integer dim btid() string dim cmf() string ''find mismatch sheets("authorizations issued").select range("a1:bi1").copy sheets("mismatch").select range("a1").select activesheet.paste sheets("authorizations issued").select last = activesheet.usedrange.rows.count = 1 last range("a2").select sheets("authorizations issued").select while selection.value <> "cust bill id" activecell.offset(0, 1).select loop if selection.value = "cust bill id" activecell.offset(i, 0).select redim preserve btid(i) btid(i) = selection.value end if next j = 1 last range("a2").select while selection.value <> "sap cmf#" activecell.offset(0, 1).select loop if selection.value = "sap cmf#" activecell.offset(j, 0).select redim preserve cmf(j) cmf(j) = selection.value end if next j dim l integer l = 2 k = 3 last if btid(k) <> cmf(k) range("$a$" & k & ":$bh$" & k).copy sheets("mismatch").select range("$a$" & l).select activesheet.paste l = l + 1 else: l = l end if next k sheets("mismatch").select activesheet.usedrange.select selection.entirecolumn.entirecolumn.autofit end sub
Comments
Post a Comment