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:
if (e.source.getActiveSheet().getName() === 'IB' && editedCol === 4 && editedRowStart > 1) {
var ibSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('IB')
var ibSheetData = ibSheet.getDataRange().getValues()
var updatedPOs = []
var updatedDates = []
for (var i = editedRowStart - 1; i < editedRowEnd; i++) {
updatedPOs.push(ibSheetData[1])
updatedDates.push(ibSheetData[3])
}
var updatedObject = Object.assign(...updatedPOs.map((PO, i) => ({[PO]: updatedDates})))
var inboundPOSheet = e.source.getSheetByName('Inbound POs')
var inboundPOSheetLastRow = inboundPOSheet.getLastRow()
var inboundPOSheetData = inboundPOSheet.getDataRange().getValues()
for (var i = 1; i < inboundPOSheetLastRow; i++) {
var inboundPO = inboundPOSheetData[1]
if (updatedPOs.includes(inboundPOSheetData[1])) {
inboundPOSheet.getRange(i+1, 19).setValue(updatedObject[inboundPO])
}
}
} SummarySheet.getRange(36,3).setValue(currentDate);
}
Thank you!
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:
if (e.source.getActiveSheet().getName() === 'IB' && editedCol === 4 && editedRowStart > 1) {
var ibSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('IB')
var ibSheetData = ibSheet.getDataRange().getValues()
var updatedPOs = []
var updatedDates = []
for (var i = editedRowStart - 1; i < editedRowEnd; i++) {
updatedPOs.push(ibSheetData[1])
updatedDates.push(ibSheetData[3])
}
var updatedObject = Object.assign(...updatedPOs.map((PO, i) => ({[PO]: updatedDates})))
var inboundPOSheet = e.source.getSheetByName('Inbound POs')
var inboundPOSheetLastRow = inboundPOSheet.getLastRow()
var inboundPOSheetData = inboundPOSheet.getDataRange().getValues()
for (var i = 1; i < inboundPOSheetLastRow; i++) {
var inboundPO = inboundPOSheetData[1]
if (updatedPOs.includes(inboundPOSheetData[1])) {
inboundPOSheet.getRange(i+1, 19).setValue(updatedObject[inboundPO])
}
}
} SummarySheet.getRange(36,3).setValue(currentDate);
}
Thank you!