Results 1 to 7 of 7

Move a single column into array

This is a discussion on Move a single column into array within the Excel Questions forums, part of the Question Forums category; hi... i have a single column of values which i need to take into an array and then, i need ...

  1. #1
    Board Regular
    Join Date
    Mar 2009
    Posts
    101

    Default Move a single column into array

    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.
    Code:
    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...
    Code:
    ref_arr = Sheets("sheet1").range("C4" & lastrow).value
    Kindly help me out on this...

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,009

    Default Re: Move a single column into array

    Hi

    Try, for ex.:

    Code:
    ref_arr = Sheets("sheet1").range("C4:C" & lastrow).value
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    Board Regular
    Join Date
    Mar 2009
    Posts
    101

    Default Re: Move a single column into array

    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 :
    Code:
    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...
    Code:
    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

  4. #4
    Board Regular
    Join Date
    Mar 2009
    Posts
    101

    Default Re: Move a single column into array

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

  5. #5
    Board Regular
    Join Date
    Mar 2009
    Posts
    101

    Default Re: Move a single column into array

    Quote Originally Posted by pgc01 View Post
    Hi

    Try, for ex.:

    Code:
    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?

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    64,939

    Default Re: Move a single column into array

    Do you have a worksheet called 'Sheet1?
    If posting code please use code tags.

  7. #7
    Board Regular
    Join Date
    Mar 2009
    Posts
    101

    Default Re: Move a single column into array

    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...

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com