VBA to paste number in first empty cell if conditions are met

smide

smide

Board Regular
Joined
Dec 20, 2015
Messages
164
Office Version
  1. 2016
Platform
  1. Windows
Hello.

In row 3 (Sheet1) I have products names (every second column) and list of their prices (below product names). New obtained price for each product is also in row 3 and always in "right" column.

I need a VBA to check whether this new obtained product's price is different from the last (active) price (last price in that column).
If this price is different then paste it in first empty cell in that column.

Example.

* new obtained prices are in cells C3,E3,G3...

Sheet1 (before macro run)

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Product1
[/TD]
[TD="align: center"]35
[/TD]
[TD="align: center"]Product2
[/TD]
[TD="align: center"]59
[/TD]
[TD="align: center"]Product3
[/TD]
[TD="align: center"]33
[/TD]
[TD="align: center"]....
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]18
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]19
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]17
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]22
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]25
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]29
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]36
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]33
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
Sheet1 (after macro run)

[TABLE="class: grid, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Product1
[/TD]
[TD="align: center"]35
[/TD]
[TD="align: center"]Product2
[/TD]
[TD="align: center"]59
[/TD]
[TD="align: center"]Product3
[/TD]
[TD="align: center"]33
[/TD]
[TD="align: center"]....
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]18
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]19
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]17
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]22
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]25
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]29
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]35
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]36
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]33
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]59
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

* obtained price for Product 3 is the same as last price - don't add value 33 again in Product 3 column (!)
 
Sort by date Sort by votes
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG22Sep07
[COLOR="Navy"]Dim[/COLOR] rLst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] cLst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
cLst = Cells("3", Columns.Count).End(xlToLeft).Column
[COLOR="Navy"]For[/COLOR] Ac = 2 To cLst [COLOR="Navy"]Step[/COLOR] 2
    [COLOR="Navy"]If[/COLOR] Cells(3, Ac + 1) > Cells(Rows.Count, Ac).End(xlUp).Value [COLOR="Navy"]Then[/COLOR]
        Cells(Rows.Count, Ac).End(xlUp).Offset(1).Value = Cells(3, Ac + 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
Try this:-
Code: 
[COLOR=Navy]Sub[/COLOR] MG22Sep07
[COLOR=Navy]Dim[/COLOR] rLst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] cLst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
cLst = Cells("3", Columns.Count).End(xlToLeft).Column
[COLOR=Navy]For[/COLOR] Ac = 2 To cLst [COLOR=Navy]Step[/COLOR] 2
    [COLOR=Navy]If[/COLOR] Cells(3, Ac + 1) > Cells(Rows.Count, Ac).End(xlUp).Value [COLOR=Navy]Then[/COLOR]
        Cells(Rows.Count, Ac).End(xlUp).Offset(1).Value = Cells(3, Ac + 1)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...


Thanks for reply.
There is a problem with macro if new obtained price is less than last price (it does not copy it) ...
Any idea how to fix this?
 
Upvote 0
Change :-
Code: 
 If Cells(3, Ac + 1) [B][COLOR=#FF0000]<>[/COLOR][/B] Cells(Rows.Count, Ac).End(xlUp).Value Then
 
Upvote 0
Code: 
Sub new_one()
    Dim x, c As Long
    x = Range("b3").End(xlToRight).Column
    For c = 2 To x Step 2
        If Not Cells(Rows.Count, c).End(xlUp) = Cells(Rows.Count, c + 1).End(xlUp) Then
            Cells(Rows.Count, c).End(xlUp).Offset(1) = Cells(Rows.Count, c + 1).End(xlUp)
        End If
    Next c
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Copy non-empty cells from the same column from multiple sheets to a sheet "Breeders"
Replies
6
Views
121
mumps
mumps
G
  • Question Question
Creating a dynamic list of unique values in a range with a condition
Replies
3
Views
256
Cubist
Cubist
S
  • Solved
Individual Ranking
2
Replies
11
Views
510
sksanjeev786
S
gtd526
  • Solved
Modify Conditional Formatting to include Odd and Even rows
Replies
4
Views
130
Peter_SSs
Peter_SSs
K
  • Question Question
conditional formatting question
Replies
2
Views
686
kerm007
K

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