Macro Inquiry - Beginner Support

  • Re: Macro Inquiry - Beginner Support


    Maze,


    I'll adjust the code I posted to try and answer your questions.






    If you wanted to test for a "BLANK" then


    Code
    If cell = "" Then


    The only issue with the above is what if the cell has a BLANK space ( someone who wanted to blank the cell and used the spacebar ) so a better test for a blank cell would be

    Code
    If trim(cell) = "" Then
  • Re: Macro Inquiry - Beginner Support


    The extra line of code you provided highlights all the cells with text in them below the row that includes the first cell with "Within Footprint" text contained in it. Can you break down this line of code for me?


    Code
    cell.Range("A1",cell.range("IV1").end(xlToLeft)).Interior.ColorIndex = 3


    I understand the first part cell.Range will span the cell range you want to include. The A1 I'm guessing is the starting point. What is IV1 actually mean and what does x1ToLeft mean? The Interior.ColorIndex=3 I understand, this would highlight the interior of the cell the color 3, which is assigned to red.

  • Re: Macro Inquiry - Beginner Support


    Maze,


    Ok that was my fault replace that one line of code with the following:


    Code
    cell.Range("A1", Range("IV1").End(xlToLeft)).interior.colorindex = 3


    You seem to have grasped the concept of what I've done. Try doing a help on END that'll give you what the END does. Then IV1 that's the last column available on a worksheet, that with the information from END you'll know why I reference it.

  • Re: Macro Inquiry - Beginner Support


    Alright if the code highlights to the last column of text then how come if I add a new column of text to Column G that it still only highlights to Column F?

  • Re: Macro Inquiry - Beginner Support


    Maze,


    In looking at the workbook that you posted you had a solid table of data. Meaning that all cells had values from A To F.


    Now if you add a new column of data but leave the cells blank the end method will find the first cell with data to the left of IV hence if the new columng "G" is blank the first found cell is still "F"


    Now if your table will not have data in all fields, i.e. some rows will have data A to E, or A to D, or A to G then you need to


    1. Hard code the the G range. ( But what happens when you add a column H of data )
    2. Determine the width of the data table by the column headers. ( This will take into account if you add 1 or n number of columns )

  • Re: Macro Inquiry - Beginner Support


    Oh alright, I see what you are saying. I'm just trying now to run the macro searching for a blank cells in Column G. First I tried removing any text within the quotes and basically it highlighted all the rows that had text with in it. Where would I need to place the IF statement?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!