ActiceCell Copy

[RBartonSWW]

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?!

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

 

[gingerafro]

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]

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]

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]

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

Thanks.

Rob

 

[gingerafro]

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]

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.



Thanks anyways.