Named Range Manipulation

[lee2smooth03]

Hello all,

my worksheet has a named range that it constantly being resized; every week there's a new row added, etc. My goal is to traverse the array from top to bottom with checks at each row (VBA of course).

An IF statement should check to see if CONDITION is met at each row. If it is, then the row should be searched for a value. This looks and sounds an awful lot like the VLOOKUP function, but the difference is that in VLOOKUP, the user knows which column to search because it asks for an offset value. Furthermore, I'm trying to avoid the use of "APPLICATION.WORKSHEET" functions since they begin to look cumbersome.

There is a method (or property) called Rows.Find that asks for lots of inputs, but I'm not to keen on how to satisfy it's requirements. After inputting the value that I'm looking for, the rest of the inputs seem helpful, but ambiguous.

So here's what I need to know:

• How can I turn each row of a named range into an array to be traversed/searched?
• How to use the .FIND method for rows; Excel 2010's example wasn't very helpful
• How to advance the last row of my named range to the next row using array properties of the range and offset the method/property...

In the Rows.Find example, could you please include the LookIn and LookAt inputs so that I can get a sense of how to assign these values. Any help would be great; hopefully I'm not far off from any of the answers received. Below are snippets of code to demonstrate my progress (or lack there of)

    Select Case combText(theData.Cells(rowCount, 1), thisDay)    'combText is a function that compares strings
        Case -1, 1                                              'these two values indicate "No Match" 
            counter = counter + 1                               'counting the mismatches
        Case 0
            'subData was declared as an array at the beginning of the funciton
            'every time the loop is run, subset is supposed to become the row that had matching string values
            'the program doesn't seem to have a problem with subData, but it seems redundant 
            Set subData = theData.Range(theData.Cells(rowCount, 1), theData.Cells(rowCount, 1).End(xlRight))
            
            theData.Cells(rowCount, 1).Interior.Color = RGB(100, 200, 150)  'verifies the function test
            counter = theData.Columns.Count                                 'more verification
            'at this point I'd like to assign some variable to be the number of columns in theData
            'there is supposed to be a loop that traverses this row to search for the item
            theData.Rows.Find(item, after:=theData.Cells(rowCount,1),lookin:= xlValues,lookat:= xlWhole,searchorder:= xlByColumns)
            
            
    End Select

 

[lee2smooth03]

Hey,

after posting in this thread, the loop began working, but the conditional statement is not very effective. Follow me​

if you think you can help.

This thread is still very much alive; I'm still very interested in responses to help optimize this process.

Thanks!

 

[Weaver]

If you're worried about application.workheetfunction looking cumbersome, you could do the following

set fn = application.worksheetfunction

then use fn in place of the full code

mySum = fn.sum(range("A1:A100"))

for instance.

 

[lee2smooth03]

Thanks Weaver,

that's good advice that I'll put to good use in the future. In the case of using embedded worksheet functions, would I have to call the "fn" variable in front of every function called or does calling it once suffice for everything within the parentheses?

Ex:

• application.worksheet.sumif(application.worksheet.function.left("hi mom",2), h)
• application.worksheet.sumif(left("hi mom",2),h)

 

[Weaver]

In front of every one, although you could possibly use 'with'

with fn
    myResult.sumif(.left("hi mom",2), h))
end with