Results 1 to 5 of 5

Thread: If statement with multiple actions to complete based on the condition
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If statement with multiple actions to complete based on the condition

    Hi All,

    This one is a doozy. I am creating a summary report for each record within a worksheet. The worksheet has a number of columns in it, but it is likely that, for each record (a/k/a row) most of those columns won't have any data associated with them for the particular record. So I am creating a new worksheet for each record and then transposing the data so the headings will all paste into column A and the data will all paste into column B, then all empty cells that do not contain data will delete. I do not know how many records will be in the original worksheet at any given time, but probably not more than 9, so 9 rows of data. But, there could be less, hence the if statement, if the column containing the record number has a cell with a value (a/k/a a record number) then a new sheet will be created for the record and the data will be transposed and formatted. I am getting a "end if without block if" error. I've looked at other posts but they appear to have multiple ifs or else ifs. I only have one if, but I have multiple actions that need to be completed if the condition is met. Here is my code below, I plan to repeat the code for each record (up to 9):

    Code:
    ' If there is a 2nd record exported into the workbook, create a summary worksheet for that new record. This includes:
        ' 1. Create a new worksheet
        ' 2. Rename the worksheet to the record number
        ' 3. Transpose the column headings and that particular record's data into the summary worksheet
        ' 4. Delete any empty rows from the data.
        ' 5. Format data.
    
    If Sheets("Clt Info").Range("A3").Value > 0 Then
    
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "Record 2"
    
    
    Sheets("Guardianship Doc Gen").Range("A1:AGA1").Copy
    Sheets("Record 2").Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks:=False, Transpose:=True
    
    Sheets("Guardianship Doc Gen").Range("A3:AGA3").Copy
    Sheets("Record 2").Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks:=False, Transpose:=True
    
    On Error Resume Next
    Sheets("Record 2").Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
    
    Dim wa As Worksheet
    Set wa = Sheets("Record 2")
    With wa.UsedRange
        .HorizontalAlignment = xlLeft
        .EntireColumn.AutoFit
    End
    
    End If
    Any insight into why I'm getting this error would be much appreciated.

    Thanks,
    Gingerbreadgrl

  2. #2
    Board Regular
    Join Date
    Mar 2016
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: If statement with multiple actions to complete based on the condition

    The "End" by itself after the With statement needs to be "End With". That will close the With block properly. By itself, "End" quits running the code at that point. Since the With block isn't closed properly, the "End If" at the end is looking within the With block for an If. Since it's not there, the error is happening.
    Last edited by shknbk2; Sep 13th, 2019 at 05:55 PM.
    Hobby: VBA and some .Net
    Career: the world of patent and trademark protection

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If statement with multiple actions to complete based on the condition

    Hi Shknbk2,

    Thanks for your reply! I actually thought that might be the issue (based on my VERY limited knowledge of VBA) so I tried it, but it produced an error so I switched it back (thinking that I shouldn't mess with it)! So, the error that it produces is a compile error that is "expected end with," it also highlights the very end of the macro, the "End Sub" line. Hmmm, what do you think?

    Best,
    Gingerbreadgrl

  4. #4
    Board Regular
    Join Date
    Mar 2016
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: If statement with multiple actions to complete based on the condition

    Can you provide the entire code?

    On a side note, if I copy your code above and put Sub lines around it together with the End With, it compiles fine.
    Hobby: VBA and some .Net
    Career: the world of patent and trademark protection

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If statement with multiple actions to complete based on the condition

    Hi Shknbk2,

    Thank you for replying to me, so there was another line of code (with the same code) for the 1st record that also needed to be changed to End With (this code did not have the condition), when I first tested the macro it never produced an error, but when I added in the conditional statement wrapped around the same code for Record 2 the system must not have liked that. Not sure why but silly mistake and an easy fix.

    Thanks so much for your time!
    Gingerbreadgrl

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •