Grab data from another sheet based on values matching in Excel with VBA -
the goal have second worksheet (calcvalues) lookup values (if fielda = x, fieldb = y, fieldc = z, value = a).
the skeleton of have (i don't work in vba, may wrong):
dim calcarray calcarray = array(fielda, fieldb, fieldc, fieldd, fielde) if calcarray.contains(target.column) dim fieldaval, fieldbval, fieldcval, fielddval, fieldeval fieldaval = target.worksheet.cells(target.row, fielda) ... fieldeval = target.worksheet.cells(target.row, fielde) end if
with fielda-e being lookup fields column indexes.
not functional, goal if set 1 of fields check done, in pseudo-c# entity framework mixed in little know excel's vba like:
var data = worksheets("calcvalues").where(c => c.columna == fieldaval); data = data.where(c => c.columnb == fieldbval); ... data = data.where(c => c.columne == fieldeval); ' ideally, grabbing row number data. or row itself, either or. ' grabbed_row = last remaining 'data' object's row number, provided 1 exists. target.worksheet.cells(target.row, updatedfielda) = worksheet("calcvalues").cells(grabbed_row, updatedfieldaindex); target.worksheet.cells(target.row, updatedfieldb) = worksheet("calcvalues").cells(grabbed_row, updatedfieldbindex); ...
with question being how achieve filtering in var data... data = data.where(c => c.columne == fieldeval); section pseudo coded?
edit: i'm doing reviewing, looks best bet somehow have dynamic array akin list in c#, , use application.worksheet.match there, correct?
edit2: okay, updated (so can ignore 2nd section outside of seeing intent), i'm not code correct (isn't firing):
= 1 worksheet("calcvalues").listrows.count if (worksheet("calcvalues").cells(i, 1).value = fieldaval , worksheet("calcvalues").cells(i, 2).value = fieldbval , worksheet("calcvalues").cells(i, 3).value = fieldcval , worksheet("calcvalues").cells(i, 4).value = fielddval , worksheet("calcvalues").cells(i, 5).value = fieldeval) if (worksheet("calcvalues").cells(i, 6).value = "column a") target.worksheet.cells(target.row, columna).value = worksheet("calcvalues").cells(i, 7).value elseif (worksheet("calcvalues").cells(i, 6).value = "column b") target.worksheet.cells(target.row, columnb).value = worksheet("calcvalues").cells(i, 7).value end if end if next
okay, figured out. lot of assumptions made on part , come find out quite bit different functionally (perhaps there's better way, seems work):
dim calcarray(1 5) integer calcarray(1) = fielda calcarray(2) = fieldb calcarray(3) = fieldc calcarray(4) = fieldd calcarray(5) = fielde dim integer, found boolean = 1 while <= ubound(calcarray) , not found if (calcarray(it) = target.column) found = true else = + 1 end if loop if (found) dim fieldaval, fieldbval, fieldcval, fielddval, fieldeval fieldaval = target.worksheet.cells(target.row, fielda) fieldbval = target.worksheet.cells(target.row, fieldb) fieldcval = target.worksheet.cells(target.row, fieldc) fielddval = target.worksheet.cells(target.row, fieldd) fieldeval = target.worksheet.cells(target.row, fielde) = 1 worksheets("calcvalues").usedrange.rows.count if (worksheets("calcvalues").cells(i, 1).value = fieldaval , worksheets("calcvalues").cells(i, 2).value = fieldbval , worksheets("calcvalues").cells(i, 3).value = fieldcval , worksheets("calcvalues").cells(i, 4).value = fielddval , worksheets("calcvalues").cells(i, 5).value = fieldeval) if (worksheets("calcvalues").cells(i, 6).value = "column a") target.worksheet.cells(target.row, columna).value = worksheets("calcvalues").cells(i, 7).value elseif (worksheets("calcvalues").cells(i, 6).value = "column b") target.worksheet.cells(target.row, columnb).value = worksheets("calcvalues").cells(i, 7).value end if end if next end if
Comments
Post a Comment