Excel Macro - Using IsNumeric and Cell IsEmpty as a condition

[Gomes1985]

If IsNumeric(ThisWorkbook.Sheets("sheet1").Cells(RowCounter, "A")) = True Then
Call Method
End If
InitialRow = InitialRow + 1

Next
MsgBox ("The End")
End Sub


Hi All,

Above is the Code if the Column A has a Character go to Next line Works Perfect. BUT it Exits if the Column A is Empty too

But currently what I DONT WANT is not to skip if column A is EMPTY

so IF the Column A has Character ONLY go to next line BUT IF Column A has a Numeric or EMPTY read the line

Any help would be grateful

 

[sericom]

IsNumeric will return True on numbers and blanks. I'm guessing you want to 'Call Method' only on numbers and not blanks, so

If  IsNumeric(ThisWorkbook.Sheets("sheet1").Cells(RowCounter, "A")) And Not  IsEmpty(ThisWorkbook.Sheets("sheet1").Cells(RowCounter, "A"))  Then

 

[Gomes1985]

Thanks Sericom,

I tried but the condition does NOT satisfy to call the method so it run inside the For Loop and then its ends

I did some changes to ur code and tried below

If IsNumeric(ThisWorkbook.Sheets("sheet1").Cells(RowCounter, "A")) And Not IsEmpty(ThisWorkbook.Sheets("sheet1").Cells(RowCounter, "A")) Then



Below code I tried with different way

If IsNumeric(ThisWorkbook.Sheets("sheet1").Cells(RowCounter, "A")) = True And IsEmpty(ThisWorkbook.Sheets("sheet1").Cells(RowCounter, "A")) = false Then


Still I cant figure out. Even the code I tried Has to be ISNumeric AND IsEmpty didnt work either it Only reads 1 line and EXITS

 

[GTO]

Hi there,

I would suggest showing us at least the entire loop and mention what happens in "Method".

 

[Gomes1985]

Public Sub pro()
Dim RowCounter As Long
InitialRow = 2
'Write a procedure that counts the number of rows...
FinalRow = BaseSheet.Cells(BaseSheet.Rows.Count, "B").End(xlUp).Row 'This gives the number of data in the column "B"


For RowCounter = InitialRow To ThisWorkbook.Sheets("sheet1").Cells(ThisWorkbook.Sheets("sheet1").Rows.Count, "A").End(xlUp).Row ' The loop you wanted
With ThisWorkbook.Sheets("sheet1")
If Len(.Cells(RowCounter, "A")) = "" And _
Len(.Cells(RowCounter, "C")) = 0 Then Exit For
End With
If IsNumeric(ThisWorkbook.Sheets("sheet1").Cells(RowCounter, "A")) = True And IsEmpty(ThisWorkbook.Sheets("sheet1").Cells(RowCounter, "A")) = False Then
Call proNewMacro
End If
InitialRow = InitialRow + 1
Next
MsgBox ("The End")
End Sub

 

[GTO]

...if the Column A has a Character go to Next line Works Perfect. BUT it Exits if the Column A is Empty too

But currently what I DONT WANT is not to skip if column A is EMPTY

so IF the Column A has Character ONLY go to next line BUT IF Column A has a Numeric or EMPTY read the line

Any help would be grateful

Thank you for posting the procedure. In reading your request, I am unclear as to what you are trying to do. In reading the code as posted, it seems a tad confusing to me leastwise. 'InitialRow' is not dim'med, but is set to 2 initially. Then InitialRow is used in the parameters for the loop, which granted, are only evaluated once, but changing InitialRow's value within the loop seems unusual. I do not see where you use InitialRow, why the 'InitialRow = InitialRow + 1' ?

Finally, what is FinalRow for?

Mark

 

[Gomes1985]

Hi Mark,

I get it could be confusing because i have posted is a Sub method of a large Program.

What the program does is it reads row by row and put the data in to B2K screen.

What you mentioned above InitialRow is already dimmed Global - I needed to start the reading from row 2

Since i am not sure of the size of records everyday I took the Finalrow after Initialised to end the program.

So when reading Starts from row 2 it reads line by line (Works Perfect)

It even Skips to the next line if it come across a NON numeric (Works perfect)

So Empty is also not a Numeric ( because of that ) Loop exits. Hence program exits

So if the Column A has a Character(abcd...z) go to Next line (NON Numeric). if the Column A is Empty too ( Do not exit but read as usual) thats why code below does

If IsNumeric(ThisWorkbook.Sheets("sheet1").Cells(RowCounter, "A")) = True (is numeric Only call the sub method)

id IsEmpty(ThisWorkbook.Sheets("sheet1").Cells(RowCounter, "A")) = True (Mistaken false before call the sub method)

if above both conditions true go to inial row and +1 so start reading the next line

If Not Finalrow = true Exits

This what i want this method to do ( Like filter to main program) Just get only required rows

Hope that helps to understand its a bit complex I know

 

[sericom]

Public Sub pro()
    Dim RowCounter As Long
    InitialRow = 2
    FinalRow = BaseSheet.Cells(BaseSheet.Rows.Count, "B").End(xlUp).Row 'This gives the number of data in the column "B"
    With ThisWorkbook.Sheets("sheet1")
    For RowCounter = InitialRow To .Cells(.Rows.Count, "A").End(xlUp).Row
        If Len(.Cells(RowCounter, "A")) = 0 And Len(.Cells(RowCounter, "C")) = 0 Then Exit For
        If IsNumeric(.Cells(RowCounter, "A")) Then
            Call proNewMacro
        End If
    Next
    End With
    MsgBox ("The End")
End Sub

This will go line by line and Call proNewMacro if Column A is numeric or empty (An empty cell is numeric). The program will exit if it finds a blank in column A AND column C.
Is this what you're after?

 

[Gomes1985]

Thanks Sericom,

The For loop exits from first line and end the program because of below

If Len(.Cells(RowCounter, "A")) = 0 And Len(.Cells(RowCounter, "C")) = 0 Then Exit For

Because Column A is Empty it wont check

below condition

If IsNumeric(.Cells(RowCounter, "A")) Then

Its getting bit too complicated than I actually thought.

Good challenge if anyone want to give it a go :D

 

[Trebor76]

What about this:

Public Sub pro()
    Dim RowCounter As Long
    InitialRow = 2
    FinalRow = BaseSheet.Cells(BaseSheet.Rows.Count, "B").End(xlUp).Row 'This gives the number of data in the column "B"
    With ThisWorkbook.Sheets("sheet1")
    For RowCounter = InitialRow To .Cells(.Rows.Count, "A").End(xlUp).Row
        If Len(.Cells(RowCounter, "A")) > 0 And Len(.Cells(RowCounter, "C")) > 0 Then
            If IsNumeric(.Cells(RowCounter, "A")) Then
                Call proNewMacro
            End If
        End If
    Next RowCounter
    End With
    MsgBox "The End"
End Sub

Good challenge if anyone want to give it a go :D

We need clear advice as to what you want to do. You have posted a number of threads over the past couple of weeks asking the same thing but keep saying everyone's reply is not correct - including senior MVP members like GTO