I have a workbook with the macro in it (dim wb) and worksheet Billing (dim ws) and a named range (dim rngCust). I want the macro to evaluate the If Else statements and perform the macro if conditions are met, for each customer in rngCust
Here is a simplified range to represent wb.ws.range("rngCust") which is ColA
so the macro should identify the customer, starting at row 2 with ABC, because some customers have special conditions which require a different ElseIf statement, and if the amount is not equal to zero then perform the macro (which it should in this case because the amount is -62.00 which is not zero), so now move to the next in the list, identify the customer and if the amount is zero, move to the next in the list (so it should skip row 3 and move on to four).
Putting the loop in this is the last stage of my macro, when i run it without a loop and have a specific cell reference instead of a range to start it, it moves through the If Else statements and does everything correctly but I have to change the cell reference to run it for the next customer. With the loop, it does the first customer correctly, but after that it stops and replaces cells A3 and A4 with "TRUE"
Here is what i have for the code, since the macro is very large I have only included what I think are the relevant lines, but if you need more, I can provide it:
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Worksheets("Billing")
Dim rngBilling As range
Set rngBilling = ws.range("rngBilling") 'this range is used elsewhere in the macro and includes the subset "rngCust"
Dim rngCust As range
Set rngCust = ws.range("rngCust")
Dim cust As range
ws.Activate
ws.range("rngCust").Select ' selecting the entire range of cells to loop through
For Each cust In rngCust
If ActiveCell <> "GHI" And ActiveCell <> "MNO" And ActiveCell.Offset(0, 1) <> 0 Then
'do this macro
ElseIf ActiveCell = "GHI" And ActiveCell.Offset(0, 1) <> 0 Then
'do this macro
ElseIf ActiveCell = "MNO" And ActiveCell.Offset(0, 1) <> 0 Then
'do this macro
Else 'should apply if amount = zero, with no other code between the Else and the End If I'm expecting this to do nothing and move to the End If and continue to Next cust
End If
wb.Activate 'I have to re-activate the macro workbook because the end of the Else If macros leaves another workbook active
ws.Select
ActiveCell = ActiveCell.Offset(1, 0).Select ' to make the ActiveCell the next one in the list
Next cust
I also tried making the initial If statement: If ActiveCell = 0 Then, and making the other statements all ElseIf's, but I wasn't able to figure out how to tell it to do nothing and skip all the ElseIf's and go to the next cell in the range.
I don't get any error msgs, the code seems to run fine but it's not giving the correct results, and I think it's stopping before it gets to the end of the list, judging by the fact that it's only putting the TRUE message in rows 3 & 4.
I would appreciate any help!
Thank you.
Win10 Off 2013
Here is a simplified range to represent wb.ws.range("rngCust") which is ColA
so the macro should identify the customer, starting at row 2 with ABC, because some customers have special conditions which require a different ElseIf statement, and if the amount is not equal to zero then perform the macro (which it should in this case because the amount is -62.00 which is not zero), so now move to the next in the list, identify the customer and if the amount is zero, move to the next in the list (so it should skip row 3 and move on to four).
Putting the loop in this is the last stage of my macro, when i run it without a loop and have a specific cell reference instead of a range to start it, it moves through the If Else statements and does everything correctly but I have to change the cell reference to run it for the next customer. With the loop, it does the first customer correctly, but after that it stops and replaces cells A3 and A4 with "TRUE"
Here is what i have for the code, since the macro is very large I have only included what I think are the relevant lines, but if you need more, I can provide it:
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Worksheets("Billing")
Dim rngBilling As range
Set rngBilling = ws.range("rngBilling") 'this range is used elsewhere in the macro and includes the subset "rngCust"
Dim rngCust As range
Set rngCust = ws.range("rngCust")
Dim cust As range
ws.Activate
ws.range("rngCust").Select ' selecting the entire range of cells to loop through
For Each cust In rngCust
If ActiveCell <> "GHI" And ActiveCell <> "MNO" And ActiveCell.Offset(0, 1) <> 0 Then
'do this macro
ElseIf ActiveCell = "GHI" And ActiveCell.Offset(0, 1) <> 0 Then
'do this macro
ElseIf ActiveCell = "MNO" And ActiveCell.Offset(0, 1) <> 0 Then
'do this macro
Else 'should apply if amount = zero, with no other code between the Else and the End If I'm expecting this to do nothing and move to the End If and continue to Next cust
End If
wb.Activate 'I have to re-activate the macro workbook because the end of the Else If macros leaves another workbook active
ws.Select
ActiveCell = ActiveCell.Offset(1, 0).Select ' to make the ActiveCell the next one in the list
Next cust
I also tried making the initial If statement: If ActiveCell = 0 Then, and making the other statements all ElseIf's, but I wasn't able to figure out how to tell it to do nothing and skip all the ElseIf's and go to the next cell in the range.
I don't get any error msgs, the code seems to run fine but it's not giving the correct results, and I think it's stopping before it gets to the end of the list, judging by the fact that it's only putting the TRUE message in rows 3 & 4.
I would appreciate any help!
Thank you.
Win10 Off 2013