Tuesday, 12 July 2016

Excel VBA: Find string variable in range on separate sheet, then set range variable to that column

I'm trying to find a string variable A within the range Lists.Range("I2:AD2"). Once that cell is found, I'd like to set a new range variable A_backup to be the range from the cell in "I2:AD2" that was found, down to the last not empty cell in the column. I've been able to do this using select, activecell, etc., but I'd like to avoid that as it causes problems when I run it from different sheets in the workbook.



Here's the code that seems to be working:



A = OrderForm.Range("C15").Value

If Len(A) > 0 Then

Set A_backup = Lists.Range("I2:AD2").Find(A)


From here I want it to do something like:



       Set A_backup = Range(A_backup.Address, A_backup.Address.End(xlDown)
End If


Can't seem to figure that part out though. Thanks for the help!

No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...