PDA

View Full Version : Solved: Macro help: Loops



Dio
10-12-2008, 01:36 AM
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.

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

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
10-12-2008, 02:19 AM
Sub Savings()
With Range("C2")

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

Dio
10-12-2008, 02:32 AM
Thanks for the quick response. I tried that out and received the following error:

Run-time error '1004'
Reference is not valid.

Debugging it highlights the following line:


.GoalSeek Goal:=0.15, ChangingCell:=Range("B2")

Bob Phillips
10-12-2008, 02:49 AM
Do you have GoalSeek installed?

Dio
10-12-2008, 03:02 AM
No I don't, does it cost money?

I have Excel 2007 if that matters at all.

Bob Phillips
10-12-2008, 04:24 AM
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
10-12-2008, 04:36 AM
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
10-12-2008, 04:57 AM
Maybe, I have no idea what Goal Seek premium is. Is it the same error? Have you tried uninstallig it?

Dio
10-12-2008, 05:01 AM
Yep I removed it, ran Goal Seek on a couple of cells, then ran the script you wrote and same error.

Bob Phillips
10-12-2008, 06:31 AM
Post the workbook.

Dio
10-12-2008, 06:56 AM
Uploaded: Book1.xls

Bob Phillips
10-12-2008, 07:30 AM
It's a rubbish error message, but I think I have tracked it



Sub Savings()

With Range("C2")

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

mikerickson
10-12-2008, 10:33 AM
Why use Goal Seek, why not use the formula
ListPrice = RetailPrice/(1-.15)

Bob Phillips
10-12-2008, 10:56 AM
<deleted>

Dio
10-12-2008, 01:45 PM
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.

If savings =< .15
"then raise list price until condition is met"
"type it out to cell A5(list_price)"
end loop


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
10-12-2008, 02:13 PM
Try this

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

Dio
10-12-2008, 02:59 PM
Try this

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



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
10-12-2008, 03:00 PM
Yes that that sounds great, but I really need to use VBA to increment List Price for me. For example.

If savings =< .15
"then raise list price until condition is met"
"type it out to cell A5(list_price)"
end loop


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
10-12-2008, 03:08 PM
If the "savings" in column C varies from Row to Row

This will put the figures that you want in column F.
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

Dio
10-12-2008, 03:12 PM
If the "savings" in column C varies from Row to Row

This will put the figures that you want in column F.
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
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.

Bob Phillips
10-12-2008, 03:52 PM
Have you got the Goal Seek version working at all?

Dio
10-12-2008, 04:02 PM
Have you got the Goal Seek version working at all?

No for some reason it won't run. Same errors that I listed before.

Bob Phillips
10-13-2008, 12:13 AM
Even the version I posted in #12?

Dio
10-13-2008, 08:17 AM
Even the version I posted in #12?
Ok now the code runs, not sure why it is now and wasn't before. Anyway I played with it and it changes the retail price instead of the list price. That was easy enough to change.


ChangingCell:=Range("A2")


Edit: I tried the code listed in #12 before and it didn't run before now it does.

Bob Phillips
10-13-2008, 08:33 AM
Are you sure Dio? As I said eralier, it seemed odd because it didn't work for me, then I ran it in Excel, and then it did work in VBA. The reason was that when I did it in Excel was that I got a meanigful error, so I installed the formule in C2. So in post #12 i made sure that C2 had the formula, and it has worked for me flawlessly since.

Dio
10-13-2008, 11:18 AM
Are you sure Dio? As I said eralier, it seemed odd because it didn't work for me, then I ran it in Excel, and then it did work in VBA. The reason was that when I did it in Excel was that I got a meanigful error, so I installed the formule in C2. So in post #12 i made sure that C2 had the formula, and it has worked for me flawlessly since.

Yes I'm sure it works now. I'm gonna mark this solved. Thanks for all the help everyone.