0

I'm currently trying to learn VBA for excel and am having some trouble when it comes to wrapping my head around defining ranges. I come from a matlab background where if I want to reference a cell in a matrix, (row#,column#) is the best way to do that. It appears that in excel, this usually creates problems, as the "native" cell reference is "LetterNumber".

I'm having trouble creating code that dynamically references ranges. For example, if I have a table of data and want to select the region that spans "the first instance of X string" to "the last instance of Y string". Using some variation of the "find" command returns row#/column#.

In some places, I've been successfully able to use: ".Range(.Cells(Row#,Column#),.Cells(Row#,Column#))"

But it seems like excel dislikes this notation and it tends to cause problems depending on whether the data I'm looking for is on the active sheet, or another workbook, etc.

Am I just looking at this wrong because of my familiarity with Matlab and I need to get used to using "range("A5:B6)"? And if so, is there a good way to dynamically create that range without having a super contrived "range(columnString & number ":" columnString2 & number2" style structure? These seem extremely cumbersome to work with.

I've been trying my best at google but it seems like my question lies right on the border between "extremely basic and covered by tutorials" and "too simple to be covered by specific questions". Any links to tutorials that reference "good coding practices" for excel VBA and how to reference things consistently would be appreciated.

Some sample pseudocode of what I'm working with:

Dim allRelevantVars as allRelevantTypes  
With Sheets("sheet1")
    stringStartRow = .Range("A:A").Find(what:=targetString, after:=.Range("A1")).Column  
    stringEndRow = .Range("A:A").Find(what:=targetString, after:=.Range("A1"), searchdirection:=xlPrevious).Row  
    Set rng = Range(Cells(stringStartRow, 1), Cells(stringEndRow, 1))  
End With

At this point, if I select my range, it selects it on the active sheet. I think there are a few workarounds, including simply changing my active sheet multiple times, but again this seems cumbersome and I can't help but feel like there is a better way.

I'd rather not create code out of paper clips and rubber bands and try to learn things the way that will benefit me in the long run.

racast5
  • 1
  • For some reason, in the body of your question you have `.Range(.Cells(Row#,Column#),.Cells(Row#,Column#))"`, but then you forgot the `.` in front of the `Cells` calls in your code snippet. – BigBen May 07 '21 at 19:30
  • `Set rng = .Range(.Cells(stringStartRow, 1), .Cells(stringEndRow, 1))` When addressing ranges you always need to consider the default scope for Range()/Cells() etc https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 Also you're using `stringStartRow` but then setting that to the `Column` of the result from `Find()` – Tim Williams May 07 '21 at 21:25
  • ...and also also: `Find()` tends to remember the settings used in the previous call to Find, so it's always worth explicitly providing all of the arguments which might be important, otherwise it may not do what you expect. – Tim Williams May 07 '21 at 21:28

0 Answers0