Copy and paste value only as long as it doesn't equal the value of the destination cell

[TedX]

Hi Everyone,

I scrape up a free publicly available website and gather some very basic information, I add it to a helper page, get rid of the formatting and just have straight data. I switch to my rating worksheet and do a Vlookup and get a positive result as intended. Now my issue is, I have the new rating (just a number) in column AM and I want to create a macro that finds any numbers in column AM as opposed to just the vlookup formula and then compare it to the rating in column AH which is just a number. If the numbers are not equal, then copy the value in column AM and paste it to column AH which is 5 columns to the left (-5).

It's vital that nothing is done to all the other values in the AH column, I only want to change the value in the AH column if a genuine number is on the row in Column AM and providing it's a different number of if there is no number assigned yet to the cell in column AH.

In the image, you can see that the horse named DARLAMAX has a current value of 69 but needs to be downgraded to 65. Can anyone suggest a macro that will do this for me please?

 

[Legacy 143009]

Assuming your data start from row 2:

Sub updateRating()
  Dim lRow As Integer
  lRow = Cells(Rows.Count, 34).End(xlUp).Row
  For i = 2 To lRow
    If Application.WorksheetFunction.IsNumber(Cells(i, 34).Value) Then
      If Not Cells(i, 34).Value = Cells(i, 39).Value Then
        Cells(i, 34).Value = Cells(i, 39).Value
      End If
    End IF
  Next
End Sub

 

[Peter_SSs]

If the numbers are not equal, ...

... if a genuine number is on the row in Column AM and providing it's a different number

I cannot see why you have to worry if it is a different number.
What is the difference between leaving, say, 26 alone and over-writing 26 with 26? In either case you still end up with 26 in column AH

My code therefore doe not do that 'difference' check. It also only checks the rows that have numbers in col AM.

Sub TransferValues()
  Dim Nums As Range, c As Range
  
  On Error Resume Next
  Set Nums = Range("AM1", Range("AM" & Rows.Count).End(xlUp)).SpecialCells(xlFormulas, xlNumbers)
  On Error GoTo 0
  If Not Nums Is Nothing Then
    For Each c In Nums
      Cells(c.Row, "AH").Value = c.Value
    Next c
  End If
End Sub

 

[TedX]

I cannot see why you have to worry if it is a different number.
What is the difference between leaving, say, 26 alone and over-writing 26 with 26? In either case you still end up with 26 in column AH

My code therefore doe not do that 'difference' check. It also only checks the rows that have numbers in col AM.

Sub TransferValues()
  Dim Nums As Range, c As Range
 
  On Error Resume Next
  Set Nums = Range("AM1", Range("AM" & Rows.Count).End(xlUp)).SpecialCells(xlFormulas, xlNumbers)
  On Error GoTo 0
  If Not Nums Is Nothing Then
    For Each c In Nums
      Cells(c.Row, "AH").Value = c.Value
    Next c
  End If
End Sub

Thank you to both Flashbond and Peter_SSs, I can't tell you how much I appreciate your effort, it worked flawlessly. A sincere thank you, I just love this forum

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[TedX]

You're welcome. Glad we could help. Thanks for the follow-up.

Just a quick question, I perform pretty well the same actions when scraping a website using Power Query, is it possible to create a macro of my actions? If a macro can't be made, can I use Power Automate to copy my actions? I'm of the understanding that Macros are strictly for use inside Excel, but I've learnt never to say never I can ask this question in a new post if you like but all I want is a yes or no, if it's yes, I'll do the research.

 

[Peter_SSs]

I don't know the answer but I would start a new thread. You can provide a link to this thread if you think that will assist any potential helpers in the new thread.

 

[TedX]

I don't know the answer but I would start a new thread. You can provide a link to this thread if you think that will assist any potential helpers in the new thread.

Will do, thanks Peter