I have the following data

Column A | Column B | M Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana |

Now I want to do a VLOOKUP on each of the data rows.. How do I do this?

This code works for one cell:

#working for one row
from openpyxl import load_workbook
wb = load_workbook(filename = 'flat_user_data.xlsx')
ws = wb.active
ws["M10"] = "=IF(ISNA(VLOOKUP(A10,'Emergency User Exclusions'!A:A,1,FALSE)),FALSE,TRUE)"
wb.save('flat_user_data.xlsx')

I've tried the following for multiple cells:

#multiple rows
from openpyxl import load_workbook
wb = load_workbook(filename = 'flat_user_data.xlsx')
ws = wb.active
for i in ws.iter_rows():
    ws["M{i}"] = "=IF(ISNA(VLOOKUP(A{i},'Emergency User Exclusions'!A:A,1,FALSE)),FALSE,TRUE)"
    wb.save('flat_user_data.xlsx')

However getting the following error ValueError: M{i} is not a valid coordinate or range

CodePudding user response：

You need to put an 'f' in front of your string if you want to use variables between curly brackets.

Try the code below:

from openpyxl import load_workbook
wb = load_workbook(filename = 'flat_user_data.xlsx')
ws = wb.active
for i in ws.iter_rows():
    ws[f"M{i}"] = f"=IF(ISNA(VLOOKUP(A{i},'Emergency User Exclusions'!A:A,1,FALSE)),FALSE,TRUE)"
    wb.save('flat_user_data.xlsx')

CodePudding user response：

@Max - For the other two issues you reported

1. Do you know how to skip the first 8 rows?
2. ValueError

Try the following in your for loop

#min_row = 9 will start iteration from 9th row
#values_only = True will provide the values instead of cell references

for i in ws.iter_rows(min_row=9, values_only=True):