Replace cell value via userform

[Mr.Karr]

Hello

I found the attached sample file on the web.

But there seems like a problem where replace button doesn't make any changes for the records which are available after row24.

I would really appreciate if someone can help me to get this done.

Thanks a lot in advance. Please see the attached file.

 

[NARAYANK991]

Hi ,

Your problem has nothing to do with row 24.

The code has not been written to take into account duplicates , for which a reference to the row numbers needs to be maintained , so that when a selection is made from the listbox , the entry against the relevant row number is modified ; at present the code is doing a find , which will work only when there are no duplicates.

A complete rewrite is required.

Narayan

 

[Mr.Karr]

@NARAYANK991 : thanks Narayan for your findings and prompt response.
I would request if you can help on this please?

 

[NARAYANK991]

Hi ,

I am sorry , but I am not in a position to devote time to this ; hopefully , someone else will help out.

Narayan

 

[Hui]

Firstly, Its a nice little piece of work you have there.

The problem is that there is no connection between the loaded listbox and the original data

What I have done, which took minimal effort was to load the list box with a 5th column
The 5th Column is not displayed but stores the Row number from the original Database

Then in the replace subroutine it simply uses the value from the 5th column as the Row value for the Replacement

It will only replace one value at a time, not multiple rows

and Voila.

see attached

 

[Mr.Karr]

@Hui : appreciate your response.
Please see below my findings;
1. when you search product by "*" or perform an empty product search, each product line repeats by 3 times. Is this intentional ?
2. Replace button doesn't replace the existing product description but it does some changes to the product number.
3. When you delete a line, it throws up: runtime error 13. type mismatch.
4. If I want to expand the existing records with additional columns, is that possible ?

Please advise.

 

[Hui]

I only tried the Replace option as I didn't think it would impact the others
I never tried * searches

 

[Mr.Karr]

@Hui :
http://forum.chandoo.org/threads/combo-box-search-value-auto-fill-textbox.22975/

this is for similar kind of activity. Can you please help please

 

[Hui]

In the file I sent change the Named Formula for Product Range to:
=Products!$A:$A

 

[Mr.Karr]

@Hui : sorry Hui. That doesn't help

 

[Hui]

Ok how about this

You can now also just type 12345678 in the Product code and it will reformat it for you as 123-456-78 when you press Tab or Enter

I have tested all functions and it appears to do as it should

 

[Hui]

In regards to adding additional columns

Will that be a one off or is it going to change regularly?

It is easy to add additional columns as a 1 off
A little bit harder to allow them to change more regularly

 

[Simayan]

Hi,

Tried to modify the code.

if you put ** in search filed multiple records coming...else its working fine i.e. either with * or part string/full string/part string followed by *.

I have also added additional column with various control.

Search module & add module working absolutely fine

Some how my delete module is not working can anyone look into

Separately the replace module written is quite complex ;I suggest can we write a code with the help of taking reference of Serial no ( i.e. row num-1) in the same way we are adding new record.

Thanks in advance