Excel 2010 VBA- Loop If Then Statements

Question

New to VBA but trying really hard to learn. I recently wrote a macro with IF Then Else for a woman at work. Unfortunately she is not as computer savvy and doesn't want to find the macro in the list so I figured I would use a command button user form and adjust the code to loop through the cells for her rather than her going from active cell to active cell. Sadly, I am having issues with the If Then Else in the For each loop.

Here was the original non-looping coded macro

Sub InvoicesTotalDue()

Dim myRange As Range 
Set myRange = Application.InputBox(Prompt:="Select an Invoice Type Then Hit Enter", Type:=8)
'person is on active cell and selects cell in Type column of corresponding row of active cell
If myRange.Cells.Value = "Cancel300" Then
     ActiveCell.Value = 300
ElseIf myRange.Cells.Value = "Cancel350" Then
     ActiveCell.Value = 350
Else 
     ActiveCell.FormulaR1C1 = "=Product(100,[@Hours])"
End If
End Sub

This was working but the user found it tedious and she is not used to macro usage. So I figured a userform command button that looped through the range and filled in the corresponding values in the final column would be best for her. This the way I figured it should work.

Sub InvoicesTotalDue()

Dim myRange As Range 
Set myRange = Application.Range("Invoices[Type]")

For each cCell in myRange
If myRange.Cells.Value = "Cancel300" Then
     [TotalDue].Cell.Value = 300
ElseIf myRange.Cells.Value = "Cancel350" Then
     [TotalDue].Cell.Value= 350
Else 
     [TotalDue].Cell.FormulaR1C1 = "=Product(100,[@Hours])"
End If
Next cCell
End Sub

Should calculate a total due based on type of invoice. Cancellations are set values, anything not a cancellation is the product of 100 and number of hours worked.

Can anyone spot my problem or have any suggestions/solutions?

Thanks!

Richard Morgan · Accepted Answer · 2014-01-02 18:57:58Z

3

Try:

For Each cCell in myRange.Cells
If cCell.Value = ...

Untested, but wanted to throw that out quickly.

answered Jan 2, 2014 at 18:57

Richard Morgan

7,6799 gold badges51 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Instant Breakfast Over a year ago

That does look like the likely problem.

SandPiper · Accepted Answer · 2016-08-17 14:59:00Z

It looks like you are using tables, which is great. However, I don't think you need VBA to solve this one. In your column "TotalDue", enter the following formula into the formula bar for one cell:

=IF([@Type]="Cancel300",300,IF([@Type]="Cancel350",350,100*[@Hours]))

It should automatically fill down the entire column. As much as I love VBA, sometimes a formula is a better answer!

However, I was running through your code, and your problem child is this line: myRange.Cells.Value

If myRange.Cells.Value = "Cancel300" Then

That is a bad reference. Change it to say this:

If cCell.Value = "Cancel300" Then

Another useful debugging tool I have is adding a MyNewlyDefinedRange.Select after a vba line so I know it is actually working on the range I think I told it to work on.

Collectives™ on Stack Overflow

Excel 2010 VBA- Loop If Then Statements

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related