Macro Used Textbox In Worksheet Rather Than Cell Reference As Criteria To Find Matches
Sep 26, 2013
This is a continuation of thread 182629 [URL] ....
The End User wanted originally to type characters into cell C1 of a worksheet that the macro searched for in another worksheet, copying across every row containing the input.
The responses gave me the following code which worked perfectly:
VB:
With Sheets("PICKLIST")
myFind = "*" & .[c1].Value & "*"
End With
If myFind = "*" & "" & "*" Then Exit Sub
Unfortunately, the user NOW wants a Text Box instead of putting the characters into a cell.
I added a textbox (originally called 'TextBox2' but renamed "Choice") and "Dimed" CHOICE As Range.
Then tried the following code:
VB:
With Sheets("PICKLIST")
myFind = "*" & CHOICE & "*"
End With
But it generates a "RunTime Error 91" - Object variable or With block variable not set.
View 8 Replies
ADVERTISEMENT
Apr 11, 2008
I've got 3 sheets, A, B, and C. In sheet C, I have a cell named "element". That cell can have either the letter A or the letter B in it. In my VB code, I want to reference the sheet which correlates with the "element" cell. So,
Worksheets("element").Select
when the user clicks my "go" button, it calls this logic, and I want the worksheet that correleates to the letter in the cell in worksheet C - to open.
The reason for my posting - this is not working, and may not even be possible. Perhaps someone has experience in this and knows how to do it - or knows a different way to do this.
View 4 Replies
View Related
Nov 21, 2011
I have a macro that would check data in Column A and validate if a particular number is repeating, then for that number go to column B, Take the Values from there go to a new sheet and paste the values in a row.
CurrencyDateRef CodeIDAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD-111,131.450Distr Payable 07152011USD07152011XDVU4315420.4240.C5001.USD18,606,772.190Distr Payable 07152011USD07152011XDVU4315420.4240.C5002.USD111,131.450Distr Payable 07152011
I get the data in the below format
CurrencyDateRef CodeIDAccountAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD420.4240.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD420.4240.C5002.USD-111,131.450Distr Payable 07152011
I need to the macro to get the data not from the second cell.
Below is my macro
Sub test()
Dim idRange As Range, c As Range
Dim uniqueID As String
Dim destSht As Worksheet, sourceSheet As Worksheet
Dim r As Long
Dim i As Integer
Dim map As Object, key, item
[code]....
View 2 Replies
View Related
Mar 31, 2008
I have two worksheets. The first contains a list of software (some having duplicate listings) in column A and a list of comments in column B. None of the software titles that have duplicates will have comments.
The second worksheet contains a condensed list of software (the previous list without the duplicates) and a column for the comments.
I need to have a formula that populates the second worksheet comments cells with the corresponding comments from worksheet one only if:
(1) The software title in worksheet two matches the software title in worksheet one
(2) The comment cell in worksheet one contains a comment (or text)
I have had only partial success; my obstacle being the duplicate software titles in worksheet one.
View 10 Replies
View Related
Sep 14, 2006
I can't seem to find the answer to this. In VBA, how do you Find a cell or row based on multiple criteria? All I need is to find it's location. If it helps, here's an example:
Sheet 1 contains source data:
Date Name Time Complete
Sheet 2 contains an interactive worksheet using this data. On Sheet 1 there's an entry: 9/12/2006 George 03:30 PM
Sheet 2 displays those records with adjacent cells for entering data. When data is entered, I need the system to go back to Sheet 1, find the entry that matches this one, and put an "x" in the Complete column.
View 6 Replies
View Related
May 24, 2008
I am trying to write/ find a macro that will open a new worksheet when Y is entered into a specific blank cell;
View 7 Replies
View Related
Oct 7, 2008
I am trying to use =MATCH to find a value in one column that matches a cell. The cell contains a formula and I want to match to the result.
At the moment it isn't working.
Here's what I have:
=MATCH(G2,$A$2:$A$2189,0)
where G2 is
=K2&L2
View 9 Replies
View Related
Feb 24, 2008
I have a userform where the textbox already pulls data from a worksheet. I have a checkbox next to the textbox, If the checkbox is true it finds the textbox value on a worksheet and using Offset it inserts "yes" in the next cell.
View 6 Replies
View Related
Feb 14, 2014
I've created a bar chart that I want to add some text boxes to. I want the text boxes to read what is in Cell A1 for example as the information in that cell will change over time.
How do I create a link between the information displayed in the text box and what's in Cell A1?
View 1 Replies
View Related
Mar 22, 2011
I need a formula or VBA macro to do the following:
I have 2 worksheets, namely sheet 1 and sheet 2.
Sheet 1 contains data in columns A to H.
Sheet 2 contains data only in column A.
I want Excel to check the data found in column A of Sheet 2 with those in Column A of Sheet 1. Hence, when an exact match is found in Column A of Sheet 1, Excel will delete that entire row.
View 4 Replies
View Related
Nov 7, 2013
I have a worksheet named "ABC". In row 4 of this worksheet, each cell from G to BG is numbered from 1 onwards and each cell has a unique number. I would like to have a macro ask the user through a pop-up question box which column data should be calculated in. For example, if the user types "1" in the question box, the macro will go to column G which has "1" in cell G4 and will select G5 the cell just below the column the user defined and calculate the formula.
View 6 Replies
View Related
Jul 20, 2014
I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:
Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say
AAB
ABC
CDE
And have a formula in column B that converts this to
=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)
I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?
View 2 Replies
View Related
Sep 18, 2006
I am using 2 criteria on one spreadsheet to identify an get a value on another spreadhsheet.
The criteria are Sex (Male or Female) cell C8 and Age (ranging from 0 -100) cell D8.
I want the formual to Look Up the Life Expectency (from the other worksheet) The worksheet has colums that contain age, sex and life expectency.
I can only get VLOOKUP to work with one criteria;
=VLOOKUP(D8,"Mortality Table"!$B$10:$E$109,5,False)
How can I find the Life Expectency utilizing 2 criterial, age and sex?
View 9 Replies
View Related
Nov 3, 2009
I am writing a macro that will use information from the worksheet in use and the one prior to it. The problem is I do not know how to reference the worksheet that will always come before the worksheet in use. To fill you in, I have a monthly budget worksheet that tracks all my monthly financials. What I like to do is compare this months balances (cash, equity, bonds) and see the amount of loss/gain and percentage from the month before.
Here is what i currently have implemented.
This macro was written in the month of October. For this function the macro I wrote will take Octobers cash balance and subtract it from Septembers cash balance. This will give me the gain or loss. Now the problem is if I were to run this macro in November it will still reference September not October. I always want the macro to use the month before (the 1st tab to the left) not a specific tab. I wrote this macro using relative references so I do not understand why it will not use the tab before. Can someone please shed some light on this for me?
View 10 Replies
View Related
Oct 18, 2009
I have a file here included which has 8 + columns. I normally have to go through each column and find when the references start and delete everything below it. If i could have a macro find where the references start in each column and delete on down that would be a huge time saver.
#REF!
#REF!
#REF!
#REF!
Thats the example of the reference i run into, i was thinking of a macro to find where the REF starts and delete that cell and everything on down.
View 11 Replies
View Related
Oct 8, 2009
Maybe it's not as complicated as i'm thinking, but i'm trying to figure out a formula (or macro) that will let me basically fill in a blank from a (nearly) irrelivant second worksheet. The only similarity the two will have is a PO number, or in my picture diagram, it's a letter. The worksheet that i'm trying to fill in the blank from has maybe 1500 entries, the worksheet i am referencing to has roughly 11,000, so sorting it would make no difference and the columns would be very inconsistant with eachother, but the rows will be similar.
I want to start a formula in Sheet one, that looks at the Letter in that same row, finds that Letter in Sheet two, and gets information from the same row but a few columns over. I might be explaining this poorly, so here's a picture of what i'm referring to. I'm trying to fill in F6 in Sheet 1 with information from F2 in Sheet 2.
View 5 Replies
View Related
Apr 19, 2012
I have a Workbook that has a macro that, as of now, is referencing the Workbook's file name. I'd like it to just be referencing the worksheet itself (it is in the same workbook) so that when the file name is changed, the macro still works.
I know which part needs to be changed... just not what to!
Code:
Sub CopyData()
Dim Cell As Range
Dim DstWkb As Workbook
Dim DstWks As Worksheet
Dim R As Long
Dim Rng As Range
Dim RngEnd As Range
Dim SrcCols() As Variant
Dim SrcWks As Worksheet
[code]....
View 2 Replies
View Related
Jul 11, 2009
I've created a macro that searches the active worksheet for a textboxvalue and copies all full and partial matches to a multicolumn listbox. However, I'd like to install some sort of filter that prevents registrations not containing the value in a combobox from making it into the listbox (so I'd actually like to search for registrations meeting two criteria, i.e. an advanced search). The macro I'm using is:
Private Sub Query_Change()
Dim vFound As Range
Dim strFirstAddress As String
On Error Goto ErrorHandle
Set vFound = Cells.Find(What:=Query.value, After:=Cells(1, 10), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not vFound Is Nothing Then
strFirstAddress = vFound.Address
I've attached the workbook I'm working on, in case I haven't made myself sufficiently clear in the above.
View 3 Replies
View Related
Jun 29, 2008
how to use VBA to write a formula in one sheet that refers to a fixed cell on the sheet to the right. The referenced sheet may have different names and it may be in a different order (i.e. not Sheet(2)) but it will always be one sheet to the right. I just want to have cell A1=(cell B2 one sheet to the right).
View 7 Replies
View Related
Apr 12, 2006
SUMIF allows one to enter a [valid] criteria text string into cell D2, say
">14000", and then reference D2 as the function criteria, e.g.,
=SUMIF(B5:B10,D2,C5:C10).
Is there a syntax that SUMPRODUCT will accept that does the same thing? Note the inclusion of a comparison operator is important.
View 9 Replies
View Related
Nov 21, 2013
I'm trying to use AVERAGEIFS to take the average of the values in a column that are > a low cutoff value and < a high cutoff value.
In my sheet, the cutoff values are calculated in two cells, and I need to refer to those values in my AVERAGEIFS formula.
Here's what I've tried so far:
=AVERAGEIFS(H2:H81,H2:H81,">B10",H2:H81,"<B11")
This gives me a #DIV/0! error.
If I replace the cell references in the criteria clauses (">B10" and "<B11") I get the correct answer, so I guess it's not handling these sections correctly.
View 7 Replies
View Related
Jun 5, 2007
In reference to an earlier thread, which shg and ByTheCringe2 both were genereous in their assistance, I would like to add a wrinkle, as requested by the end-users.
how to reference an earlier thread for people to reference but you can search " Sumproduct By Last (criteria) Month With Data". Edit by ByTheCringe2: Sumproduct By Last (criteria) Month With Data.
We had used the MAX function to find the most recent investments, per counselor, per bank. I now need to find the most recent investments as of a certain date (month-end perhaps). I have ideas, but I'm afraid my misperceptions/assumptions might hinder me from seeing all available options. Initially, I thought I could use a text or listbox on the summary sheet to enter a date that would link to the reference cell on the data page, and this reference cell would then be used in whichever formula I came up with.
View 4 Replies
View Related
Jul 4, 2014
I am completely new to Macro's and VB and the macro below has been generated using the "Record Macro" function in Excel 2013 with a couple of very minor modifications based on some research I have done (hyperlink & input box). My ultimate goal is to make a copy of my "TEMPLATE", which is hidden and the copy could have a variety of names, then create an entry in my "SUMMARY" table that references cells on the newly created sheet. The new entry on the SUMMARY page should be entered in the next available row ... at the moment I need to make sure I have my cursor in the right place before I run the macro. I also want the first cell in the new "SUMMARY" row to create a hyperlink to the newly created worksheet.
The macro does what I need it to do, as long as I name the new sheet "Test", what I would like is for the Macro to recognise the name of the new worksheet and create links to that name. The rows and columns in each new sheet will remain the same, hence the R##C## part will always work.
The "SUMMARY" and "TEMPLATE" worksheet names will not (ever) change.
View 4 Replies
View Related
Jun 9, 2008
I have a userform that has two comboboxes to search data on a sheet and a textbox to input data to a specific cell on the same sheet. The cell that the textbox writes to is determined by the combination of choices selected in the comboboxes. The comboboxes are searching as required but I don't know how to determine which cell to write my data from the textbox to. I'll attempt to clarify...combobox1(drivename) searches the data in column C, combobox2(unconformlist) searches the data in column H. The combination of these determines a specific row and I need to write data from the textbox(initials) into this row in column M.
Private Sub CancelButton_Click()
Unload Me
Sheets("Index").Select
End Sub
Private Sub initials_Change()
initials = UCase(initials)
End Sub
Private Sub OKButton_Click()
Dim ws As Worksheet
Set ws = Worksheets("QAQCconformity")...................
View 7 Replies
View Related
Jan 7, 2010
See attached a sample from a larger workbook I am working on. What i would like to do is in the Rec tab column G, keep the references from columns L & M as the Table Array and Column Index Number. I have =VLOOKUP(F:F,L:L,M:M,0), I would like to have =VLOOKUP(F:F,whatever tab reference is in column L as table array,whatever number is in column M as index number,0). I have included what I would like the data to look like in coulmn H.
View 2 Replies
View Related
Jan 9, 2014
I'm using a macro to paste a formulae into a destination cell on another worksheet.
The problem is that I need the Macro to put the name of the active sheet into the formulae
VB:
Sheets("Front").Select
Range("E1").Select
Selection.End(xlDown).Select
Range("E3").Select
ActiveCell.Formula=INDIRECT(ADDRESS(MATCH(9E+99+306,*****ActiveSheet.Name*****!F:F,1),6,4))
The macro has to work whis way becuse I will be using it with multiple worksheets, all with different names.
View 9 Replies
View Related
Mar 24, 2014
I'm looking for a macro to jump to and select a cell if it matches the text in another cell based on a drop down list. So I have a drop down list in cell c57 and a form control search button right next to it. I want the user to be able to select from the drop down, click search and then jump to the cell that matches what they selected. I would like the macro to search from B:60 - B629.
View 4 Replies
View Related
May 9, 2013
I have several textboxes that are not on a userform; they reside on worksheet A. I want to format these textboxes so that they only accepts numberical values (like 500,000....no dollar sign and no cents needed). After typing in 500,000 lets say for this example, I want to paste it into a cell in worksheet B (I can actually handle this part). What iIcan't handle is:
Formatting the textboxes so that they only accept numbers (or display a message if a number is not entered) Display the 500000 as 500,000 in the textbox without the user having to enter a comma Hitting enter or tab to get the value from the textbox into the cell in worksheet B. It only pastes it into worksheet B when I click on another cell. That doesn't seem like the right way to enter data.
I am wondering if I just should have used a userform and added the controls onto it. I have 8 sets of 5 user controls; I thought this would be easier.
View 1 Replies
View Related
Jul 25, 2014
I need the value of active x control textbox on my worksheet 1, to be copied to a textbox in my userform, that pops up from that sheet....
And I want it to display after the textbox on my worksheet has been updated and the comman button for the userform is clicked...
View 1 Replies
View Related
Aug 5, 2014
I have a worksheet with additional worksheet name listed in Column G, starting in G3.
I want to run this formula on each sheet, using the value in Column G as the WORKSHEETNAME.
=COUNTIFS(A7:A1000,">12/31/2010",A7:A1000,"<1/1/2012",F7:F1000,"Excluded")
Faulty example of what I'm trying to do:
=COUNTIFS('WORKSHEETNAME'!A7:A1000,">12/31/2010",''WORKSHEETNAME'!A7:A1000,"<1/1/2012",''WORKSHEETNAME'!F7:F1000,"Excluded")
I've tried INDIRECT, but I cant seem to make it work.
View 4 Replies
View Related