Help with copy/paste code based on value

GuyGadois

Active Member
Joined
Jan 12, 2009
Messages
342
Office Version
  1. 2019
Platform
  1. Windows
I would appreciate any help putting together some code that I can run to copy and paste from one cell to another based on value. Here is what I am looking for:

I have a list of stocks in 32 rows. Column B or range "column_last" has the current price. Column R or range "column_high_watermark" has the value of the high watermark and column S or range "column_watermark_date" has the date that watermark takes place.

I would like to run the macro and have it look at each row, from 2 to 32 and compare column B value and see if it is bigger than column R value. If it is TRUE then it would copy column B value and paste the value only to column R and then paste in the current date into column S. Then, it would go to the next row until it has finished row 33.

How would you best perform this? What other info is needed?

Cheers and thank you in advance.

Guy
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Here is what I have so far but there is a major problem. I would like the macro only to run on rows 2 thru 33. The current code continues on down and I don't know how to stop it. Any help is appreciated. I am not sure this is the best way to do it.

Guy

Code:
Sub Watermark()
*** Dim c As Range
*** Dim x As Long
*
*** For Each c In Sheets("Portfolio").Range("last").Cells
*********** For x = 0 To 32
*************** If c.Offset(x, 0) > c.Offset(x, 16) Then
******************* c.Offset(x, 16).Value = c.Offset(x, 0).Value
******************* c.Offset(x, 17).Value = Date
*************** End If
*********** Next x
*** Next c
End Sub

Sheets("Portfolio").Range("last").Cells = range B2:B33
 
Last edited:
Upvote 0
hello guy
try the following and see if it is what you want

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = 1
Do
x = x + 1
If Range("b" & x) > Range("r" & x) Then Range("r" & x) = Range("b" & x): Range("s" & x).Value = Date

If x = 32 Then Exit Sub
Loop
End Sub

cheers

kevin
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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