Problem to loop columns with array

[elmnas]

Hello people,

I am trying to create an array that each string in my array presents a column,

I want to loop each used cell in in the column then go to next string in the array(next column)



here is my code so far

Sub Test()


Dim i As Long
Dim ArrCol() As Variant
ArrCol = Array("T", "U", "V")


For i = LBound(ArrCol) To UBound(ArrCol)
Dim mystring As Variant
mystring = ArrCol(i)


'I need to declare the column and cell here but I dont sucess...


MsgBox mystring


Next


End Sub

Could someone help me?

 

[FormR]

Hi - here is one possible option, for example.

Sub Test()
Dim i As Long, c As Range, ArrCol() As Variant
ArrCol = Array("T", "U", "V")
For i = LBound(ArrCol) To UBound(ArrCol)
    For Each c In Range(ArrCol(i) & "1:" & ArrCol(i) & Range(ArrCol(i) & Rows.Count).End(xlUp).Row)
        Debug.Print c.Value 'for example
    Next c
Next i
End Sub

 

[elmnas]

Hi - here is one possible option, for example.

Sub Test()
Dim i As Long, c As Range, ArrCol() As Variant
ArrCol = Array("T", "U", "V")
For i = LBound(ArrCol) To UBound(ArrCol)
    For Each c In Range(ArrCol(i) & "1:" & ArrCol(i) & Range(ArrCol(i) & Rows.Count).End(xlUp).Row)
        Debug.Print c.Value 'for example
    Next c
Next i
End Sub

Thank you work as a charm I will look on it in more detail so I get more understanding for it

Cheers!

 

[elmnas]

How do I select a cell that is offset same row but in Column D? when I got C.value?

I know I can use

    c.Select
    
     ActiveCell.Offset(0, -3).Select
    MsgBox ActiveCell.Offset(0, -3).Value

But that doesn't help me because the value dont gonna be the same when its looping in column "U", "V" etc.
then I have to increase the value by -1 cause its one more step away


Thank you in advance

 

[FormR]

How do I select a cell that is offset same row but in Column D? when I got C.value?

Something like this maybe..

Debug.Print Range("D" & c.Row).Value 'for example

 

[elmnas]

Something like this maybe..

Debug.Print Range("D" & c.Row).Value 'for example

work as a charm thank you!
Another question the code looping from row 1 to last used cell
but how do I make the code loop from row 2 to last used cell instead?

 

[FormR]

how do I make the code loop from row 2 to last used cell instead?

You could try:

    For Each c In Range(ArrCol(i) & "2:" & ArrCol(i) & Range(ArrCol(i) & Rows.Count).End(xlUp).Row)

 

[elmnas]

You could try:

    For Each c In Range(ArrCol(i) & "2:" & ArrCol(i) & Range(ArrCol(i) & Rows.Count).End(xlUp).Row)

Work as charm. !!

 

[elmnas]

I still facing a problem,

here is my code at the moment,

I need to color the cell (C.value) if the value is lower or equal than the value in column "D" same row then color it green.
if the value in (C.value) its more than the value in column D same row color it red.

but it doesnt work why?

Have I missed anything?

see my code

Sub MyCelCounter()




Dim i As Long, c As Range, ArrCol() As Variant
ArrCol = Array("K")


    For i = LBound(ArrCol) To UBound(ArrCol)
        For Each c In Range(ArrCol(i) & "2:" & ArrCol(i) & Range(ArrCol(i) & Rows.Count).End(xlUp).Row)
         
       
       
        myColDV = Range("D" & c.Row).Value
       MyColv = Len(c.Value)
                  
                  
             ' MsgBox "This is value in column D:" & myColDV & "This is value in active Loop column:" & MyColv
              
              

              If myColDV > MyColv Then
              c.Interior.ColorIndex = 4
              End If
              
                 
     
                
        Next c
    Next i
    
End Sub

here is another code I made here do the loops work fine but I have hardcoded to column "K"

Sub MyColCounter()

Dim i As Long
Dim columnD As String
    For i = 2 To ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row
    Dim CharCount As Integer

    CharCount = Len(Cells.Item(i, "K").Value)
    'MsgBox DCount & " " & CharCount
        If (Cells.Item(i, "D").Value > CharCount) Then
        Cells.Item(i, "K").Interior.ColorIndex = 4
        'MsgBox Cells.Item(i, "D").Value
            Else
            Cells.Item(i, "K").Interior.ColorIndex = 3
            End If
            If (Cells(i, 4).Value) <> "" Then
                If Cells(i, 4).Value = "NONE" Then
                'MsgBox Cells(i, 4).Value
                Cells(i, "K").Interior.ColorIndex = 4
            End If
        End If
    Next i
    
End Sub

I dont understand this works here but not the other one.

 

[FormR]

I need to color the cell (C.value) if the value is lower or equal than the value in column "D" same row then color it green.
if the value in (C.value) its more than the value in column D same row color it red.

Is there a reason why you're not using conditional formatting?