Sub error

Hi,

I have a sub below

My aim is if the cell in A2 of the sheet is blank, then two cells get populated with 0. If not, they look for grand total and count of data in that worksheet
The problem with the attached code is that when it realises A2 is blank, it goes to the end if which then goes to the offset

if the A2 is blank, i need it to fill out the Zeros and then go onto the loop, ignoring the rest of the code

I have tried putting the end if underneath
  ActiveCell.Offset(1, 0).Select

But then it doesnt recognize the loop

Can anyone help?

Thanks
Seamus


Sub SumUpForTable2()
        Dim FoundCell As Range

        Sheets("Corporate Actions").Select
        Range("B3").Select

        Do Until ActiveCell.Value = "MI LEDGER"
            mysheetname = ActiveCell.Text
            Sheets(mysheetname).Select
            If Range("A2").Value = "" Then

                Sheets("Corporate Actions").Select
                ActiveCell.Offset(0, 1).Value = "0"
                ActiveCell.Offset(0, 2).Value = "0"
                ActiveCell.Offset(1, 0).Select
                
            Else
            
   
             Set FoundCell = Columns("C:C").Find(What:="Grand Total", LookIn:=xlValues, _
                    lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
            End If
            With FoundCell
                .Offset(0, 7).Select
                ActiveCell.Resize(1, 2).Select
                Selection.Copy
                Sheets("Corporate Actions").Select
                ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
                ActiveCell.Offset(1, -1).Select
                
            End With
        Loop
        Range("C3:C23").Select
        Selection.NumberFormat = "#,##0"
    End Sub

Open in new window

Seamus2626Asked:
Who is Participating?
 
StephenJRCommented:
Did you make sure the End If was after the End With?
0
 
StephenJRCommented:
"if the A2 is blank, i need it to fill out the Zeros and then go onto the loop, ignoring the rest of the code"

When you say go onto the loop, where exactly do you mean?

0
 
Seamus2626Author Commented:
       End With
        Loop


At the bottom, because once it completes

           If Range("A2").Value = "" Then

                Sheets("Corporate Actions").Select
                ActiveCell.Offset(0, 1).Value = "0"
                ActiveCell.Offset(0, 2).Value = "0"
                ActiveCell.Offset(1, 0).Select

it should ignore the rest of the code and loop back to

mysheetname = ActiveCell.Text
            Sheets(mysheetname).Select
            If Range("A2").Value = "" Then

Cheers,
Seamus
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
StephenJRCommented:
Your End If on line 23 should be after line 32. That should do it I think.
0
 
Seamus2626Author Commented:
I tried that one earlier and i get the message, End IF without block if

0
 
Seamus2626Author Commented:
Legend!!!!

Thanks
Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.