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.
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
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
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