Isolating values in a cell.

Keojey

New Member
Joined
Sep 19, 2018
Messages
35
Office Version
  1. 365
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!
 
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
 
Last edited:
Upvote 0
Solution

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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