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.