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?

 

[elmnas]

Yes my boss want instead a macro..

Do you understand why my loop doesnt work? in the first of the two codes I sent you

 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

thank you in advance

 

[FormR]

Have I missed anything?

You appear to be comparing the value in D to the length of the value in column K. Aside from trying CF - you could try:

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)
        If c.Value < Range("D" & c.Row).Value Then c.Interior.Color = vbGreen
        If c.Value > Range("D" & c.Row).Value Then c.Interior.Color = vbRed
    Next c
Next i
End Sub

 

[elmnas]

You appear to be comparing the value in D to the length of the value in column K. Aside from trying CF - you could try:

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)
        If c.Value < Range("D" & c.Row).Value Then c.Interior.Color = vbGreen
        If c.Value > Range("D" & c.Row).Value Then c.Interior.Color = vbRed
    Next c
Next i
End Sub

the strange is the code runs fun but I get wrong result..

Here is a link for my file
http://www.filedropper.com/newtextsinascommycotobetranslatedandreviewed2015-07-10_1

I did named my working function "oldworking" but it just loops the "K" column.

and your function you made its called test


I would like to have the same result as the one in oldworking but for an array of columns.


Thank you

 

[FormR]

There has been a fair bit of mission creep with this
One of the issues you have been experiencing is that the numbers in column D are "text stored as numbers" and not true numerical values. This makes the whole thing a little more complicated than it should be.
That said, try this (which can be simplified if you are able to correct your column D values)

Sub m1()
Dim i As Long, j As Long, arrCol()
arrCol = Array("K")
For i = 2 To Range("D" & Rows.Count).End(xlUp).Row
    For j = 0 To UBound(arrCol)
        If Cells(i, "D").Value = "NONE" Then
            Cells(i, arrCol(j)).Interior.ColorIndex = 4
                Else
            If IsNumeric(Cells(i, "D").Value) Then
                If CLng(Cells(i, "D").Value) > Len(Cells(i, arrCol(j)).Value) Then
                    Cells(i, arrCol(j)).Interior.ColorIndex = 4
                        Else
                    Cells(i, arrCol(j)).Interior.ColorIndex = 3
                End If
            End If
        End If
    Next j
Next i
End Sub

 

[elmnas]

There has been a fair bit of mission creep with this
One of the issues you have been experiencing is that the numbers in column D are "text stored as numbers" and not true numerical values. This makes the whole thing a little more complicated than it should be.
That said, try this (which can be simplified if you are able to correct your column D values)

Sub m1()
Dim i As Long, j As Long, arrCol()
arrCol = Array("K")
For i = 2 To Range("D" & Rows.Count).End(xlUp).Row
    For j = 0 To UBound(arrCol)
        If Cells(i, "D").Value = "NONE" Then
            Cells(i, arrCol(j)).Interior.ColorIndex = 4
                Else
            If IsNumeric(Cells(i, "D").Value) Then
                If CLng(Cells(i, "D").Value) > Len(Cells(i, arrCol(j)).Value) Then
                    Cells(i, arrCol(j)).Interior.ColorIndex = 4
                        Else
                    Cells(i, arrCol(j)).Interior.ColorIndex = 3
                End If
            End If
        End If
    Next j
Next i
End Sub

Work as a charm finally!

Thank you so much!

I do really appreciate this 5 stars to you!