VBA defining range based on value

[Homegrownandy]

There is a task I've been working on in my previous thread. Now I want to take some extra steps to input the raw data.

This is in excel but for access. feel free to tell me to go to an excel forum haha.

I will receive a list of data. What I want to do is define the range in VBA so I don't have my code doing a loop on what could potentially be a lot of rows.



for example using the text "test"... This will appear in every row in A2 to in this case A25. After this is some other meaningless text.

so rather than creating a loop that will check the row.. copy the row, paste the row, insert new line, delete.. If I can see how many rows there are somehow then I can do a block copy and paste.

I have an idea how to achieve this but advice is appreciated.

[JoeM]

Are you only looking for rows in column A that have something in it, or does it specifically have to have the word "test" in it?

If you were just looking for the last cell in column A with data in Excel, you can do that like this:
lastRow = Cells(Rows.Count,"A").End(xlUp).Row

So, you could copy column A from A2 down to this row like this:
Range("A2:A" & lastRow).Copy

[Homegrownandy]

Hi, no it has to be "test" in this case.

    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.xlsx")
    Do While filename <> ""
      Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & filename)
        
        iVal = Application.WorksheetFunction.CountIf(Range("A1:A100"), "test")
         
        'Call a subroutine here to operate on the just-opened workbook
        'Call Mymacro
          Windows("other workbook.xlsm").Activate
          Range("h1").Value = iVal
        
        filename = Dir
    Loop
  Application.ScreenUpdating = True
End Sub

this is what I'm working on at the moment. but iVal is returning a 0 value. I'm trying to figure out why.

the information I want will always be listed first (under the headers) and then at the bottom is some information that I don't need and if copied over will interfere with my other code.

BUT, that other useless info is always on three rows... so perhaps.

lastRow = Cells(Rows.Count,"A").End(xlUp).Row 
iVal = lastRow -3

Ill see what happens.

[JoeM]

Are the values you are searching in column A EXACTLY equal to the word "test", or is the word "test" mixed in with other words?
Are you sure that there aren't any extra spaces or characters in those cells containing the word "test"?

[Homegrownandy]

it will be an exact match.

using the lastRow code provided returns a value of 2 when in fact there are 28.

Very confusing.

[JoeM]

Sounds like maybe you are on the wrong sheet.
I see code opening/activating certain workbooks, but nothing specifying which sheet you should be on.

[Homegrownandy]

I think you're right.

[Homegrownandy]

Workbooks(wb).Activate

type mismatch.

Incase its not obvious I'm not that good at VBA. I'm just very persistent. lol.

would you know how I could activate it using the variable?

[JoeM]

If you open a workbook, that workbook is automatically activated, so you do not have to activate it again.
However, you want to make sure that the proper sheet is activated/selected before running your COUNTIFS.
You can get the code for many of these activities simply by turning on the Macro Recorder and recording yourself performing these actions.

When things don't quite go as planned, a good debugging tactic is to step through your code line-by-line (using the F8 key) while watching what is happening at the same time.
If you have two monitors, this is very easy. Just have your VB Editor up on one, and the Worksheet up on the other.
If you only have one monitor, I will resize my VBA Editor to about 1/4 the size of the screen, so I can watch both at the same time.

So, you can see exactly which sheet is active when your COUNTIFS command runs. That should make it much more obvious as to what is going on.

[Homegrownandy]

That's some good advice there. I've read a lot about debugging but I've never seen that so thanks.

Will help a lot actually.

there are a few problems with this. even when run independently.

the count includes blank values if they are in a table. running the code on the new workbook hangs up here:

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

It says extend selection in the bottom left.

Thanks for the help so far. I'm going to give this a break for the last hour today and pick it up again tomorrow. Its getting there!

[JoeM]

It says extend selection in the bottom left.

Not sure what you got going on there - it works for me (I actually have been using this methodology of finding the last row in column A for years!).

[Homegrownandy]

that's only on the worksheet that I am sent. In the one I have made it works fine.

No problem either way. I should be able to work it out tomorrow.

[JoeM]

I would probably need to see the sheet to try to figure out what is going on.

[Homegrownandy]

test.zip

deleted most of the info here for obvious reasons. but this is what we receive pretty much.

[JoeM]

OK. I cannot download files from my current location, but will check it out when I am on my home computer tonight.