• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replace cell value via userform

Mr.Karr

Member
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.
 

Attachments

  • ManageDatabase.xlsm
    33 KB · Views: 0
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
 
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
 

Attachments

  • ManageDatabase.xlsm
    29.3 KB · Views: 0
@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.
 
I only tried the Replace option as I didn't think it would impact the others
I never tried * searches
 
In the file I sent change the Named Formula for Product Range to:
=Products!$A:$A
 
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
 

Attachments

  • ManageDatabase2.xlsm
    34.6 KB · Views: 1
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
 
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
 

Attachments

  • Book1.xlsm
    40.5 KB · Views: 0
Back
Top