Help debug an R1C1 formula

[chris111]

Hi Guys,

I can't get this code to pass, can someone help?

Range("Q1").r1c1formula = "=500 - countblank(r3c" & col & ":r502c" & col & ")"


I'm trying to enter an R1C1 formula to calculate text entries in a series of cells that are in a column, the column number is stored in the variable "col"

Thanks

[Simon Lloyd]

How do you get col? can you supply the code that populates that variable?

[chris111]

I get it from this code, when I execute it I get col=16 before I try to execute the line of code that isn't working,

Sheets("Index History").Select
col = 0
counter = 0

'find correct index history
Do
If evaldate <= Range("B2").Offset(0, counter) Then
If evaldate >= Range("B1").Offset(0, counter) Then
col = Range("B1").Offset(0, counter).Column
Else
counter = counter + 1
End If
Else
counter = counter + 1
End If
Loop Until col <> 0

[Simon Lloyd]

Why not suppy a workbook? otherwise we'll be constantly asking questions about your code snippet, you've just introduced a new undeclared variable!

[chris111]

Just caught the error,

Range("Q1").formular1c1 = "=500 - countblank(r3c" & col & ":r502c" & col & ")"
as opposed to

Range("Q1").r1c1formula = "=500 - countblank(r3c" & col & ":r502c" & col & ")"

Whoops...

Thanks for your help