Fairly unique copy/paste IF question for VBA

S

ShNBl84

New Member
Joined
May 22, 2017
Messages
6
Excel Superheros,

I have a conundrum and need a hero.

I am trying to create a VBA code that 1) detects the first empty cell in a row then 2) reads the value of the cell several rows down (Apple price score cell), 3) copies the value of a cell on a different row (price cell) and pastes it the number of times down a row that was in the score cell.
For example, if an apple price has a score of 4 (row 9), the guidance is to purchase apples at that price for 2 months (row 2). After that contract, we contract the next # of months based on the purchase guidance based on how expensive apples are that month. If they are cheaper, usually we contract the price to buy them at that price for more months. Below is an example: since apples were a little expensive in Jan 2016 (score of 4), guidance was to contract that price for two months (see "If score" is 4, "Then buy this many months" is 2). After those two months, we need to buy again in March. The price score was 2 so we bought for 4 months at that price. Et cetera.

[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD]If score[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Then buy this many months[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/16[/TD]
[TD]2/16[/TD]
[TD]3/16[/TD]
[TD]4/16[/TD]
[TD]5/16[/TD]
[TD]6/16[/TD]
[TD]7/16[/TD]
[TD]8/16[/TD]
[TD]9/16[/TD]
[TD]10/16[/TD]
[TD]11/16[/TD]
[TD]12/16[/TD]
[/TR]
[TR]
[TD]Apple price[/TD]
[TD]$1.40[/TD]
[TD]$1.20[/TD]
[TD]$1.20[/TD]
[TD]$1.30[/TD]
[TD]$1.20[/TD]
[TD]$1.00[/TD]
[TD]$1.30[/TD]
[TD]$1.40[/TD]
[TD]$1.20[/TD]
[TD]$1.40[/TD]
[TD]$1.40[/TD]
[TD]$1.20[/TD]
[/TR]
[TR]
[TD]other item price[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]other item price[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple price score[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple price if following score guidance[/TD]
[TD]$1.40[/TD]
[TD]$1.40[/TD]
[TD]$1.20[/TD]
[TD]$1.20[/TD]
[TD]$1.20[/TD]
[TD]$1.20[/TD]
[TD]$1.30[/TD]
[TD]$1.30[/TD]
[TD]$1.30[/TD]
[TD]$1.40[/TD]
[TD]$1.40[/TD]
[TD]$1.20[/TD]
[/TR]
</tbody>[/TABLE]

I have 10 items that I am testing over several years. I want to see the price affect on the items if the guidance on the number of months changes (row 2).
Since I plan to test several dozen options, putting this into a macro would save me probably over a week of work and really help me in my goals to improve my VBA.

I really appreciate it!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
This is definitely possible. Not sure I follow your logic though. If I am to do what you're suggesting then:

1. Find empty row
2. Read Price score = 4
3. Go find the Price ($1.40)
4. Look up the months to purchase (2)
5. Then duplicate $1.40 twice....

I see how this works in your example... am I to believe that we overwrite whatever data is currently there? and just got to the next product/commodity or should this be done until the end of the data? If so:

One column over where the price score is 2 and the price is $1.20 I would expect to see 4 cells in a row at $1.20 when I instead see : 1.40, 1.20, 1.20, 1.20
If I was to wait until the original purchase time period is up then I'm not sure why starting in column that has 10/16 does not repeat 3 times : 1.40, 1.40, 1.40

Can you please give a Before and After Example? to help me further understand your logic? Maybe I'm missing something simple.
 
Upvote 0
Hey Matt, I really appreciate you helping me. You are correct, and I did accidentally put in 1.40, 1.40, 1.20 when it should have been 1.40, 1.40, 1.40. I apologize for the confusion. When I retest the different "Then buy this many months" values, those values should replace the ones currently in the row.

To help clarify my logic, for the first month (January) where the price score is 4 and my guidance is to buy 2 months, it does not matter what the price is in February because I already made a "contract" to purchase apples at the $1.40 price for January and February (2 month guidance). And when March occurs, I look at the price again and purchase according to the guidance. My goal is to test numerous guidance amounts to see what is the best guidance for our items to get the best price. Our logic is that when the price score is a 1, we expect prices to increase so we would make a contract to buy at that price for many months in a row.

To clarify the steps a bit better as well:
1. Find the first empty cell within a specific row (because maybe the guidance was to contract for multiple months at a price, and I will be testing 10 items that each have their own prices per month)
2. Read Price score that coordinates with that cell for that item (there may already be a contracted price for some items but not for others). In this case, January Apple price score is 4.
3. Go find that Price ($1.40)
4. Look up the months to purchase (2)
5. Then duplicate $1.40 twice.

A "Before example" would be the table without information in the "Apple price if following score guidance" row. An example of "in the middle" would be:

[TABLE="class: cms_table_grid, width: 1000, align: center"]
<tbody>[TR]
[TD]If score[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Then buy this many months[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/16[/TD]
[TD]2/16[/TD]
[TD]3/16[/TD]
[TD]4/16[/TD]
[TD]5/16[/TD]
[TD]6/16[/TD]
[TD]7/16[/TD]
[TD]8/16[/TD]
[TD]9/16[/TD]
[TD]10/16[/TD]
[TD]11/16[/TD]
[TD]12/16[/TD]
[/TR]
[TR]
[TD]Apple price[/TD]
[TD]$1.40[/TD]
[TD]$1.20[/TD]
[TD]$1.20[/TD]
[TD]$1.30[/TD]
[TD]$1.20[/TD]
[TD]$1.00[/TD]
[TD]$1.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange price[/TD]
[TD]$1.50[/TD]
[TD]$1.60[/TD]
[TD]$1.40[/TD]
[TD]$1.50[/TD]
[TD]$1.30[/TD]
[TD]$1.40[/TD]
[TD]$1.40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]other item price[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple price score[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Orange price score[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple price if following score guidance[/TD]
[TD]$1.40[/TD]
[TD]$1.40[/TD]
[TD]$1.20[/TD]
[TD]$1.20[/TD]
[TD]$1.20[/TD]
[TD]$1.20[/TD]
[TD]$1.30[/TD]
[TD]$1.30[/TD]
[TD]$1.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Now that today it is July and my contract that I started in March ended, I have to start a new contract. I notice that apples are $1.30 this month. That puts me at an Apple price score of 3. The guidance for this test says that when the score is 3, then I should contract for 3 months at today's price. I do not know if those prices will be better or worse in the future, but that is my guidance based on the assumption that low prices are more likely to return to the mean.
I will have 10 items listed (such as Oranges) that will have its own prices and price scores, but the guidance will be the same.

I hope I was more clear this time.

Thanks again!
 
Upvote 0
Not exactly clear but here is my best guess as to what you are trying to do.... Ideally we would have dynamic references rather than hard coded, but for this example it may be easier to understand.

BEFORE:


Excel 2010
ABCDEFGHIJKLM
1If score12345
2Then buy this many months54321
3
41/162/163/164/165/166/167/168/169/1610/1611/1612/16
5Apple price$1.40$1.20$1.20$1.30$1.20$1.00$1.30
6Orange price$1.50$1.60$1.40$1.50$1.30$1.40$1.40
7other item price
8
9Apple price score4223213
10Orange price score3423122
11Apple price if following score guidance
Sheet1


AFTER:


Excel 2010
ABCDEFGHIJKLM
1If score12345
2Then buy this many months54321
3
41/162/163/164/165/166/167/168/169/1610/1611/1612/16
5Apple price$1.40$1.20$1.20$1.30$1.20$1.00$1.30
6Orange price$1.50$1.60$1.40$1.50$1.30$1.40$1.40
7other item price
8
9Apple price score4223213
10Orange price score3423122
11Apple price if following score guidance$1.40$1.40$1.20$1.20$1.20$1.20$1.30$1.30$1.30
Sheet1



VBA CODE:

Code: 
[COLOR=#0000ff]Sub[/COLOR] PriceGuidance()


    [COLOR=#0000ff]Dim[/COLOR] lastCol              [COLOR=#0000ff] As[/COLOR] [COLOR=#0000ff]Integer[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] colLp                 [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]Integer[/COLOR]
[COLOR=#0000ff]    Dim [/COLOR]buyMonths           [COLOR=#0000ff]  As Integer[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] comPrice              [COLOR=#0000ff]As Currency[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] comPriceScoreRow      [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] comPriceRow           [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] comBuyRow             [COLOR=#0000ff]As Integer[/COLOR]
    
[COLOR=#008000]    'Define Row References for commodity[/COLOR]
    comPriceScoreRow = 9
    comPriceRow = 5
    comBuyRow = 11

[COLOR=#008000]    'Define Last Column   [/COLOR]
    lastCol = Cells(comPriceScoreRow , Columns.Count).End(xlToLeft).Column

    
 [COLOR=#0000ff]   For[/COLOR] colLp = 2[COLOR=#0000ff] To[/COLOR] lastCol
[COLOR=#008000]        [/COLOR]
[COLOR=#008000]        'Get Number of Months to Buy[/COLOR]
        buyMonths = Application.HLookup(Cells(comPriceScoreRow, colLp), Range("B1:F2"), 2,[COLOR=#0000ff] False[/COLOR])
        
[COLOR=#008000]        'Get Commodity Price[/COLOR]
        comPrice = Cells(comPriceRow, colLp)   
        
[COLOR=#008000]        'Write prices to cells[/COLOR]
   [COLOR=#0000ff]     For[/COLOR] MonthLp = colLp [COLOR=#0000ff]To[/COLOR] colLp + (buyMonths - 1)
            Cells(comBuyRow, MonthLp) = comPrice
   [COLOR=#0000ff]     Next [/COLOR]MonthLp
        
[COLOR=#008000]        'Increment Column Reference[/COLOR]
        colLp = colLp + (buyMonths - 1)
        
  [COLOR=#0000ff]  Next [/COLOR]colLp
    
[COLOR=#0000ff]End Sub[/COLOR]
 
Upvote 0
Magic! Your code works great! Thanks so much! I can make some wicked good formulas, but I'm still working on my VBA. I learned a lot from studying this code. Thanks again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
How to SUMIF based on criteria from another sheet?
Replies
6
Views
394
Cubist
Cubist
smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
123
hagia_sofia
hagia_sofia
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
960
Alex Blakenburg
A
P
  • Solved
What will be formula on Search Box as i type data filter accordingly...
2 3
Replies
26
Views
616
pankajgrover
P
P
  • Solved
Copy column values from another sheet but want to exclude some specific row name completely..
2 3
Replies
23
Views
658
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

Share this page

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