title: Using Python to find an Excel cell value 0
body: <p>I'm trying to find a cell value in column B in Microsoft Excel if column A's cell value is &quot;Outstanding...&quot;. The code errors and cannot find the cell value</p>
  <pre><code>wb = Workbook()
  xlfile = &quot;ATVI.xlsx&quot;
  wb = load_workbook(xlfile)
   
  for xws in wb.sheetnames:
      worksheet = wb[xws]
      for row in worksheet.iter_rows(&quot;A&quot;)
      for cell in row:
          #cell value you want
          if cell.value == &quot;Shares outstanding 5&quot;:
              #change column number for any cell value you
              print(ws.cell(row=cell.row, column=2).value)
  </code></pre>
  <p>The code does work to change excel file data - example sample below, yet fails when I search within the raw data for a particular cell value, which is in a different row in each sheet. I was looking for some help while I continue my journey through this course.</p>
  <p>Thank You,</p>
  <p>B.</p>
  <pre><code>from bs4 import BeautifulSoup
  from openpyxl import Workbook
  from openpyxl import load_workbook
  from openpyxl.styles import Color, PatternFill, Font, Border
  from openpyxl.styles import colors
  from openpyxl.cell import Cell
  import colorama
  from colorama import Fore, Style
  import os
  from keyboard import press
  import re
  from selenium import webdriver
  from selenium.webdriver.chrome.options import Options
  import shutil
  import math
  import pandas
  from decimal import Decimal
  from openpyxl.styles import Alignment
   
   
   
   
   
  xlfile = &quot;ATVI.xlsx&quot;
  if os.path.exists(xlfile):
      os.remove(xlfile)
   
  src_file =&quot;uk_yahoo_finance_for_stock_ATVI.xlsx&quot;
  dst_file = &quot;ATVI.xlsx&quot;
   
  shutil.copy(src_file, dst_file)
   
  wb = Workbook()
  xlfile = &quot;ATVI.xlsx&quot;
  wb = load_workbook(xlfile)
   
   
  #print('B3 to B5')
  wb = Workbook()
  wb = load_workbook(xlfile)
   
  #########################################
  #########################################
   
  def round_up(n, decimals=0):
      multiplier = 10 ** decimals
      return math.ceil(n * multiplier) / multiplier
   
  def truncate(n, decimals=3):
      multiplier = 10 ** decimals
      return int(n * multiplier) / multiplier
   
  def percentage(a, b):
      return round(a / b * 100, 2)
   
  d = {
      'K': 3,
      'M': 6,
      'B': 9
      }
   
  def text_to_num(text):
      if text[-1] in d:
          num, magnitude = text[:-1], text[-1]
          return Decimal(num) * 10 ** d[magnitude]
      else:
          return Decimal(text)
   
  def percent2fraction(x):
      return float(x.strip('%'))/100
   
   
  ##########################################
  #########################
   
  del wb['Sheet']
  wb.save(xlfile)
   
  ##########################################
   
  blueFill = PatternFill(start_color=&quot;0D57A2&quot;, end_color=&quot;0D57A2&quot;, fill_type = &quot;solid&quot;)
  whiteFont = Font(color=colors.WHITE)
   
  ##########################################
  &quot;&quot;&quot;
   
  for xws in wb.sheetnames:
      sheet = wb[xws]
      sheet.delete_rows(74, 1)
      sheet.delete_rows(72, 1)
      sheet.delete_rows(70, 1)
      sheet.delete_rows(30, 1)
      sheet.delete_rows(28, 1)
      sheet.delete_rows(4, 1)
      sheet.delete_rows(2, 1)
  wb.save(xlfile)
   
  &quot;&quot;&quot;
  ##########################################
   
   
  for xws in wb.sheetnames:
      sheet = wb[xws]
      sheet.merge_cells('A1:F1')
      sheet['A1'].fill = blueFill
      sheet['A1'].font = whiteFont
      sheet['A1'].alignment = Alignment(horizontal='center')
      sheet.delete_rows(2,1)
      sheet.delete_rows(3,1)
      sheet['A2'] = &quot;Period End&quot;
      sheet['A2'].fill = blueFill
      sheet['A2'].font = whiteFont
      sheet['B2'].fill = blueFill
      sheet['B2'].font = whiteFont
      sheet['C2'].fill = blueFill
      sheet['C2'].font = whiteFont
      sheet['D2'].fill = blueFill
      sheet['D2'].font = whiteFont
      sheet['E2'].fill = blueFill
      sheet['E2'].font = whiteFont
      sheet['F2'].fill = blueFill
      sheet['F2'].font = whiteFont
  wb.save(xlfile)
   
  for xws in wb.sheetnames:
      sheet = wb[xws]
      sheet.merge_cells('A6:F6')
      sheet['A6'].fill = blueFill
      sheet['A6'].font = whiteFont
      sheet['A16'].fill = blueFill
      sheet['A16'].font = whiteFont
      sheet['B16'].fill = blueFill
      sheet['B16'].font = whiteFont
      sheet['C16'].fill = blueFill
      sheet['C16'].font = whiteFont
      sheet['D16'].fill = blueFill
      sheet['D16'].font = whiteFont
      sheet['E16'].fill = blueFill
      sheet['E16'].font = whiteFont
      sheet['F16'].fill = blueFill
      sheet['F16'].font = whiteFont
  wb.save(xlfile)
   
  for xws in wb.sheetnames:
      sheet = wb[xws] 
      sheet.merge_cells('A25:F25')
      sheet['A25'].fill = blueFill
      sheet['A25'].font = whiteFont
      sheet['A25'].alignment = Alignment(horizontal='center')
      sheet.delete_rows(26,1)
      sheet['A26'] = &quot;Period End&quot;
      sheet['A26'].fill = blueFill
      sheet['A26'].font = whiteFont
      sheet['B26'].fill = blueFill
      sheet['B26'].font = whiteFont
      sheet['C26'].fill = blueFill
      sheet['C26'].font = whiteFont
      sheet['D26'].fill = blueFill
      sheet['D26'].font = whiteFont
      sheet['E26'].fill = blueFill
      sheet['E26'].font = whiteFont
      sheet['F26'].fill = blueFill
      sheet['F26'].font = whiteFont
      sheet.delete_rows(27,1)
      sheet.merge_cells('A27:F27')
      sheet['A27'].fill = blueFill
      sheet['A27'].font = whiteFont
      sheet['A27'].alignment = Alignment(horizontal='center')
      sheet.merge_cells('A47:F47')
      sheet['A47'].fill = blueFill
      sheet['A47'].font = whiteFont
      sheet['A47'].alignment = Alignment(horizontal='center')
      sheet.merge_cells('A59:F59')
      sheet['A59'].fill = blueFill
      sheet['A59'].font = whiteFont
      sheet['A59'].alignment = Alignment(horizontal='center')
      sheet.merge_cells('A67:F67')
      sheet['A67'].fill = blueFill
      sheet['A67'].font = whiteFont
      sheet['A67'].alignment = Alignment(horizontal='center')
      sheet.delete_rows(68,1)
      sheet['A68'] = &quot;Period End&quot;
      sheet['A68'].fill = blueFill
      sheet['A68'].font = whiteFont
      sheet['B68'].fill = blueFill
      sheet['B68'].font = whiteFont
      sheet['C68'].fill = blueFill
      sheet['C68'].font = whiteFont
      sheet['D68'].fill = blueFill
      sheet['D68'].font = whiteFont
      sheet['E68'].fill = blueFill
      sheet['E68'].font = whiteFont
      sheet['F68'].fill = blueFill
      sheet['F68'].font = whiteFont
  wb.save(xlfile)
   
   
  for xws in wb.sheetnames:
      sheet = wb[xws] 
      sheet.merge_cells('A70:F70')
      sheet['A70'].fill = blueFill
      sheet['A70'].font = whiteFont
      sheet['A70'].alignment = Alignment(horizontal='center')
      sheet.merge_cells('A82:F82')
      sheet['A82'].fill = blueFill
      sheet['A82'].font = whiteFont
      sheet['A82'].alignment = Alignment(horizontal='center')
  wb.save(xlfile)
   
   
  for xws in wb.sheetnames:
      sheet = wb[xws] 
      sheet.merge_cells('A89:F89')
      sheet['A89'].fill = blueFill
      sheet['A89'].font = whiteFont
      sheet['A89'].alignment = Alignment(horizontal='center')
      sheet.merge_cells('A98:F98')
      sheet['A98'].fill = blueFill
      sheet['A98'].font = whiteFont
      sheet['A98'].alignment = Alignment(horizontal='center')
  wb.save(xlfile)
   
   
   
  ##########################################
   
  for xws in wb.sheetnames:
      worksheet = wb[xws]
      #print(worksheet)
      tuple(worksheet['B138':'B138'])
      for rowOfCellObjects in worksheet['B138':'B138']:
          for cellObj in rowOfCellObjects:
              v1 = worksheet['B138'].value
              v2 = text_to_num(v1)
              v3 = truncate(v2, 0)
              cellObj.value = v3
              if cellObj.value &gt; 0:
                  cellObj.number_format = '[&lt;999950] 0.0,&quot;K&quot;;[&lt;999950000]0.0,,&quot;M&quot;;0.0,,,&quot;B&quot;'
              elif cellObj.value == 0:
                  cellObj.number_format = '[&lt;999950] 0.0,&quot;K&quot;;[&lt;999950000]0.0,,&quot;M&quot;;0.0,,,&quot;B&quot;'
              else:
                  cellObj.number_format = '[&gt;-999950]0.0,&quot;K&quot;;[&gt;-999950000]0.0,,&quot;M&quot;;0.0,,,&quot;B&quot;'
              cellObj.fill = PatternFill(fgColor='DFECF7', fill_type = 'solid')
              wb.save(xlfile)
              v1 = None
              v2 = None
              v3 = None
              v4 = None
  ##########################################
   
  dash = &quot;-&quot;
  for xws in wb.sheetnames:
      worksheet = wb[xws]
      tuple(worksheet['B3':'F96'])
      for rowOfCellObjects in worksheet['B3':'F96']:
          for cellObj in rowOfCellObjects:
              cellValue = cellObj.value
              if cellValue == dash:
                  cellObj.value = 0
          #print('--- END OF ROW CHANGE---')
  wb.save(xlfile)
   
  ##########################################
   
  ################################
  print('B3 to F5')
  for xws in wb.sheetnames:
      worksheet = wb[xws]
      dash = &quot;-&quot;
      tuple(worksheet['B3':'F5'])
      for rowOfCellObjects in worksheet['B3':'F5']:
          for cellObj in rowOfCellObjects:
              cellValue = cellObj.value
              if cellValue != dash:
                  if cellValue != 0:
                      if cellValue != None:
                          x = str(cellValue)
                          y = str(x) + &quot;,000&quot;
                          cellObj.value = y
                          cellObj.value = cellObj.value.replace(',', '')
                          cellObj.value = int(cellObj.value)
                          wb.save(xlfile)
                          if dash not in cellValue:
                              cellObj.number_format = '[&lt;999950] 0.0,&quot;K&quot;;[&lt;999950000]0.0,,&quot;M&quot;;0.0,,,&quot;B&quot;'
                          else:
                              cellObj.number_format = '[&gt;-999950]0.0,&quot;K&quot;;[&gt;-999950000]0.0,,&quot;M&quot;;0.0,,,&quot;B&quot;'
                          #
                          cellObj.value = int(float(cellObj.value))
                          #press('enter')
                      #print('--- END OF ROW CHANGE---')
  wb.save(xlfile)
  ################################
   
  ######################
  print('B7 to F15')
  for xws in wb.sheetnames:
      worksheet = wb[xws]
      dash = &quot;-&quot;
      tuple(worksheet['B7':'F15'])
      for rowOfCellObjects in worksheet['B7':'F15']:
          for cellObj in rowOfCellObjects:
              cellValue = cellObj.value
              if cellValue != dash:
                  if cellValue != 0:
                      if cellValue != None:
                          x = str(cellValue)
                          y = str(x) + &quot;,000&quot;
                          cellObj.value = y
                          cellObj.value = cellObj.value.replace(',', '')
                          wb.save(xlfile)
                          if dash not in cellValue:
                              cellObj.number_format = '[&lt;999950] 0.0,&quot;K&quot;;[&lt;999950000]0.0,,&quot;M&quot;;0.0,,,&quot;B&quot;'
                          else:
                              cellObj.number_format = '[&gt;-999950]0.0,&quot;K&quot;;[&gt;-999950000]0.0,,&quot;M&quot;;0.0,,,&quot;B&quot;'
                          #
                          cellObj.value = int(float(cellObj.value))
                          #press('enter')
                      #print('--- END OF ROW CHANGE---')
  wb.save(xlfile)
  </code></pre>