Multiple questions with 1 macro

[Bestmiler]

Hi guys. So I wrote a code to sort (by ascending order) column H which has a column header 'Balance' (so cell H1). If there are any zero or negative balances, a message box would be produced asking if they should be deleted (yes/no). If there are no zero or negative balances, then the message box would say there are none. I have written a code below but it doesn't do exactly what I want it to.

So there are some issues that i need help with.

1. If I add another column, the code would sort column H which would no longer be the 'Balance' column. How could I wrote that piece of the code to search by column name ('Balance') and sort that column instead.

2. It seems that my piece of the code for searching for any values zero or negative does not actually do that. Is there a better way to search for that?

Sub deletezero()

    'Sorting the Balance column
    Columns("A:BB").Sort key1 = Range("H:H"), order1 = xlAscending, Header = xlYes
 
    'Selecting the Balance column and creating a variable
    Columns("H:H").Select
    Dim FindZero As Integer
 
    If FindZero <= 0 Then
 
    'Yes or No message box
    answer = MsgBox("There are zero or negative values. Would you like to delete?", vbYesNo + vbQuestion, "Zero or Negative Values")
 
        If answer = vbYes Then
     
            Dim iRow As Long, firstRow As Long, lastRow As Long
            firstRow = ActiveSheet.UsedRange.Cells(1, 1).Row
            lastRow = Clls.SpecialCells(xlCellsTypeLastCell).Row
            For iRow = lastRow To firstRow Step -1
            If Cells(iRow, 8) <= 0 Then Rows(iRow).Delete
            Next Row
        Else
        'do nothing
        End If
 
    Else
        MsgBox "There are no zero or negative values"
     
    End If

End Sub

 

[Marc L]

Hi !

Read and follow the tip under (or just above the post frame) …

To find a column, use Match worksheet function
and to detect if zero or negative numbers use CountIF worksheet function …
​

 

[Bestmiler]

So I'm using the following code below to find the column number but it won't work:

X = Application.Match("Balance", Range("A1:BB1"), 0)

MsgBox X


I'm getting a run-time error 13: type mismatch

 

[NARAYANK991]

Hi ,

If the text Balance is not found in the range A1:BB1 , then X will contain an error value , and trying to output this using a MsgBox statement will generate a Run Time Error.

The way out is to check for this condition using the IsError function , as follows :

If VBA.IsError(X) Then
   MsgBox "Text not found in Header"
Else
   MsgBox X
Endif

Narayan