Move a single column into array

[macro_user]

hi...
i have a single column of values which i need to take into an array and then, i need to process the array... i have the code if its multiple cells.

lastrow = range("C65536").end(xlUp).row
ref_arr = Sheets("sheet1").range("C" & 2, "D" & lastrow).value

so, this moves values from columns C and D from the 2nd row tilll the last row in the array ref_arr..
but wat if its just a column...?
i tried this but it isn't working...

ref_arr = Sheets("sheet1").range("C4" & lastrow).value

Kindly help me out on this...

 

[pgc01]

Hi

Try, for ex.:

ref_arr = Sheets("sheet1").range("C4:C" & lastrow).value

 

[macro_user]

i tried this but im getting some error and i don't know why i'm getting tat error... basically wat im doing is, from G4 to lastrow, i have data like < 4 hours, 0-4 hours, 1 week and so on... similarly, from P4 to lastrow, i have similar data... wat i need to do is, check if the value in column P is lesser than the value(corresponding row index) at column G... if its greater, then i need to highlight both of them in Red...
i get an error saying that "Type mis-match"... i dont understand why i get this as the code looks fine for me... my code is :

lastrow = range("G65536").End(xlUp).row
arr1 = Sheets("sheet1").range("G4:G" & lastrow).value
    arr2 = Sheets("sheet1").range("P4:P" & lastrow).value
        For t = 1 To lastrow - 3
        If (get_score(arr2(t, 1)) > get_score(arr1(t, 1))) Then
            Cells(t + 3, "G").Interior.colour = vbRed
            Cells(t + 3, "P").Interior.colour = vbRed
        End If
    Next t

so, since the data is like mixed strings, i cannot compare them directly... so i write them down in another sheet in the logical order and assign a score for it... so, 0-4 hours would be 1, 4-8 hours would be 2 and so on... they are entered in columns E and F...

Function get_score(array_name As Variant) As Integer
    Dim t, lastrow As Integer
    Dim ref_arr() As Variant
    Dim excelWst4 As Excel.Worksheet
    Set excelWst4 = ThisWorkbook.Sheets("sheet2")
    
    lastrow = range("E65536").End(xlUp).row
    ref_arr = Sheets("sheet2").range("E" & 2, "F" & lastrow).value
    For t = 1 To lastrow-1
***        If (LCase(ref_arr(t, 1)) = LCase(array_name)) Then
            get_score = ref_arr(t, 2)
        End If
    Next t
End Function

i get the error "Type mis-match" pointing to *** line... kindly help me out...
lastrow is 14

 

[macro_user]

Kindly ignore the previous post.... just posted tat out of desperation... i got some other error elsewhere...

 

[macro_user]

Hi

Try, for ex.:

ref_arr = Sheets("sheet1").range("C4:C" & lastrow).value

hi... i get "Subscript out of range" error pointing to this line in my code... what could be the mistake tat i have done?

 

[Norie]

Do you have a worksheet called 'Sheet1?

 

[macro_user]

awwww....i read so many posts which say tat this might be the possible error but i was so ignorant tat i have mentioned the sheet name properly... i really apologize for this... i feel so stupid... Thanks for ur help...