55782

Search for a Cell in Excel using VBA

Question:

Problem: I would like to find a value of a cell next to or below the cell content a text value for a workbook.

Example: In Sheet2, I have two cells stand random (assume its index is unknown and total is not a defined name)

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/TIcE8.png" data-original="https://i.stack.imgur.com/TIcE8.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

I want to search for the value "200" stands next to the total and put it in sheet 2 (an active cell). In case, there are multiple cell that contains the word "Total" list all of them and if possible, put the name of the sheet that contains the cell that I am looking for. Value 200 Sheet2

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/WzPEb.png" data-original="https://i.stack.imgur.com/WzPEb.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

My Approach: 1. User input

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/0rKAp.png" data-original="https://i.stack.imgur.com/0rKAp.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

<ol start="2"><li>

Go to each cell and search for it. This will take time if search for the whole limitation of cell in excel. So the search only limit to 100 columns x 10000 rows.

</li> <li>

After find its index, offset to 1 columns to get the value

</li> <li>

Write the result ActiveCell.Value = Search_Value. Then continue to search for the rest of sheets. Offset 1 coloum and 1 row to write the second value...

</li> </ol>

Searching is a very difficult concept, and I truly have no idea how to do the search part. Please help

Answer1:

With Worksheets(1).Range("a1:a500") counter=0 Set c = .Find("Total", lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do counter=counter+1 Worksheets(2).range("A1").offset(counter,0)=c.offset(0,1) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With

<strong>firstaddress</strong> holds the location of the first cell found (so we know when to stop); <strong>firstaddress.offset(0,1)</strong> will give you the value you are trying to save, so setting <strong>worksheet(2).range("a1").offset(counter,0)</strong> will list all the values it finds on the 2nd tab, from a1 down to however many it finds in the range

Recommend

  • pip in virtualenv gets ConnectTimeoutError
  • Mysql - How to search for 26 records that each begins with the letter of the alphabet?
  • why xml file does not aligned properly after append the string in beginning and end of the file usin
  • Marklogic : Query response time is very high
  • Create DicomImage from scratch using Dcmtk
  • htaccess add www if not subdomain, if subdomain remove www
  • Declaring variable dynamically in VB.net
  • MongoDb aggregation
  • one Local Olampyad Questions on Informatic in 2011
  • JSON response opens as a file, but I can't access it with JavaScript
  • Replace value with Factor in r data.table
  • Could not find rake using whenever rails
  • How to do unit test for HttpContext.Current.Server.MapPath
  • Android screen density dpi vs ppi
  • Splitting given String into two variables - php
  • Align navbar back button on right side
  • Accessing IRQ description array within a module and displaying action names
  • Bug in WPF DataGrid
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Join two tables and save into third-sql
  • How to apply VCL Styles to DLL-based forms in Inno Setup?
  • Timeout for blocking function call, i.e., how to stop waiting for user input after X seconds?
  • Comma separated Values
  • File upload with ng-file-upload throwing error
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • How do I configure my settings file to work with unit tests?
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • Turn off referential integrity in Derby? is it possible?
  • How does Linux kernel interrupt the application?
  • Authorize attributes not working in MVC 4
  • How can I remove ASP.NET Designer.cs files?
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • Add sale price programmatically to product variations
  • Busy indicator not showing up in wpf window [duplicate]
  • Unable to use reactive element in my shiny app
  • Python/Django TangoWithDjango Models and Databases
  • java string with new operator and a literal
  • Net Present Value in Excel for Grouped Recurring CF
  • How do I use LINQ to get all the Items that have a particular SubItem?