View Full Version : Help debug an R1C1 formula
chris111
09-01-2010, 06:43 AM
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
09-01-2010, 08:09 AM
How do you get col? can you supply the code that populates that variable?
chris111
09-01-2010, 09:03 AM
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
09-01-2010, 09:09 AM
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
09-01-2010, 09:17 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.