Solved: Macro help: Loops

[Dio]

Hello,

I have to write a macro for work that will check to see if a customer is saving at least 15% or more over our price. Using Excel I write a formula that does this:

=1-((retail price) / (list price)) then format that cell as a percentage.

I then alter the list price until the percentage goes up to 15%. I do this for rows and rows of products.

I'm just getting started on the script and to get familiar with VBA I made the following macro and it works for calculating the savings, but doesn't alter the list price for me.
[vba]
Option Explicit
Sub Savings()

Dim Savings As Single
Dim map_price As Single
Dim list_price As Single
map_price = Range("B2").Value
list_price = Range("A2").Value
Savings = 1 - (map_price / list_price)
Range("C2").Select
ActiveCell.FormulaR1C1 = Savings
Selection.NumberFormat = "0%"

End Sub
[/vba]
I found an example of a Do Until loop and thought I would use that to increment the list price until the condition of map_price + 15% is met, but I couldn't figure it out. If you want I can paste the code for the Do Until loop, but it's just botched at this point.

Question 1: What type of loop, if any, do I need to adjust the list price?

Question 2: Can you give me an example of how to use my existing code in a loop?

Thanks,

Dio

[Bob Phillips]

[vba]

Sub Savings()
With Range("C2")

.GoalSeek Goal:=0.15, ChangingCell:=Range("B2")
.NumberFormat = "0%"
End With
End Sub
[/vba]

[Dio]

Thanks for the quick response. I tried that out and received the following error:
[vba]
Run-time error '1004'
Reference is not valid.
[/vba]
Debugging it highlights the following line:

[vba]
.GoalSeek Goal:=0.15, ChangingCell:=Range("B2")
[/vba]

[Bob Phillips]

Do you have GoalSeek installed?

[Dio]

No I don't, does it cost money?

I have Excel 2007 if that matters at all.

[Bob Phillips]

It should be there, on the Data tab, Data Tools group, within the What If Analysis dropdown.

It is very odd, I got the same error at first. I then ran the GoalSeek manually, and then the macro worked!

[Dio]

Ok I tried that ran Goal Seek on a couple of cells, then ran the macro. Still nothing. I did install Goal Seek Premium and it shows up in the Add-Ins section. I didn't know Goal Seek was already installed on it. Maybe the add-in is giving me the problem?

[Bob Phillips]

Maybe, I have no idea what Goal Seek premium is. Is it the same error? Have you tried uninstallig it?

[Dio]

Yep I removed it, ran Goal Seek on a couple of cells, then ran the script you wrote and same error.

[Bob Phillips]

Post the workbook.

[Dio]

Uploaded: Book1.xls

[Bob Phillips]

It's a rubbish error message, but I think I have tracked it

[vba]

Sub Savings()

With Range("C2")

.FormulaR1C1 = "=1-(RC[-1]/RC[-2])"
.GoalSeek Goal:=0.15, ChangingCell:=Range("B2")
.NumberFormat = "0%"
End With
End Sub
[/vba]

[mikerickson]

Why use Goal Seek, why not use the formula
ListPrice = RetailPrice/(1-.15)

[Bob Phillips]

<deleted>

[Dio]

Why use Goal Seek, why not use the formula
ListPrice = RetailPrice/(1-.15)

Yes that that sounds great, but I really need to use VBA to increment List Price for me. For example.
[vba]
If savings =< .15
"then raise list price until condition is met"
"type it out to cell A5(list_price)"
end loop
[/vba]

Would be nice to have a way of automatically changing the list price in multiple cells as that is the ultimate goal here. So basically in the end my macro would:

A: verify that the customer is saving 15%
B: If not saving 15% increase the List Price until it is.
C: Keep going through all the products and stop at the last row.

I know it's a lot but it's really necessary to get all my work done. Any help is appreciated.

Thanks in advance,

Mike

Or is there some easier way to do this?

[mdmackillop]

Try this
[vba]
Sub Saving()
Dim Rng As Range, cel As Range
Set Rng = Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp))
For Each cel In Rng
If cel < 0.15 Then
cel.Offset(, -2) = cel.Offset(, -1) / 0.85
End If
Next
End Sub

[/vba]

[Dio]

Try this
[vba]
Sub Saving()
Dim Rng As Range, cel As Range
Set Rng = Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp))
For Each cel In Rng
If cel < 0.15 Then
cel.Offset(, -2) = cel.Offset(, -1) / 0.85
End If
Next
End Sub

[/vba]

Yes I've tested that out and in that spreadsheet it works fine. Now I just need to incorporate it into the actual spreadsheet I use for work. The one I use at work has much more data in it and the Map Price/List Price are in different columns.

1: How can I make this work with my existing uploaded spreadsheet(I can't seem to figure out how to alter the macro correctly)

2: How can this macro be run without it being dependent on the saving column. The one you uploaded had a saving column formatted as a percent and I can't have it on the official spreadsheet. The reason why is because we are saving the spreadsheets as a CSV file for import to a website. And any additional categories would be saved on the CSV file as well. I know my uploaded spreadsheet had a savings column but that was just for reference. Sorry about any confusion it caused.

Thanks for all the help so far it's been great coming here and getting help.

-Dio

[Bob Phillips]

Yes that that sounds great, but I really need to use VBA to increment List Price for me. For example.
[vba]
If savings =< .15
"then raise list price until condition is met"
"type it out to cell A5(list_price)"
end loop
[/vba]

Would be nice to have a way of automatically changing the list price in multiple cells as that is the ultimate goal here. So basically in the end my macro would:

A: verify that the customer is saving 15%
B: If not saving 15% increase the List Price until it is.
C: Keep going through all the products and stop at the last row.

I know it's a lot but it's really necessary to get all my work done. Any help is appreciated.

Thanks in advance,

Mike

Or is there some easier way to do this?

The problem is trying to work out what increment to modify the variable value by so that you can zero in on your target result, do you increment by 1, .1, .01, .001, etc. You can take a crude approach, and be satisfied when the answer approximates to 15%, or you need to be much smarter to get 15%.

This is exactly what Goal Seek does, s why re-invent the wheel?

[mikerickson]

If the "savings" in column C varies from Row to Row

This will put the figures that you want in column F.
[VBA]With ThisWorkbook.Sheets(1).Range("A:A")
With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
With .Offset(1, 0).Resize(.Rows.Count - 1, 1)
.Offset(0, 5).FormulaR1C1 = "=MAX(rc1,round(rc2/(1-rc3),0))"
End With
End With
End With[/VBA]

[Dio]

If the "savings" in column C varies from Row to Row

This will put the figures that you want in column F.
[vba]With ThisWorkbook.Sheets(1).Range("A:A")
With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
With .Offset(1, 0).Resize(.Rows.Count - 1, 1)
.Offset(0, 5).FormulaR1C1 = "=MAX(rc1,round(rc2/(1-rc3),0))"
End With
End With
End With[/vba]

I don't actually want the savings column at all, only want to change the list price. At first I had a savings column so I could see that the macro was changing the List price correctly.

Edit: I can't have a savings column at all because I'm saving these excel files to a CSV file and then importing them to a website catalog.