Help with an array please? 1

[qjd2004]

Hiya,

I've got a Sub that goes through ranges and colors in the cells depending on the value. In the 3rd part of the Sub, I use an array to see if values exist in column "M" and if they do then colour in the corresponding cell in "N." At the moment, it's coloring in cells in a different range (I think it's starting from row 1 not row 14 which is where it should start). I'm new to using arrays so can someone help please?

Public Sub ColorMeIn()

Dim LastRow As Long
Set LastCell = Range("A1").SpecialCells(xlCellTypeLastCell)
LastRow = LastCell.Row
Dim MyRange As String
Dim r As Variant
Dim cl As Object
Dim Arr() As Variant

 
MyRange = "M14:M" & LastRow
'Set up an array in this range and search for condition, evaluation to true results in colorindex in
'corresponding cells in row "N" being set to 2 (WHITE background)
Arr() = Range(MyRange)

    For i = LBound(Arr()) To UBound(Arr())
        If Arr(i, 1) = "END USER PACK SIZE CONVERSION!" Then
            Range("N" & i).Interior.ColorIndex = 2
        End If
    Next i
    
End Sub

Can anyone suggest where I'm going wrong?

 

[SkipVought]

qjd2004,

2 comments.

First, what you seem to want to accomplish can be done using Format/Conditional Formatting.

Second, you might want to consider using the range collection object

Set MyRange = Range("M14:M" & LastRow)

    For Each rng in MyRange
      With rng
        If rng.Value = "END USER PACK SIZE CONVERSION!" Then
          Cells(rng.Row, "N")).Interior.ColorIndex = 2
        End if
      End With
    Next 
    
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[qjd2004]

Skip,

you are a star! I didn't realise I could do it this way, and it works fine now. Was I misunderstanding when to use an array and how to implement it?

Can you explain what you mean by conditional formatting please Skip? If I could understand it then I'll be able to spot better ways of working in future.

Thanks again for a really helpful reply!

 

[SkipVought]

Conditional Formatting is a spreadsheet formatting feature.

Select the cells that you want to format in this way.

menu item Format/Conditional Formatting and select Cell Value or Formula whichever is applicable and then the conditions or formula that will control the conditional format -- choose the formatting that will occur when the expression is true. Up to 3 different conditions may be entered.

A for instance might be to format columns C&D as follows (so you would select columns C & D and set interior color to BLUE before starting Conditional Formatting)
if the value in A is < 5 then interior color is RED
if the value in A is => 5 & < 50 then interior color is YELLOW
if the value in A is =>50 & <100 then interior color is GREEN
Otherwise interior color is BLUE

Other comments on your original code:
1. A Range is an Object. As an object, it is a Collection (which is like an array). Each element of the collection may be accessed, either sequentially by way of For Each ObjectElement In ObjectCollection...Next or ObjectCollection.

Since Range is an Object, do not use a variable like MyRange as a string. I rarely use range addresses/references directly as variables. I'd rather manipulate the range directly, not the string reference.

2. The only way that I can assign a Range to an array is to SELECT the range (can't do it directly for some reason that I have not yet discovered)

    Dim arr()
    ActiveSheet.UsedRange.Select
    arr = Selection
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            MsgBox arr(i, j)
        Next
    Next

The array has 2 dimensions. It makes no sense to do this since the range object can be manipulated ditectly as illustrated above.

3. Set LastCell = Range("A1").SpecialCells(xlCellTypeLastCell)
LastCell is A1
This property is used to fine the last cell in an range like

    Set lastcell = ActiveSheet.UsedRange.SpecialCells(xlLastCell)

Well, I guess that's all for now.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[qjd2004]

Thanks Skip,

I appreciate you taking the time to explain that to me, it's been really helpful!

Is it possible to use the Range collection to compare the contents of 3 columns?

Actually I wanted to look at each cell in Column C and if it is > 0 then compare it to the corresponding cell in Column A, otherwise, compare the corresponding cell in Column B to the one in A.

I've posted on that question already here
thread707-800376

Any suggestion of how to do it would be most welcome, as once I understand the logic and syntax for how to do this sort of thing, I find that I can use it in lots of other circumstances too.

Best Regards,

Q

 

[SkipVought]

with activesheet.usedrange
  r1 = .row
  r2 = r1 + .rows.count - 1
end with
for each rng in range(cells(r1, "C"), cells(r2, "C"))
  with rng
    if .value > 0 then
      if .value = cells(.row, "A").value then

      end if
    else
      if .value = cells(.row, "B").value then

      end if
    end if
  end with
next

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[qjd2004]

Skip,

You really are a star! Thanks a billion, that's really sorted me out!

I've had to make a tiny change as If the value in 'c' (which was really "AI" but it's relative to the other 2 cols) was > 0 then you were comparing C with B not B with A, but it's working a treat. Thank you so much!

With Range("AG2:AJ" & LastRow)
  r1 = .Row
  r2 = r1 + .Rows.Count - 1
End With
For Each rng In Range(Cells(r1, "AI"), Cells(r2, "AI"))
  With rng
    If .Value > 0 Then
      If .Value = Cells(.Row, "AG").Value Then
            Cells(.Row, "AK").Value = "MATCH!"
      ElseIf .Value <> Cells(.Row, "AG").Value Then
            Cells(.Row, "AK").Value = "NO MATCH!"
      End If
    Else
      If Cells(.Row, "AG").Value = Cells(.Row, "AH").Value Then
            Cells(.Row, "AK").FormulaR1C1 = "MATCH!"
      Else
            Cells(.Row, "AK").FormulaR1C1 = "NO MATCH!"
      End If
    End If
  End With
Next

My changes.

Thanks again Skip!