+ Reply to Thread
Results 1 to 11 of 11

Beginner question on how to read cells and changing values to the same cells

  1. #1
    Registered User
    Join Date
    08-29-2019
    Location
    athens
    MS-Off Ver
    2010
    Posts
    6

    Question Beginner question on how to read cells and changing values to the same cells

    Hello colleagues,

    I would like to ask some questions about using VBA in order to read cells in an Excel worksheets, of unknown size (rows) and then I need to read each cell value line by line.
    How can I do this??

    After I read each cell value line by line I have to search for a specific keyword in those lines. If that word is found then I need to perform something on that line ONLY. The action needed is to replace some keywords there

    Last, I need to return everything to the cell I just read, and save that worksheet.

    I need some help here on how to approach this code.


    Thank-you !

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Beginner question on how to read cells and changing values to the same cells

    Check the below link to know how to loop through cells.

    http://codevba.com/excel/for_each_ce...m#.XWeAI9R95xA


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    08-29-2019
    Location
    athens
    MS-Off Ver
    2010
    Posts
    6

    Lightbulb Re: Beginner question on how to read cells and changing values to the same cells

    Thanks 6thSense
    I would like to know if there is a way to search for WHOLE words and to not get false TRUE using instr() function.
    For example, searching for this: '_ABCD_Fail'

    Ret__ABCD_Fail_err1 - this is not a match
    X_ABCD_Fail_ - this is not a match
    _ABCD_Fails - this is not a match
    _ABCD_Fail --> MATCH!

    Can you guys helps me think of a way to this in VBA ?

    Thank-you!

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Hi !

    As a direction, better & faster than reading line by line is to use the Range.Find method
    like you can see its example within the VBA help …

  5. #5
    Registered User
    Join Date
    08-29-2019
    Location
    athens
    MS-Off Ver
    2010
    Posts
    6

    Lightbulb Re: Beginner question on how to read cells and changing values to the same cells

    Is there an option to do something like "find whole word", in Excel?
    I have trying to search for a string within a string but I get false success.
    Also, text in a cell which is delimited by char(10) is giving me trouble when I use Instr() function in order to find text with that cell.

    Any idea will be greatly appreciated.

  6. #6
    Registered User
    Join Date
    08-29-2019
    Location
    athens
    MS-Off Ver
    2010
    Posts
    6

    Lightbulb Re: Beginner question on how to read cells and changing values to the same cells

    Hi ,
    Would like to know if there is an an option to create a backup of the file I am working on.
    Since my code makes changes to the contents of the file I am working on, how would I create a backup first?

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    You can use the SaveCopyAs method …

  8. #8
    Registered User
    Join Date
    08-29-2019
    Location
    athens
    MS-Off Ver
    2010
    Posts
    6

    Question Re: Beginner question on how to read cells and changing values to the same cells

    Hi again.
    Great help so far , so thanks to those who replied.

    Something else I would like to know is how to loop through rows from a specific column? Populated rows should exist or by human error might not be present,
    so I need to verify this beforehand.
    I am suppose to receive an Excel file which holds an unknown number of rows, in column A (no header needed for now). Each cell holds a path to an Excel file which should
    be read later on.
    After each run, on a certain file - I should report whether there was a failure. If an error occurred, then I will "open" column B for this and write in the
    **relevant row**, what the error was.

    How can I do this please?

  9. #9
    Registered User
    Join Date
    08-29-2019
    Location
    athens
    MS-Off Ver
    2010
    Posts
    6

    Re: Beginner question on how to read cells and changing values to the same cells

    Hi,

    In the attached URL, one of the examples of looping through cells a specific column, goes like this:

    Please Login or Register  to view this content.
    What is " Set rng = " ? Why is it empty ?
    Last edited by Pepe Le Mokko; 09-04-2019 at 11:06 AM.

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    Just a very bad codeline as often on Web (or you misread) ! As you can easily check the VBA help of Set statement …

  11. #11
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Beginner question on how to read cells and changing values to the same cells

    @liorde

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I will do it for you this time as you are new on the board

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Changing color format in cells based on 3 range values in a different column cells.
    By konkonmike in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 07-08-2019, 08:11 AM
  2. mapping values from a single cell into multiple cells - beginner needs help
    By JimBalls in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-17-2017, 03:33 PM
  3. [SOLVED] read values from cells and compare them with preset values
    By AHMED_GWAD77 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-24-2016, 02:16 PM
  4. Replies: 7
    Last Post: 06-24-2016, 06:44 AM
  5. Beginner Macro Question - Selecting all cells?
    By mrbenn in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-20-2011, 08:51 AM
  6. Replies: 2
    Last Post: 05-27-2010, 05:29 AM
  7. VBA - Read Values of Many Cells
    By Cecil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2009, 10:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1