Assistance with Excel macros

Closed
xOgrex Posts 1 Registration date Friday December 19, 2014 Status Member Last seen December 19, 2014 - Dec 19, 2014 at 08:09 AM
 RayH - Dec 19, 2014 at 02:27 PM
Greetings all

My company uses Excel to track inspections and maintenance of portable fire extinguishers and I'm trying to add some functionality to it but don't have the first clue about how to do it. I'll do my best to explain what I need assistance with:

All three sheets will have the same column headings in row 1. On the first and second sheets in the workbook (titled "Monthly" and "Annual" respectively), I am using conditional formatting to color cells in columns Y and AC based upon there values as determined by a formula (Yellow if the calculation returns a value less than 90, Red if the value returned is 0 or a negative number). What I would like a macro to do is to copy the entire row from column A through column AD to a third sheet (titled "Maint Due"). It would also be nice if this process were automated so that anytime the values in columns Y and AC changed in the "Monthly" or "Annual" sheets, the information in the "Maint Due" sheet was automatically updated (but if I have to rerun the macro manually for that to happen it's not a big deal).

Hopefully I've explained what I'm trying to do in a way that it makes sense to someone and they can offer help. I have very little coding experience and it's been 15 years since I've really done any coding so I'm very rusty. I appreciate any advice or assistance folks may be able to give.

I've never used the Macro recorder and I can only figure out how to create a macro to copy and paste, so I had no luck there. After doing some more searching and watching some videos I cobbled this together:

Sub Show_on_Maint()
x = 2
'Sets the starting row
Do While Cells(x, 2) <> ""
'Continue to evaluate until a blank cell is reached
If Cells(x, 25) <= 90 Then
'Evaluates the cell in column Y to determine if the value is less than or equal to 90
Sheets("Sheet1").Rows(x).Copy Sheets("Sheet6").Range("A2")
'Copies the row to the Maint Due sheet
Else
If Cells(x, 29) <= 90 Then
'Evaluates the cell in column AC to determine if the value is less than or equal to 90
Sheets("Sheet1").Rows(x).Copy Sheets("Sheet6").Range("A2")
'Copies the row to the Maint Due sheet
End If
x = x + 1
Loop
End Sub

When I run/debug it I get a Loop without Do error. I think my logic is sound but I don't have enough experience to figure out why I'm getting that error.

1 reply

You were missing an "ENDIF" .


The way this is set up it means that the 2nd IF THEN is ONLY evaluated if the 1st one does not meet the criteria.

Essentially it is doing this.



Sub Show_on_Maint()
x = 2
'Sets the starting row

Do While Cells(x, 2) <> ""
'Continue to evaluate until a blank cell is reached

'Evaluates the cell in column Y or AC to determine if the value is less than or equal to 90
If Cells(x, 25) <= 90 Or Cells(x, 29) <= 90 Then

'Copies the row to the Maint Due sheet
Sheets("Sheet1").Rows(x).Copy Sheets("Sheet6").Range("A2")

End If
x = x + 1

Loop

End Sub
0