Isolating values in a cell.

[Keojey]

Hello,


I'm trying to isolate/ignore the values that get entered in a cell in order to keep things neat within the program I'm making.

Currently, I have multiple values in "Sheet1" going into "Sheet1 Data" with the use of a VBA Macro. These values are then compared to other values within the same sheet and ignored if certain values are equal to each other. In a normal case where there are two equal values, the second one would be ignored when this information gets used in a spreadsheet.

The issue that I'm seeing is that the first value, for example, is "123, 124" and a second value would be just "124." If it happens like this, then nothing would be ignored because the values are not equal and the end result on the spreadsheet would be "123, 124, 124." The ideal would be to have the second value get ignored because the first value has the "124" within its value already and a result of just "123, 124."

Is it possible to do this in any way with or without VBA?

Thank you!

 

[Akuini]

So initially trying this it didn't work because the line
'rr = Columns(x).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row'
Would give me error saying the "Object variable or With block variable not set" unless I set the value of 'x' to anywhere between 1 and 10? So it works, but could you possibly explain why it won't work unless the value of 'x' is set between these numbers? You said 'rr' is equal to the last row, so I first tried setting 'x' to 23 and 24 respectively, but had the above results.
On that note, there are some weird inconsistencies when I use the same code for two sheets. On the first sheet, both codes will only work between row 4 and 24. But on the second sheet, it'll go all the way to row 52 for the first code and way past Row 80 with the second code. Is there not a variable to change which row it should end on?

Ok, I made some assumptions aboout the data layout, maybe some of them are wrong.
In each targeted columns (i.e col C&F, I&L, O&R, U&X, AA&AD .. & so on):
1. there is header in row 3.

2. there are no blank columns. If there are any blank columns in targeted columns the code will fail in this line:
'rr = Columns(x).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row'

You said 'rr' is equal to the last row, so I first tried setting 'x' to 23 and 24 respectively, but had the above results.

Don't change the value of x
x is for column number, x = 3 means col C
so when value of x is 3, it means:

rr = Columns(3).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

it means 'find last row with data in col C', so if in col C you have last data in row 24 then rr value become 24.

I didn't use fixed last row with data as you specified (i.e row 23 & 24) in order to make the code flexible to use for both sheets.

But let's try using fixed last row & see what happen
Just change this line (in both codes)
rr = Columns(x).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
to:
r = 24

 

[Keojey]

This all works perfectly! Thank you so much for the help!

 

[Akuini]

This all works perfectly! Thank you so much for the help!

You're welcome & thanks for replying