CODEWITHSUNDEEP
vbaexcel
PointXIV
PointXIV

Reputation: 1288

Not able to get range of excel worksheet

I'm currently trying to extract certain data from a workbook to put into a different workbook. I've got the workbook to open using 

Application.GetOpenFile

, and then assigning that to a workbook. Then I assign a sheet to the active worksheet from that workbook.

My problem is coming from trying to get the range of the worksheet. I'm using an array of strings (like 

    columnLetter(0) = "A"
    columnLetter(1) = "B"

and so on to try to check through all of the columns for certain strings (which are listed in an if statement with a ton of "Or"s. The specific place of the strings varies from file to file, so my plan was to search the first row, then the second row, etc until it finds one of the strings. So, I'm using this:

    lastRow = brokerSheet.Range(columnLetter(i) & Rows.Count).End(xlUp).Row

to get the amount of rows in that column specifically. When I run the program, though, I get the error 

    Method 'Range' of object '_Worksheet' failed

on that line. I'm guessing that's because I'm trying to use 

     columnLetter(i)

,which has A through R assigned to it, instead of "A" or something like that for the column name. However, I tried using 1, 2, 3, etc to represent first column, second column, third column...but that didn't work. The worksheet only has around 90 rows (though some will have upwards of 400 once I get this working). Is there another way to do this? I could write out "A" "B" "C" etc for all of them, but there has to be a better way to designate which column to check than that.

Upvotes: 0

Views: 163

Answers (1)

nagyben
nagyben

Reputation: 938

Why not try creating a string object strRange and injecting this into Range():

strRange = columnLetter(i) & CStr(Rows.Count)
lastRow = brokerSheet.Range(strRange).End(xlUp).Row

This should work

Upvotes: 1

Related Questions