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

x
?
Solved

Sub Structure error

Posted on 2011-10-27
2
Medium Priority
?
127 Views
Last Modified: 2012-05-12
Hi,

I have put together a sub but am getting two errors, one saying Loop without Do and the other saying If without End f

I cant see the problem, can someone point it out please!

Thanks
Seamus
Sub SumUpForTable()
Dim FoundCell As Range

 Sheets("Corporate Actions").Select
    Range("B3").Select
    
        Do Until ActiveCell.Value = ""
            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)
 


   With FoundCell
      .Offset(0, 7).Select

       ActiveCell.Resize(1, 2).Select
        Selection.Copy
        
        Sheets("Corporate Actions").Select
     ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
        
        
     

             
            Loop

        
        
         End With
        Range("C3:C23").Select
    Selection.NumberFormat = "#,##0"
         
End Sub

Open in new window

0
Comment
Question by:Seamus2626
2 Comments
 
LVL 15

Accepted Solution

by:
Eyal earned 2000 total points
ID: 37037393
keep your code in the proper indentation and it will be easy to find such errors

    Sub SumUpForTable()
        Dim FoundCell As Range

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

        Do Until ActiveCell.Value = ""
            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
                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)
            End With
        Loop
        Range("C3:C23").Select()
        Selection.NumberFormat = "#,##0"
    End Sub

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 37038137
Thank you, i found the error and will post for a solution

Seamus
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question