Syntax help for With Activesheet

[GreenyMcDuff]

Hi All,

I have written the following code:

Dim i As Long, LastRow As Long
Dim TaskCol As Range, LucyCol As Range
Dim beforeArray As Variant

With ActiveSheet
    
    Set TestCol = .Range("1:1").Find("Task Type", LookAt:=xlWhole)
    Set LucyCol = .Range("1:1").Find("Task Type Lucy", LookAt:=xlWhole)
   
    [COLOR="Red"]LastRow = .Range(TestCol & Rows.Count).End(xlUp).Row[/COLOR]

But keep getting an "application-defined or object-defined error".

I'm not entirely sure what this means, but I am pretty sure the syntax is incorrect.

Would appreciate your expert knowledge

many thanks

Chris

 

[VoG]

Try

 LastRow = .Cells(Rows.Count, TestCol.Column).End(xlUp).Row

 

[GreenyMcDuff]

Much appreciate Peter,

Thanks

 

[GreenyMcDuff]

Hi Peter,

One stage further on from my last question:

The code now reads:

Dim i As Long, a As Long, LastRow As Long
Dim TaskCol As Range, LucyCol As Range
Dim beforeArray() As Variant

beforeArray = Array("Assignment Pending", "Canadian Bond Price", "Evaluation Challenge", "Unchanged Evaluation", "Vendor Comparison", "CMO", "Corporate Action/Xcitek Notice", "Descriptive Data Challenge - Call Schedule", "Descriptive Data Challenge - Called", "Descriptive Data Challenge - Conversion Features", "Descriptive Data Challenge - Coupon Rate / Type", "Descriptive Data Challenge - Industrial Classification Codes / SIC Codes / NAICS / GICS", "Descriptive Data Challenge - Interest Accrual Basis / Frequency", "Descriptive Data Challenge - Issue Status / Record Type", "Descriptive Data Challenge - Maturity date / Redemption date", "Descriptive Data Challenge - Miscellaneous / Other", "Descriptive Data Challenge - Name Challenge", "Descriptive Data Challenge - Nominal / Par Value", "Descriptive Data Challenge - Ratings", "Descriptive Data Challenge - Security Type / Asset Class", "Descriptive Data Challenge - Shares Outstanding / Issued Capital", "Descriptive Data Challenge - Skeletal", _
"Descriptive Data Challenge - Ticker / Local Code", "Dividend / Interest", "EUSD", "Indicated Annual Dividend", "Indicated Annual Dividend / Forecast Dividend", "SBA", "Xcitek.com", "Fund Manager Line", "ICMA and Broker Quote Price", "Index Price", "Listed Markets Price Challenge", "Commodities and Futures Price", "NQB Price", "Price/Evaluation Request", "FTEX Code Request", "MEXID Request", "SEDOL Request")

With ActiveSheet
    
    Set TaskCol = .Range("1:1").Find("Task Type", LookAt:=xlWhole)
    Set LucyCol = .Range("1:1").Find("Task Type Lucy", LookAt:=xlWhole)
   
    LastRow = .Cells(Rows.Count, TaskCol.Column).End(xlUp).Row
    
        For i = 2 To LastRow
    
                For a = 0 To 4
                    [COLOR="Red"]If .Range(TaskCol & i) = beforeArray(a) Then[/COLOR]
                       .Range(LucyCol & i).Formula = "Bond Evaluation Challenge" 
                    End If
                Next a

I am getting the same error on the red line. I have tried changing it to a cell reference

If .Cells(i, TaskCol.Column) = beforeArray(a) Then

but that didn't help

Thanks

Chris

 

[VoG]

That should work. Perhaps

If .Cells(i, TaskCol.Column).Value = beforeArray(a) Then

 

[GreenyMcDuff]

Lol I literally just spotted that. But thanks anyway Peter.

Cheers

Chris