Excel Macro - If Cell contains a character go to the next row and STOP when Empty

[Gomes1985]

Hi All,

I Am reading and excel sheet using macro. I have Put a condition already if the code come across ani ISNummeric = false Skip it to next line.

I wanna know if the code comes a line where Column A B and C is empty Code should end DO Not go Beyond that row. Below is the table as per table below

Code should end after line 3(SEQ)

The job Exits If I have 2 Character values in Column A

I think just a logical error or I am not using the Code at right place.

[TABLE="class: cms_table_cms_table, width: 192"]
<tbody>[TR]
[TD="class: cms_table_cms_table_xl63, width: 64"]SEQ[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]acc[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]bsb[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_xl63, width: 64"]bas[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]123[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]4567[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_xl63, width: 64"]bas[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]456[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]789[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_xl63, width: 64"]2[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]123[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]4567[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_xl63, width: 64"]3[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]147[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]78[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_xl64"][/TD]
[TD="class: cms_table_cms_table_xl64"][/TD]
[TD="class: cms_table_cms_table_xl64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_xl63, width: 64"]4[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]474[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]456[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_xl63, width: 64"]5[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]741[/TD]
[TD="class: cms_table_cms_table_xl63, width: 64"]475[/TD]
[/TR]
</tbody>[/TABLE]


Public Sub proIterate()
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 .Cells(RowCounter, "A").Value = "" And _
.Cells(RowCounter, "B").Value = "" And _
.Cells(RowCounter, "C").Value = "" Then Exit For
End With
If IsNumeric(ThisWorkbook.Sheets("Sheet1").Cells(RowCounter, "A")) = True Then
Call proNewMacro
End If
InitialRow = InitialRow + 1
Next
MsgBox ("The End")
End Sub

 

[Trebor76]

Hello Gomes1985 (again),

I'm not too sure about some of your code but you can use the following to stop the macro if there's nothing in columns A, B & C:

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

Regards,

Robert

 

[MARK858]

Duplicate post?

http://www.mrexcel.com/forum/excel-...f-cell-empty-code-should-end.html#post4558398

 

[Gomes1985]

Hi Trebor76,

Sorry I had to come back again Code it right.

its logical error.

What happens is since its to String It does not go to 3 rd row for some reason.

I might have to pull apart

With ThisWorkbook.Sheets("Sheet1")
If .Cells(RowCounter, "A").Value = "" And _
.Cells(RowCounter, "B").Value = "" And _
.Cells(RowCounter, "C").Value = "" Then Exit For
End With

Code checks for Numerics and work fine

even skips. Code is working fine but Loop Exits at Row 3

What should happen is it should read

[TABLE="class: cms_table_cms_table_cms_table, width: 192"]
<tbody>[TR]
[TD="class: cms_table_cms_table_cms_table_xl63, width: 64"]2[/TD]
[TD="class: cms_table_cms_table_cms_table_xl63, width: 64"]123[/TD]
[TD="class: cms_table_cms_table_cms_table_xl63, width: 64"]4567[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_cms_table_xl63, width: 64"]3[/TD]
[TD="class: cms_table_cms_table_cms_table_xl63, width: 64"]147[/TD]
[TD="class: cms_table_cms_table_cms_table_xl63, width: 64"]78
[/TD]
[/TR]
</tbody>[/TABLE]
Then Exit the loop.

Code is correct its Logical error I think

Funny thing is if I have Only 2 records it works too

p.s - Its not a duplicate post Its slightly different issue

 

[MARK858]

p.s - Its not a duplicate post Its slightly different issue

How is post #1 of this thread different to the question you asked in post #6 of the other thread