End IF Without Block IF Error

[pjmsimmons]

Hi All,
I am trying to create a reasonably complicated macro within an excel workbook. I might end up coming to the forum several times for help but this is my first question on the first part of what I'm trying to do.

I have a worksheet called 'blood' in which I select various cells in column B. For each x in my selection I wish to copy the cell content to a separate worksheet called 'Blood Report' if X.offset (0,9) = "1". The contents of the cell will be pasted into column B of the 'Blood Report ' worksheet starting at cell B65.

thus far I have

Sub Test_Report()


Dim x As Range, lstrw As Long


lstrw = Sheets("Blood Report").Cells(Rows.Count, "B65").End(xlUp).Row


Application.ScreenUpdating = False


With Sheets("Blood")


For Each x In Selection


If x.Offset(0, 9) = "1" Then


With Sheets("Blood Report").Range("B65:B" & lstrw) = x




End If


Next x




End Sub

but I keep getting an error End IF Without Block If.

any help available?

regards,

Paul

 

[timfritsch]

The

With Sheets("Blood Report").Range("B65:B" & lstrw) = x

statment needs an End With.


Tim

 

[pjmsimmons]

Thanks Tim. I have added two End With statements but seem to have generated another error on the 'lstrw = ' line. Sorry to ask but do you have any further help?

Sub Test_Report()


Dim x As Range, lstrw As Long


lstrw = Sheets("Blood Report").Cells(Rows.Count, "B").End(xlUp).Row


Application.ScreenUpdating = False


With Sheets("Blood")


For Each x In Selection


If x.Offset(0, 9) = "1" Then


With Sheets("Blood Report").Range("B65:B" & lstrw) = x


End With


End If


Next x


End With


End Sub

No error is generated but neither is it copying the data from the 'Blood' Sheet to the 'Blood Report' sheet.

 

[pjmsimmons]

you can ignore this statement 'No error is generated but neither is it copying the data from the 'Blood' Sheet to the 'Blood Report' sheet.'

 

[timfritsch]

The "B65" is causing the error. Excel is looking for a column number, and while "B" will work, Excel can't convert the columun number of column "B65". If you want to get the last row in column B, change to "B" or just the number 2.


Tim

 

[pjmsimmons]

Cheers Tim.

 

[timfritsch]

Happy to help.