If statement with multiple actions to complete based on the condition

[gingerbreadgrl]

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):

[FONT=Verdana]' 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.[/FONT]
[FONT=Verdana]
If Sheets("Clt Info").Range("A3").Value > 0 Then[/FONT]
[FONT=Verdana]
Sheets.Add After:=ActiveSheet[/FONT]
[FONT=Verdana]ActiveSheet.Name = "Record 2"[/FONT]


[FONT=Verdana]Sheets("Guardianship Doc Gen").Range("A1:AGA1").Copy[/FONT]
[FONT=Verdana]Sheets("Record 2").Range("A1").Select[/FONT]
[FONT=Verdana]Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks:=False, Transpose:=True[/FONT]
[FONT=Verdana]
Sheets("Guardianship Doc Gen").Range("A3:AGA3").Copy[/FONT]
[FONT=Verdana]Sheets("Record 2").Range("B1").Select[/FONT]
[FONT=Verdana]Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks:=False, Transpose:=True[/FONT]
[FONT=Verdana]
On Error Resume Next
Sheets("Record 2").Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0[/FONT]
[FONT=Verdana]
Dim wa As Worksheet
Set wa = Sheets("Record 2")[/FONT]
[FONT=Verdana]With wa.UsedRange
    .HorizontalAlignment = xlLeft
    .EntireColumn.AutoFit
End[/FONT]
[FONT=Verdana]
End If[/FONT]

Any insight into why I'm getting this error would be much appreciated.

Thanks,
Gingerbreadgrl

 

[shknbk2]

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.

 

[gingerbreadgrl]

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

 

[shknbk2]

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.

 

[gingerbreadgrl]

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