×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Macro to read Strings and Values in Rows

Excel Macro to read Strings and Values in Rows

Excel Macro to read Strings and Values in Rows

(OP)
I am currently developing an Excel macro in VB that can format spreadsheets that I receive everyday.  I have developed a code that reads down Column A which contains numeric values(0-10) in order to pinpoint the exact rows that I need formatted.  For example, the code will read down Column A, find any cell with a 2, and make that row bold.  The code has no problem reading up and down ColumnA, however, I have run into a problem when trying to read cells across the row.  I want the code to recognize any cell in Column A with a numeric value of 5, read across the row(through cells containing text, nulls, and numbers), and recognize all cells containing a 0.  After the cell is determined to have a 0 in it, I want the code to hide the entire column and move on until the end of the range.  This is what I have so far.



Dim CurrentCell As Object, NextCell As Object
    
    ActiveWorkbook.Sheets("Sheet1").Select
    Set CurrentCell = ActiveSheet.Range("A1")
    Do While Not IsEmpty(CurrentCell)
        Set NextCell = CurrentCell.Offset(1, 0)
    'reads down Column A to pinpoint values        
            If CurrentCell.Value = 5 Then
                Do While ActiveSheet.Range("NoNull").Select
                    Set NextCell = CurrentCell.Offset(0, 1)
    'attempts to read across row                
                    If CurrentCell = 0 Then
                        Columns.Select
                        Selection.EntireColumn.Hidden = True
                    End If
                    Set CurrentCell = NextCell
                Loop
            End If
            Set CurrentCell = NextCell
    Loop

End Sub


The code will not step through the rows and I believe that it is getting stuck on the cells containing text, null, and numbers that are not 0.  So, in essence, I am trying to figure out a way to get my code to recognize whether an active cell is a string, a null, or a numeric value.  Any advice would be greatly appreciated.  

RE: Excel Macro to read Strings and Values in Rows

I am not exactly sure what you need, but the following code may get you going into the right direction. I got a bit confused with your CurrentCell and NextCell, so I changed it to have CurrentRow point to the first cell of the row you are examining, and have CurrentCol go through all the cells in the row until it finds an empty cell (then CurrentRow is increased). The sub stops when CurrentRow finds an empty cell. You may want to modify the sub to stop on other conditions, it's up to you.
To prevent the sub failing when it reads a string instead of a number, I have added an If statement testing for IsNumeric(CurrentCol) - this may after all be the only thing you were looking for...


Sub whatever()
Dim CurrentRow As Range, CurrentCol As Range
    
    ActiveWorkbook.Sheets("Sheet1").Select
    Set CurrentRow = ActiveSheet.Range("A1")
    Do While Not IsEmpty(CurrentRow)
    'reads down Column A to pinpoint values
        If IsNumeric(CurrentRow) Then
            If CurrentRow.Value = 5 Then
                Set CurrentCol = CurrentRow.Offset(0, 1)
                Do While Not IsEmpty(CurrentCol)
        'attempts to read across row
                    If IsNumeric(CurrentCol) Then
                        If CurrentCol.Value = 0 Then CurrentCol.Columns.Hidden = True
                    End If
                    Set CurrentCol = CurrentCol.Offset(0, 1)
                Loop
            End If
        End If
        Set CurrentRow = CurrentRow.Offset(1, 0)
    Loop
End Sub

Cheers,

Joerd

RE: Excel Macro to read Strings and Values in Rows

(OP)
Joerd,

Thank you very much for your help.  I was unaware that the IsNumeric function could read through null values as well as bypass the strings.  I have inputted your code into my spreadsheet and it works like a charm.  I give you credit for helping me with my problem.  Again, thanks a million for your help.

Regards,

asumter  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close