Using row numbers to create a range to be copied?

[passinthru]

Hi, folks. I'm working with some diagnostic messages produced by a communications network, and trying to isolate a certain kind of message. There are many different types of messages contained in the error logs, and I need to look at different ones at different times. As a single log may contain thousands of messages, I'm trying to use VBA to grab the ones I want. The easy part is that these logs can be exported as CSV files. Yay!

Each message contains an identifier which I can use for finding the correct messages of a given type. (I'm only looking for one type at a time.) However, this identifier is not on the first line of the message; it's buried a few lines down, and the number of lines is variable. Additionally, the length of the message is variable. The good news is, all messages of interest start with the same text string, and all of them end with another consistent text string. It looks something like this:

Message Start
blah
blah
Message Type
blah
blah
Message End

(Where "blah" is useful information that varies, and the number of lines of blah varies.)

The Message Start string is constant across all message types, as is the Message End string, so I can't use them to find the correct Message Type. However, they are still useful for marking the start and end of each message.

I'm using the find function to find the correct message type, then another find (going up) to get the start of the message, and putting the row number into a variable (msgStartRow). Then I find again for the end of message (msgEndRow).

That all works just fine.

What I can't figure out is how to turn those message row numbers into a range that can then be copied and pasted onto a separate worksheet.

How can I use these variables to copy the correct range? For example, this is what I recorded manually, but it's not intelligent. It can't accommodate different message lengths:

    ActiveCell.Offset(6, 0).Range("A1:A145").Select
    Selection.Copy

Or is there a better approach you can suggest?

Thanks!

 

[stemar]

Can you get the message text into a string?

If so, you could do something like

if strMessage like "*Message Type1*" then DoStuffForType1
if strMessage like "*Message Type2*" then DoStuffForType2
etc

It isn't very elegant, but should get you started

 

[mumps]

I'm not sure if I understood you correctly. The following line of code uses the Message Start row number and Message End row number to define a range that you can copy. This code assumes that your messages are in column A.

Range(Cells(MessageStart.Row, "A"), Cells(MessageEnd.Row, "A")).Copy

 

[passinthru]

I'm not sure if I understood you correctly. The following line of code uses the Message Start row number and Message End row number to define a range that you can copy. This code assumes that your messages are in column A.

Range(Cells(MessageStart.Row, "A"), Cells(MessageEnd.Row, "A")).Copy

Thank you, mumps! I think that's exactly what I'm looking for.

I'll try it out and report back!

 

[passinthru]

No joy. It chokes with an "Invalid Qualifier" compile error, highlighting the "msgStart" part of the expression.

I've declared the variables at the start of my macro like this:

Dim LastRow As Long, CurrentRow As Long, msgStart As Long, msgEnd As Long

And my data is in column F, so my selection code looks like this:

Range(Cells(msgStart.Row, "F"), Cells(msgEnd.Row, "F")).Copy

Looks like the ".Row" might have been the problem. When I tried again with this:

Range(Cells(msgStart, "F"), Cells(msgEnd, "F")).Copy

I got no compile error. Instead, I got a runtime error elsewhere!

So, mumps, I think you got me pointed in the right direction. Thanks!

 

[mumps]

My pleasure.

 

[passinthru]

Confirmed! That part works perfectly.




Now to figure out my looping problem....