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

 

[Gomes1985]

Hi Trebor,

I have done in the past is very same program but different parts.

This Method is a totally different problem that i haven't asked before you can have a look if you would like.

I Explained on above Threads exactly what my Main program does.

if you dont understand Which part is it not understand? I know you have posted past issues I rasied and they were really effective I thank you for that.

most of the before is how to do as ur past solutions have given me ways to integrate to my program.

Its hard to put my whole code here as its has B2K screen capture and copy file and how the reading is done.

Which is Pages and pages of Macro.

So I gave the description what my program does.

Anyway the problem in this logical code relies just this part

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

but condition is logically wrong therefore it exits is the issue.

If anything u dont understand point out which part so i can explain more broadly

hope that helps

 

[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

Both column A and C have to be blank for that to exit.

When do you want the program to stop looking at rows?

Maybe you just want

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 IsNumeric(.Cells(RowCounter, "A")) Then
            Call proNewMacro
        End If
    Next
    End With
    MsgBox ("The End")
End Sub

 

[Gomes1985]

Thanks Sericom,

You cracked it.

I have done bit of mistakes on my initial code If Len(.Cells(RowCounter, "A")) = 0 And Len(.Cells(RowCounter, "C")) = 0 Then Exit For

Which Should be If Len(.Cells(RowCounter, "D")) = 0 And Len(.Cells(RowCounter, "E")) = 0 Then Exit For

Previous code you entered was correct Only reason why it was not going to next line was IntialRow was not increasing so i put that

Final correct code is:

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, "B").End(xlUp).Row
If Len(.Cells(RowCounter, "D")) = 0 And Len(.Cells(RowCounter, "E")) = 0 Then Exit For
If IsNumeric(.Cells(RowCounter, "A")) Then
Call proNewMacro
End If
InitialRow = InitialRow + 1
Next
End With
MsgBox ("The End")
End Sub

Thanks Everyone for your help I finished my code now

 

[sericom]

InitialRow = InitialRow + 1

This won't be doing anything, try it without it.

RowCounter is what moves you down the rows, it increments automatically by 1 on each For Next loop

 

[Gomes1985]

Yeah what your saying is true,

In this method InitialRow does nothing. using next Loop moves and I dont need that in this method

In the Main program Initial row I use to assign values. To give you an answer for your curiosity is the below code

Public Sub proNewMacro()

inibsb = Sheets("sheet1").Range("B" & InitialRow).Value
debitbsb = Sheets("sheet1").Range("D" & InitialRow).Value
debitacc1 = Sheets("sheet1").Range("E" & InitialRow).Value
creditbsb = Sheets("sheet1").Range("G" & InitialRow).Value
creditacc = Sheets("sheet1").Range("H" & InitialRow).Value

Etc....... Goes on and on.........
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

Thats why i need InitialRow

Thanks again