Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

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

0
Seamus2626
Asked:
Seamus2626
  • 3
  • 3
1 Solution
 
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
 
StephenJRCommented:
Your End If on line 23 should be after line 32. That should do it I think.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Seamus2626Author Commented:
I tried that one earlier and i get the message, End IF without block if

0
 
StephenJRCommented:
Did you make sure the End If was after the End With?
0
 
Seamus2626Author Commented:
Legend!!!!

Thanks
Seamus
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now