Loop VBA

[Jameo]

Hi Guys,

I am still quite new to VBA but am learning slowly. I would just like some advice on how to proceed with the following.

I need to write a macro that will look at an array of data in a sheet. The array has several columns, but the ones I am interested in are the date, the pressure, and if there has been an agreement breach.

I would like a macro to start at the first row in a specific sheet. It would check to see if there has been a breach (Y/N). If there has been a breach, it would copy the data and pressure into a new speadsheet. If not, it will move to the next row and perform the check again.

From reading around, I am guessing that I need to use a loop statement, but am not sure how to implement it. If anyone could point me in the right direction, or give me a peice of sample code, I would greatlya appreciate it.


Many thanks in advance for the help

 

[JamesW]

Hi,

Play about with this example:

Sub test()
    Dim lRow, nRow As Long

    lRow = Range("A" & Rows.Count).End(xlUp).Row
    nRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
    For i = 1 To lRow
        If Cells(i, 1).Value = "Y" Then
            Cells(i, 2).Copy Destination:=Sheets("Sheet2").Range("A" & nRow)
            nRow = nRow + 1
        End If
    Next i
End Sub

If you need help on what things are doing in the loop, or what the variables are, then give me a shout.

 

[Jameo]

Hi James,

Many thanks for your quick response. I think I will be able to get to the point of getting the above to work for me. Saying this however, I do not know what all the variables mean.

From my limited understanding, this is what I take the code to mean:

"Dim lRow, nRow As Long"

Defines the variables lRow and nRow (I am not quite sure what "As Long" actually means).

"lRow = Range("A" & Rows.Count).End(xlUp).Row"
Sets the lRow varaiable as the range A to the bottom of the column (I assume that is what the rows.count does, counts the number of rows that are filled). Unsure about the End(xlUp).Row bit.

"nRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1"
Same as above but defines a a sheet and a range.

"For i = 1 To lRow"

I think the above:
Says for all i's between i=1 and the end of the data set (lrow)
"If Cells(i, 1).Value = "Y" Then"

If the values of cells for for row i, in column 1, equals Y then

"Cells(i, 2).Copy Destination:=Sheets("Sheet2").Range("A" & nRow)"

Copy the value for row i in column 2, to sheet 2 in column A and row nRow

"nRow = nRow + 1"
Then set nRow to nRow + 1

"Next i"

Returns the argument to "For i = 1 To lRow" where this time, i=2, and so on.

Again, thanks for the code James, I don't like not understanding things fully, so if you can clear up some of my questions without taking up too much of your time, then that will be great.

Cheers

 

[JamesW]

Hi Jameo,

I used Long because that holds more values (If you have a massive spreadsheet you could have more than 32767 rows):

Integer variables can hold values between -32,768 and 32,767, while Long variables can range from -2,147,483,648 to 2,147,483,647.

http://msdn.microsoft.com/en-us/library/aa164754%28v=office.10%29.aspx

"lRow = Range("A" & Rows.Count).End(xlUp).Row" finds the last populated row. Rows.Count will count the number of possible rows (65536 in Excel 2003) and then End(xlUp) is the same as you going to row 65536 and pressing End + Up (finds the last populated row). The reason we don't use xlDown is becasue there may be a blank somewhere in your data.

nRow is exactly the same as lRow (finds the last populated row) but the +1 means it will jump to the row after the last populated one (a blank row) - I set it to look in Sheet2 because that's where the data will be copied.

You hit the nail on the head with the rest of the code - and you say you're new to VBA?! Lies!

 

[Jameo]

Haha, thanks James. Beleive me I have surprised my self

Just a couple more questions. I think I am correct in assuming that this will run from the active sheet at the time of running the macro? If I wanted to run it from a specific sheet (which I don't, but just to play devilis advocate) I could either add:

sheets("newsheet").select

At the top of the macro, but is there any way you can define the sheet in the rest of the argument?


Also my code, using the above would be:

Sub test()
    Dim lRow, nRow As Long
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    nRow = Sheets("AgreedStats").Range("A" & Rows.Count).End(xlUp).Row + 1
    For i = 1 To lRow
        If Cells(i, 7).Value = "Y" Then
            Cells(i, 1).Copy Destination:=Sheets("AgreedStats").Range("A" & nRow)
            Cells(i, 9).Copy Destination:=Sheets("AgreedStats").Range("b" & nRow)
            nRow = nRow + 1
            
        End If
    Next i
End Sub

Where column 7 is where the Y/N is, column 1 is the date, and column 9 the pressure. Hopefully the above looks OK, as I want to copy both the date and the pressure into the AgreedStats sheet.

Finally, if I then wish to run the macro on a different sheet, do I then need to set i back to 1, or will it automatically reset at the end of the sub?

I will basically be running this macro for 17 different sheets, slightly editing the macro each time to redefine AgreedStats to a slightly different name. Probably not the most efficient way to go about it, but it will do what I need, and will be a **** site quicker than manually searching 17 sheets each with over a thousand rows of data

Sorry for the constant questions James. You have been a great help already

 

[JamesW]

Hi,

It will automatically reset i to 1 after it exits.

The macro will always run on the active sheet, always avoid using .Select, as it can slow down your code considerably. Instead use something like: Activesheet

You could loop through each sheet, like this:

    For Each sht In Sheets
        For i = 1 To lRow
            If Cells(i, 7).Value = "Y" Then
                Cells(i, 1).Copy Destination:=Sheets("AgreedStats").Range("A" & nRow)
                Cells(i, 9).Copy Destination:=Sheets("AgreedStats").Range("b" & nRow)
                nRow = nRow + 1
            End If
        Next i
    Next sht

This is assuming you want to do exactly the same thing on each sheet in your workbook.

 

[Jameo]

You've been a great help James,

Thanks again

 

[Peter_SSs]

I will basically be running this macro for 17 different sheets, slightly editing the macro each time to redefine AgreedStats to a slightly different name.

Are you saying that the relevant data from each of the 17 sheets is copied to a different sheet? So there are 17 'receiving' sheets as well as 17 'giving' sheets?

Or is the relevant data from all 17 sheets being appended in a single 'AgreedStats' sheet?

 

[Jameo]

Hi Peter,

Basically, I have 17 different sheets (may well increase to 30) that this macro will need to drag the the relevent data out of all of them. At the moment I am undecided on if there will be just one 'receiving' sheet or many. I am thinking probably just the one.

If I do decide to do just one sheet, then I will manually edit the following lines:

Cells(i, 1).Copy Destination:=Sheets("AgreedStats").Range("A" & nRow)
Cells(i, 9).Copy Destination:=Sheets("AgreedStats").Range("b" & nRow)
every time I run it, to:

Cells(i, 1).Copy Destination:=Sheets("AgreedStats").Range("C" & nRow)
Cells(i, 9).Copy Destination:=Sheets("AgreedStats").Range("D" & nRow)

and so on and so forth.

Unless you have a way to have the macro adjust the destination columns for each sht In Sheet.

Doing that myself is far beyond my level of comprehension I am afraid.

Thanks

 

[Peter_SSs]

1. Are we checking for 'breaches' in column G of all sheets in the workbook except "AgreedStats" or are there some other sheets as well in the workbook that this code will NOT run on?

2. Are there Headings in row 1 of the 17 (or more) sheets that the code has to run on?