Column reference

[tomleitch]

I have the following code that I'm using to try and count colour fill of cells in a specified range on another worksheet, and I can't get it to quite work how I want.

The code that I can get working how I want is this:

twowk = Sheets("PROD").Range("2:2").Find(What:="2WK GATE DATE", lookat:=xlWhole, MatchCase:=False).Columnsixwk = Sheets("PROD").Range("2:2").Find(What:="6WK GATE DATE", lookat:=xlWhole, MatchCase:=False).Column
twelvewk = Sheets("PROD").Range("2:2").Find(What:="12WK GATE DATE", lookat:=xlWhole, MatchCase:=False).Column




Lrow = Sheets("PROD").Cells(Rows.Count, 1).End(xlUp).Row


  For nRowIndex = 3 To Lrow
  
    If Sheets("PROD").Range("M" & nRowIndex).Interior.ColorIndex = 3 Then 'red
      nCellNumber = nCellNumber + 1
    End If
    
    If Sheets("PROD").Range("M" & nRowIndex).Interior.ColorIndex = 46 Then 'orange
      nCellNumber2 = nCellNumber2 + 1
    End If
    
  Next nRowIndex

But instead of using column reference "M" in the range, I want to use twelvewk column identifier like this

twowk = Sheets("PROD").Range("2:2").Find(What:="2WK GATE DATE", lookat:=xlWhole, MatchCase:=False).Columnsixwk = Sheets("PROD").Range("2:2").Find(What:="6WK GATE DATE", lookat:=xlWhole, MatchCase:=False).Column
twelvewk = Sheets("PROD").Range("2:2").Find(What:="12WK GATE DATE", lookat:=xlWhole, MatchCase:=False).Column




Lrow = Sheets("PROD").Cells(Rows.Count, 1).End(xlUp).Row


  For nRowIndex = 3 To Lrow
  
    If Sheets("PROD").Range(twelvewk & nRowIndex).Interior.ColorIndex = 3 Then 'red
      nCellNumber = nCellNumber + 1
    End If
    
    If Sheets("PROD").Range(twelvewk & nRowIndex).Interior.ColorIndex = 46 Then 'orange
      nCellNumber2 = nCellNumber2 + 1
    End If
    
  Next nRowIndex

I can't get it working like this at all!.... it's probably something obvious - can anyone help?


Many thanks
Tom

 

[Fluff]

Re: Help with column reference

Try

If Sheets("PROD").Cells(nRowIndex,twelvewk).Interior.ColorIndex = 3 Then 'red

 

[JoeMo]

Re: Help with column reference

Can you explain, in detail, what "can't get it working" means? Do you get an error message? If so, on what line and what is the message?

Just glancing at your code, I suspect one of the issues is that you are trying to use twelvewk as a column letter when it is actually a column number. Instead of using:
Range(twelvewk & nRowIndex), try Cells(nRowIndex,twelvewk). There may be other issues as well.

 

[tomleitch]

Re: Help with column reference

Many thanks to you both - what you've said has done the trick.

Much appreciated
Tom

 

[Fluff]

Re: Help with column reference

Glad we could help & thanks for the feedback

 

[tomleitch]

Re: Help with column reference

Glad we could help & thanks for the feedback

I'm having a similar problem with a very similar thing, any chance you could give me a pointer?

I'm trying to copy a range but it's not working.

Worksheets("PROD").Cells(3, twelvewk, Sheets("PROD").Cells(Lrow, twelvewk)).Copy

Thanks
Tom

 

[Fluff]

Re: Help with column reference

Try

With Worksheets("PROD")
   .Range(.Cells(3, twelvewk), .Cells(lrow, twelvewk)).Copy
End With

 

[tomleitch]

Re: Help with column reference

Try

With Worksheets("PROD")
   .Range(.Cells(3, twelvewk), .Cells(lrow, twelvewk)).Copy
End With

Fluff, really can't thank you enough for this and all your other help.

 

[Fluff]

Re: Help with column reference

You're welcome & thanks for the feedback