ActiceCell Copy

RBartonSWW

New Member
Joined
Nov 18, 2005
Messages
38
Could some people please test this code, basicaly it is meant to copy data from a spreadsheet to a userform.
When you press a button on the userform it looks throught the spreadsheet for the Account Number entered in the 'tbAcc' text box, and if it finds it then it will copy the data from the ofset cell into the 'tbName' text box.

It works, but it seems to (atleast for me) insert a character which is not in the spreadsheet. Does it do this for anybody else?!


Code:
Private Sub cmdSearch_Click()

       Dim firstCell, nextCell, stringToFind As String
       stringToFind = tbAcc.Value
       Set firstCell = Cells.Find(what:=stringToFind, lookat:=xlWhole, _
           searchdirection:=xlPrevious)
       If firstCell Is Nothing Then
           Dim msg As String
        msg = "Claim not found. Do you want to create a new one?"
        If MsgBox(msg, vbQuestion + vbYesNo, "Claim not found") = vbNo Then Exit Sub
        frmNew.Show
            
       Else
        nextCell = _
               Cells.FindNext(after:=Range(firstCell.Address)).Address
           
        Range(firstCell.Address).Select
        Selection.Offset(0, 1).Select
        ActiveCell.Copy
        tbName.Paste

       End If

End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

gingerafro

Active Member
Joined
Mar 23, 2005
Messages
448
Yes, its doing it for me (or not as the case may be!)

I manage to get rid of it by selecting multiline = true,
but then selecting another value puts the new return value underneath, which is not ideal. I'm going to keep playing with with the other options.

This may be one of those problems that needs a workaround. Also, the 'pilcrow' (never knew it had a name) is common in WORD (there is even a button for it), but I don't know what it does. If we could find that out we may be further on...
 

RBartonSWW

New Member
Joined
Nov 18, 2005
Messages
38
Yeah, in Word you click it and it puts a symbol in every space · (middle dot) like that one and ¶ (Pilcrow) for every return and paragraph. Excel does not have this function and if you try to put the Pilcrow into a cell it comes out as a  (unknown character).
This could be the case if I had pressed Enter on each cell to get to the next one, but I know I didn't, which makes it a right pain.
 

gingerafro

Active Member
Joined
Mar 23, 2005
Messages
448
OK, so I have a workaround.

I manage to get rid of it by selecting multiline = true,
but then selecting another value puts the new return value underneath, which is not ideal.

I put this at the end of the macro so that another search can't be performed immediately

cmdSearch.Locked = True

I then have another button called NEW SEARCH that has the code:

Unload Me
UserForm1.Show

This combination should stop the pilcrow and force the user to click NEW SEARCH at the end.
 

RBartonSWW

New Member
Joined
Nov 18, 2005
Messages
38

ADVERTISEMENT

Ah, cool, Ok.

Cheers for that, it's been bugging me for ages...I now just have to update a few hundred records to remove the symbols thanks to some great co-workers that carried on using it and ended up with them all over the spreadsheet. :(

At least now I can sort it out for the future :biggrin:

Thanks.

Rob
 

gingerafro

Active Member
Joined
Mar 23, 2005
Messages
448
Firstly, go slap your co-workers.

Secondly, go into WORD, go to insert>symbol... and find the pilcrow. Insert it into the word document.

Now copy it.

Go back into EXCEL and do a find and replace.
CTRL + V to put the pilcrow into the find box and leave the replace with box blank.

Hope that works for you.
 

RBartonSWW

New Member
Joined
Nov 18, 2005
Messages
38
Well in the cells Excel it is shown as an unknown character which is a square box, and I can't search for it and when I F2 edit (or click on the formula bar) it converts it into a return and makes my cell double high. I am quite impressed by the bugs that this is throwing out, also quite anoyed as it doesn't seem fixable, unless there is another way to copy text from cells into a Userform and vice-versa.

:confused:

Thanks anyways.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,022
Messages
5,834,994
Members
430,330
Latest member
Syed Yasir Hannan

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
Top