VBA If Else Next Loop not giving correct results after the first loop

wilt823

New Member
Joined
Feb 29, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
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
1584482998976.png


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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Given:
Book1
AB
1CustomerAmount
2ABC (62.00)
3DEF -
4GHI (120.00)
5JKL -
6MNO 33.00
7PQR 12.00
Billing


Output as follows:
I1
I4
I2
I4
I3
I1

Macro rewrite:
VBA Code:
Public Sub wilt823()
  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"  Not sure what is being done but hoping coding illustrated below will assist here as well
  Dim rngCust As Range
  Set rngCust = ws.Range("rngCust") ' given from forum post coding will be adjusted based on a premise that using the named range/table will be done.
  Dim cust As Range

  'ws.Activate Not needed
  'ws.Range("rngCust").Select ' selecting the entire range of cells to loop through selection not needed

  For Each cust In rngCust.Columns(1).Cells  ' This means we are looking at a cell in each row of the FIRST COLUMN of given named range.
    If cust.Value <> "GHI" And cust.Value <> "MNO" And cust.Offset(0, 1).Value <> 0 Then
      'do this macro
      Debug.Print "I1"
    ElseIf cust.Value = "GHI" And cust.Offset(0, 1).Value <> 0 Then
      'do this macro
      Debug.Print "I2"
    ElseIf cust.Value = "MNO" And cust.Offset(0, 1).Value <> 0 Then
      'do this macro
      Debug.Print "I3"
    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
      Debug.Print "I4"
    End If
  
    ' wb.Activate ' Activation/Selection not needed 'I have to re-activate the macro workbook because the end of the Else If macros leaves another workbook active
    ' ws.Select ' Not needed
    ' Not needed with proper for each selecting the proper cells :: ActiveCell = ActiveCell.Offset(1, 0).Select ' to make the ActiveCell the next one in the list
  
  Next cust
  Set wb = Nothing
  Set ws = Nothing
  Set rngBilling = Nothing
  Set rngCust = Nothing

End Sub
 
Upvote 0
I made the changes and now it loops correctly, (oh, and thank you so much for your explanatory comments, that really helps my learning process!) however, I now have a new problem with the first part of the macro.
After the macro determines that ABC should be in the first If statement, it opens a specific template workbook to populate data from wb (the other ElseIf's have different template workbooks). After the template is opened, the first thing the macro has to do is put the customer name in C3 of the template, all of the code after that references that customer name. I was using ActiveCell for it to know what name to populate into C3. So now when it runs, it populates C3 with whatever the active cell is no matter where on the spreadsheet it is or where in the loop it is. I'm assuming it's because the loop process doesn't actually "activate" the cell currently in the loop. So that's what I need to accomplish somehow, here's the current code for the first part of the macro:


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")
Dim rngCust As range
Set rngCust = ws.range("rngCust")
Dim cust As range

Dim template As Workbook
Dim tempIMB As Workbook
Dim tempORM As Workbook

For Each cust In rngCust.Columns(1).Cells 'to look at a cell in each row of the first column of range

If cust.Value <> "GHI" And cust.Value <> "MNO" And cust.Offset(0, 1).Value <> 0 Then

'open blank template
Set template = Workbooks.Open("C:\Services\Billing\MM-YYYY TXX Invoice TEMP.xlsm")
Worksheets("Pivot Invoice").Select
'add customer ID
Dim custIDN As String
Application.ScreenUpdating = False
wb.Activate
ws.Select
Let custIDN = ActiveCell.Address
template.Activate
Sheets("Pivot Invoice").Select
range("C3").Select
ActiveCell.Formula = _
"=+'[" & wb.Name & "]Billing'!" & custIDN
Application.ScreenUpdating = True
then the macro continues on from there using C3 to populate other cells in the template for customer name, address, billing month, and then the specific rows of data in the wb file for that customer, refreshes pivots, and then it names the file to save as, prints and closes, then next in loop.

So while it's in the loop, I need it to know that it's looking at ABC right now and to put that value in C3 in the template workbook. The only ones I don't have to worry about are GHI and MNO because they have their own template so I can hard code the customer name, but the remaining 40 customers are all going to use the first template.

Any ideas how to put all of this together? I've googled and looked at other forums and I can't find this sort of scenario, and since I'm still a newbie I feel I don't have enough of a knowledge base to think of something else.

I would appreciate your further help!
Thank you
 
Upvote 0
First I'm only guessing as I have no frame of reference, and only visualize what code you provide to go by and still guessing what your intent/needs are. All that being said here is my educated guess:

VBA Code:
Option Explicit

Public Sub wilt823()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim rngBilling As Range
  Dim rngCust As Range
  Dim cust As Range
  Dim template As Workbook
  Dim tempIMB As Workbook
  Dim tempORM As Workbook
  Dim custIDN As String 
    ' Bad form so I moved all your Dims to top much easier to edit/maintain.

  Set rngBilling = ws.Range("rngBilling")
  Set wb = ThisWorkbook 
  Set ws = wb.Worksheets("Billing")
  Set rngCust = ws.Range("rngCust")

  Application.ScreenUpdating = False ' Prepare for automated changes
  For Each cust In rngCust.Columns(1).Cells 'to look at each cell in every row of the columns(1) {first column} of named range
    If cust.Value <> "GHI" And cust.Value <> "MNO" And cust.Offset(0, 1).Value <> 0 Then  ' cust.Value is the String you are looking to utilize
      'open blank template
      Set template = Workbooks.Open("C:\Services\Billing\MM-YYYY TXX Invoice TEMP.xlsm")
      'STOP selecting :) Worksheets("Pivot Invoice").Select
      'add customer ID
      'wb.Activate
      'ws.Select
      Let custIDN = cust.Address
      'template.Activate
      'Sheets("Pivot Invoice").Select
      template.Sheets("Pivot Invoice").Range("C3").Value = cust.Value
      cust.Formula = _
        "=+'[" & wb.Name & "]Billing'!" & custIDN ' Assuming you're trying to utilize the address of the current customer (cust) and set a reference to it once set in the template.
    End If  'Either thisor else or elseif
      'ASSUMING OTHER CONDITIONS HERE SOMEWHERE
  Next cust ' Continue with the next customer in the custRng for each loop
  Application.ScreenUpdating = True
  'some other code assumed below.
End Sub
 
Upvote 0
I just finished implementing this update and cleaning up some of my extra lines of code with all those "Select"s, and it is working perfectly! Thank you so much for all your help!!
 
Upvote 0
Welcome glad to help! Thank you for updating us that all is working! :)
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top