VBA Code to check if cell value already exists in the range while looping

Anaya Zeeshan

New Member
Joined
Nov 4, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
I am trying to write a code to loop through a range and copy cell values if the cell is not blank and cell value doesn't already exists in the range.
I have written this code but its checking the cell above but I want to check check MyValue in entire range not just the cell above. if myValue is unique only then I want the value too be pasted in column T


Sub Testing()
Dim i As Long
Dim LastRow As Long
Dim MyValue As Variant
Const StartRow As Byte = 4

LastRow = Range("P" & Rows.Count).End(xlUp).Row

For i = StartRow To LastRow
MyValue = Range("P" & i).Value
If MyValue <> "" Then
If Range("p" & i + 1) <> MyValue Then
Range("T" & i).Value = MyValue
End If
End If
Next i

End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Do you want to copy from col P to T if the value in P only exists once, or do you want to copy each distinct value from col P
 
Upvote 0
Thanks for that, forgot to ask, should the values be copied to the same row they are on, or to the next blank row in col T?
 
Upvote 0
Ok, how about
VBA Code:
Sub AnayaZeeshan()
Dim x As Variant
   With Range("P4", Range("P" & Rows.Count).End(xlUp))
      x = Application.Unique(.Value, , 1)
   End With
   If IsArray(x) Then
      Range("T" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(x)).Value = x
   End If
End Sub
 
Upvote 0
Ok, how about
VBA Code:
Sub AnayaZeeshan()
Dim x As Variant
   With Range("P4", Range("P" & Rows.Count).End(xlUp))
      x = Application.Unique(.Value, , 1)
   End With
   If IsArray(x) Then
      Range("T" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(x)).Value = x
   End If
End Sub
It is working only on 13 rows.
this code is beyond my understanding, as I am just a beginner. if possible, It would be great if you could just add to my code so that I can make changes later.
 
Upvote 0
What do you mean it's only working on 13 rows?
It would be great if you could just add to my code so that I can make changes later.
Unfortunately that is not really possible, as your code simply doesn't do what you have asked for.
 
Upvote 0
What do you mean it's only working on 13 rows?

Unfortunately that is not really possible, as your code simply doesn't do what you have asked for.
I think I wasn’t clear before. If there are duplicate values, i do not want to skip that value, I want to record it once
 
Upvote 0
So you want a list of all the distinct values?
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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