Move a single column into array

macro_user

Board Regular
Joined
Mar 9, 2009
Messages
101
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...
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

Try, for ex.:

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

macro_user

Board Regular
Joined
Mar 9, 2009
Messages
101
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
 

macro_user

Board Regular
Joined
Mar 9, 2009
Messages
101
Kindly ignore the previous post.... just posted tat out of desperation... i got some other error elsewhere...
 

macro_user

Board Regular
Joined
Mar 9, 2009
Messages
101

ADVERTISEMENT

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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Do you have a worksheet called 'Sheet1?
 

macro_user

Board Regular
Joined
Mar 9, 2009
Messages
101
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...
 

Forum statistics

Threads
1,136,792
Messages
5,677,753
Members
419,718
Latest member
ALWP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top