How to use RANGE Statment - Receiving Error 1004

Hello,

I am new to Excel VBA and I would appreciate any advice (or help) given.

In the worksheet, an user will enter information (such as Policy Number). After the user finish entering the information, a command button can be pressed to begin the process. Using my code it will check the values of the Policy Number (located in Column D) against an ADODB table (CRMDB) to see if the policy number exist and if there is a Block_No's value. If the Block_No's value is blank, then a value of "No" will be entered in a Closed Block (Y/N)? (located in Column K) on the same row as the Policy Number. If the Block_No's value equals to "1011", then a value of "Yes" will be entered in that column.

Please note that my code did work based on the selected cell for Policy Number but now I wish perform a search based on values in column D (regardless of the row) and return with answers for each row based on the policy number given into column K.

I think an array may be needed for this code along with the usage of a RANGE statement, but I am getting an error " Run-time error '1004': Method 'Range of object'_Global' failed.

Here is layout of the report (using test data):

Control Company Check Policy Check Processor Process to Verfied in Check Identification / Closed
Number Number Number Number Amount to Susp Susp Date 110008 Resolution Comments Block (Y/N)?
030000892
030000856
030000888
030000892
030000924
030001159
030001231
030001350
030001351
030001352
030001400
030001558
030001570
030001626
030001801

---------------------------------------------------------------------

Sample of my table in ODBC:

Company Policy Block_No
0090 030000856 1011
0090 030000888 1011
0090 030000892 1011
0090 030000924 1011
0090 030001159 1011
0090 030001231 1011
0090 030001350 1011
0090 030001351 1011
0090 030001352 1011
0090 030001400 1011
0090 030001558 1011
0090 030001570 1011
0090 030001626 1011
0090 030001801 1011
0090 030001872 1011
0090 030002083 1011
0090 030002450 1011

---------------------------------

Here is my code:

S

ub CBreader()

    'Establish connection and record set with CRMDB
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    'Dim strSQL As String
    
    'Declare field variables
    Dim GetItem As String
    Dim FPolicy As String
    Dim FBlock As String
    Dim FMsg As String
       
    'Activate Workbook & Worksheet
    Worksheets("EF").Activate
    FPolicy = Range("D7", Range("LastCell")).Value  <-- this where my error is occurring
    
    
    
    'Open the ODBC Connection using this statement
    
    With EF
    
    cnn.Open "CRMDB", "XXXXXX", "xxxxxxx"
    rst.ActiveConnection = cnn
    rst.CursorLocation = adUseServer
    
    rst.Source = "Select * FROM u_CloseBlock WHERE [u_CloseBlock].[Policy]= '" & FPolicy & "'"
    
    rst.Open
    
    If rst.EOF Then
      GetItem = "Not Found"
    Else
        FPolicy = rst.Fields("Policy")
        FBlock = rst.Fields("Block_No")
    End If
    
    If FBlock = "1011" Then
        FMsg = "Yes"
        Range("K7", Range("LastCell")).Value = FMsg
    ElseIf IsNull(FBlock) Then
        FMsg = "No"
        Range("K7", Range("LastCell")).Value = FMsg
        
    End If
    
    End With
       
    'Close everything and set the references to nothing
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
     
        
     
 End Sub

Sorry for being too detail, just wanted to make sure that it is understandable.

Looking forward in hearing suggestions.

Thank you.