Posts by Russco
-
-
Hi all, I have a dbase with a userform with combobox restricting users to selecting between M OR MM OR M-I-O which are event entry codes. This updates dbase. Then I use macro advanced filter which I have just found an issue where if criteria is set to M it will still extract output range to include records with MM and M-I-O. Why and how to overcome so all I get is those records with M only.
CodeHDScore.Range("BR4") = "M" '// <<< slightly different for EV1 & EV2 (M-I-O) HDScore.Range("BR5").ClearContents HDScore.Range("BR6").ClearContents 'Sort DBase, ADV FILTER and fill Listbox for Maiden Bracelet/Agg 1x line of criteria Call SortHeadBracelet HDScore.Range("A3:Z500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=HDScore.Range( _ "br3:br4"), CopyToRange:=HDScore.Range("HD_ExtractResult"), Unique:=False
Any suggestions on how to just output M records would be great.Thanx
-
Re: Find Replace if first two characters instring
Jindon, I will certainly read those help topics. Much for me to learn but am enjoying the challenge, once again many thanks for this code my users can enter addresses in lowercase and it wont matter now !!
-
Re: Find Replace if first two characters instring
Hi Jindon, I see why you are classed as a "Very Helpful member". I've used test2() This does MORE than I asked for but awesome thank you. My question how does it not only deal with my issue of uppercasing RD found anywhere in an address string but code seems to also propercase the whole string which is also what I wanted !! I'd like to understand how your test2() sub does all that ? Many thanks indeed.
-
Hi all, still battling this issue. I want to look in column F or G and if address contains as first two characters in string e.g. "rd6 1 anywhere street stratford" I want my code to propercase whole address string but then look for RD at start of string and replace rd to RD but not change the rd in word Stratford. The code I have now turns the example string above to " RD6 1 Anywhere Street StratfoRD ". Note that this code uses a UDFunction called ProperCaseX. At present it only looks at one cell, I'd like it to search all of columns F or G
CodeSub FindRD()Dim FindRD As StringDim textstart As IntegerFindRD = ActiveSheet.Range("F2").Valuetextstart = InStr(1, FindRD, "rd", vbTextCompare)If textstart = 1 Then FindRD = Replace(FindRD, "rd", "RD") ProperCaseX (FindRD) FindRD = Replace(FindRD, "Rd", "RD") ActiveSheet.Range("F2").Value = FindRD End IfEnd Sub
Hope someone can help me overcome this issue. Regards
-
Re: error 91 object var or with block not set
Further comment, on this procedure, no other Dim's or Set = ...., I think I need them but unsure how. The debugger indicates it recognises lbCompID etc it seems to be sticking on sheet ref ..("HuntScores") and .offset part of code ????
-
Re: error 91 object var or with block not set
Hi Dan, no good compile error method etc I think this is because a label which is on my userform (me.lbCompID) does not have a value. Tried to Remove the .value but then got error 91 again. Any further thoughts much appreciated.
-
Hi all, made some additions to existing code that was working just fine. But after inserting the additions it now bugs out. "error 91" as per subject line. See existing section of code below. Note this is just an extract and code is linked to submit command button on a userform.
While I have read references to this error I assume I need to better set & declare my objects but unsure how. What confuses me is that before I added code this EXTRACT worked just fine.Code
Display More'// if DOG#1 record exists find index number(col Q - rangename: HTIndx) and overwrite those details THEN DELETE RECORD if required. If Not Me.LbIndx1 = "" Then With Sheets("HuntScores").Range("HTIndx").Find(what:=Me.LbIndx1, lookat:=xlWhole) .Offset(0, -16).Value = Val(Me.lbCompID) 'CompID [COLOR=#ff0000]<<<<<..BUGS OUT HERE[/COLOR] ****** .Offset(0, -15).Value = Me.lbFullName 'FullName .Offset(0, -14).Value = Range("ID").Find(what:=Me.lbCompID, lookat:=xlWhole).Offset(0, 9).Value 'Local .Offset(0, -13).Value = Me.TextBox8.Value 'ManStatus .Offset(0, -12).Value = Me.TextBox3.Value 'DOG#1 Name .Offset(0, -11).Value = Me.ComboBox1.Value 'EV3 .Offset(0, -4).Value = Me.ComboBox2.Value 'EV4 .Offset(0, -2).Value = Me.ComboBox3.Value 'Bracelet end with end if
Hoping someone could help me on why I'm getting this error or what might resolve please.
thanks -
Re: Find and replace part string
Quote from pike;679208The remaining issue is if address string begins with e.g. rd13 North Auckland then this code ignores the "rd" because no space before, this is my dilemma, how to handle this situation aswell and not messup e.g. kenerdine in middle of string ??
any further help would be greatly appreciated.
-
Hi all, despite looking at a large amount of discussion on string manipulation I still can't quite solve my proper format of certain address types in my excel competitor database. The particular issue I have is many of my competitors addresses are rural and have e.g. RD5 (Rural Delivery zone 5) An example 138 Long road rd13 hawkes bay. What I'm trying to do is when user enters via userform the address in "any case" I'd like it vbpropercase ! BUT also call another sub which then looks for the "rd" anywhere in sheet2.column "f & g" and converts to uppercase RD13. The other issue I'm finding is that when street name is e.g. kinderdine street, the result is KinderRDine Street. Is it possible to modify my code to handle both of these situations?
-
Re: pass userform1 label content to label on userform2
Many thanks Bill, any difference where I'm actually using labels (frmsearch.lbCompID >>>>>headform2.lbCompID) to house data rather than textbox on each form ??
-
Hi all, have two userforms with a label which displays CompetitorID. I want to transfer content (displayvalue) from UF1.label to UF2.Label. I know labels don't have a value property but want to simply know if it can be done as presently I'm getting run time error 380, can't set property value.
Regards in advance.
-
Re: Find Findnext to populate userform
Hi cytop, please find simple sample file attached. My apologises. I hope this is comprehensible. I can adapt code for the other fields shown on userform, if you can help to show code for populating the 5 records for comp id 297. I have hardcoded ID field just for testing. Usually my comp ID will be found by separate search userform. If you can show how to populate DogName would be enough I think I can then adapt code for other fields. Regards Russco
-
Hi all, I have a userform which allows users to enter up to 5 dog entries (DOG TRIAL COMPETITORS). I have the ADD new entries part working. But if user then wants to recall the dog entries for a particular competitor (search string based on ID) from the sheet("EntryRego") col A (Comp ID) , col B (DogName) I can get the first found entry to fill row 1 (Dog entry record1) ON MY FORM but can't work out how to populate my userform for 2nd or subsequent entries if they exist at all. I've looked at find and find next code but seems to me that this will treat all found records (e.g. CompID = 25 has say 3 dog entries (records) it does the same thing, I want each found records to be placed on separate line of my userform so for simple example:1st found record populates myform.textboxDogName1 and 2nd found record populates myform.textboxDogName2 and so on (max 5th record found populates myform.textboxDogName5) Hope some can help.
-
Hi all, I have managed to create my userform which shows an existing record from my excel database based on Comp ID NO. in column A. However, some of the records in my database (on sheet called sheets("lists") ) have missing data. e.g. column L often is blank so when user is editing an existing record using this userform instead of seeing a textbox control (which is blank - no data) I'd prefer upon initialising this userform that code conditionally checks if value of col L is blank then instead of a textbox displaying instead it displays a listbox with code: ListBox3.AddItem "M - Maiden" or 'ListBox3.AddItem "I - Intermediate" or ListBox3.AddItem "O - Open" I DO NOT want user having to manually type data in the textbox field so QC is maintained. So hopefully this is clear, how do I write code in userfrom (called EDITForm1) initialise event to conditionally make visible listbox if value of field (col L) is blank otherwise just make textbox visible with data if it exists. Many thanks for any help.
-
Re: Using Autofilter criteria2 range issue
Hi Smallman, appreciate your time and patience, as you can tell I'm a beginner. I assumed in this new code you've suggested that where you reference sheet(2) I'd change that to sheet(9) given my "date value" I'm trying to use as criteria is located on sheet9 which is actually called "Rego-DateTime". Bearing in mind my value on sheet9 at cell c2 is a "date" your code is appearing not to recognise it: Sheets(9).Range("C2").Value and when I run macro it produces no results in my extract range. What have I now done wrong. ?
-
Re: Using Autofilter criteria2 range issue
Hi again Smallman, strangely although I thought your suggested code worked and it did on a test application I set up on excel v2007 when I now try to apply to my real application file which is excel v2010 it gives me a Run time error 9
subscript out of range[code]Range("A5:L5").Select
Selection.AutoFilter
ActiveSheet.Range("$A$5:$L$105").AutoFilter Field:=12, Operator:= _
xlFilterValues, Criteria1:=Sheets("Sheet9").Range("C2").Value
ActiveSheet.Range("$A$5:$L$105").AutoFilter Field:=8, Criteria2:="<>"[\endcode]The debug highlights lines of code for criteria1 starting Activesheet.range.....
Hope you can help me further ?
Regards
-
Re: Using Autofilter criteria2 range issue
Hi Smallman, have been away and just catching up. Appreciated your reply. Much appreciated, has solved my issue.
Regards 2 u
-
Hi, I'm using recorded autofilter code and have the following line of code that does not work quite the way it needs to. I want to use a range value on sheet9 at C2 as the criteria2:= in my autofilter but the recorded version becomes absolute value which won't be correct date for other users. This range value (C2) is day1 date of an event
Here is part of code currently:
CodeSelection.AutoFilter ActiveSheet.Range("$A$5:$L$200").AutoFilter Field:=12, Operator:= _ xlFilterValues, Criteria2:=Sheet9.Range("C2").Value ActiveSheet.Range("$A$5:$L$200").AutoFilter Field:=8, Criteria1:="<>"
Hope someone can suggest answer to this problem.
Many thanx
Russco -
Re: Fill list column
jindon, you are the man, works perfectly, thank you so much for your help.
Russco