tipsytappy
New Member
- Joined
- Apr 13, 2021
- Messages
- 6
- Office Version
- 365
- 2019
- Platform
- Windows
Hi, would greatly appreciate if anyone is able to help!
I am working on editing a macro that involves vlookup copy/paste data from Worksheet A (IB) to Worksheet B (Inbound POs). However, it copies empty cell in Worksheet A and overwrites the filled cell in Worksheet B which i do not want. Anyone able to help? Code below:
function onEditDoug(e){
//Variables
var row = e.range.getRow();
var col = e.range.getColumn();
var IBDataEntry = "IB";
var IBDataSheet = e.source.getSheetByName(IBDataEntry);
var IBlastRow = IBDataSheet.getLastRow();
var InboundPO = "Inbound POs";
var InboundPOSheet = e.source.getSheetByName(InboundPO);
var InboundlastRow = InboundPOSheet.getLastRow();
var searchRange = InboundPOSheet.getRange(2,2,InboundlastRow,1);
var rangeValues = searchRange.getValues();
//Data Entry for Date Of Approval
if(col === 22 && row > 2 && e.source.getActiveSheet().getName() === IBDataEntry){
var POList3 = IBSheet.getRange("V3:V").getValues();
var LastRow3 = POList3.filter(String).length + 2;
var Entries3 = LastRow3 - row + 1;
for (var x = 0; x < Entries3 ; x++){
var ApprovalPO = IBDataSheet.getRange(row + x, 22).getValue();
var ApprovalDate = IBDataSheet.getRange(row + x, 23).getValue();
for(var j=1;j<=InboundlastRow;j++){
if(rangeValues[j] == ApprovalPO){
InboundPOSheet.getRange(j+2,11).setValue(ApprovalDate);
}
}
}
} SummarySheet.getRange(36,3).setValue(currentDate);
}
Also, somehow the code stops working after awhile as well.
Appreciate the help, thank you so much!!!
I am working on editing a macro that involves vlookup copy/paste data from Worksheet A (IB) to Worksheet B (Inbound POs). However, it copies empty cell in Worksheet A and overwrites the filled cell in Worksheet B which i do not want. Anyone able to help? Code below:
function onEditDoug(e){
//Variables
var row = e.range.getRow();
var col = e.range.getColumn();
var IBDataEntry = "IB";
var IBDataSheet = e.source.getSheetByName(IBDataEntry);
var IBlastRow = IBDataSheet.getLastRow();
var InboundPO = "Inbound POs";
var InboundPOSheet = e.source.getSheetByName(InboundPO);
var InboundlastRow = InboundPOSheet.getLastRow();
var searchRange = InboundPOSheet.getRange(2,2,InboundlastRow,1);
var rangeValues = searchRange.getValues();
//Data Entry for Date Of Approval
if(col === 22 && row > 2 && e.source.getActiveSheet().getName() === IBDataEntry){
var POList3 = IBSheet.getRange("V3:V").getValues();
var LastRow3 = POList3.filter(String).length + 2;
var Entries3 = LastRow3 - row + 1;
for (var x = 0; x < Entries3 ; x++){
var ApprovalPO = IBDataSheet.getRange(row + x, 22).getValue();
var ApprovalDate = IBDataSheet.getRange(row + x, 23).getValue();
for(var j=1;j<=InboundlastRow;j++){
if(rangeValues[j] == ApprovalPO){
InboundPOSheet.getRange(j+2,11).setValue(ApprovalDate);
}
}
}
} SummarySheet.getRange(36,3).setValue(currentDate);
}
Also, somehow the code stops working after awhile as well.
Appreciate the help, thank you so much!!!