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

Reference Cell For Worksheet Name Macro Code

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

Macro To Copy Data From Columns And Paste In Rows After Given Criteria Matches?

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

Populating Cell Only If Multiple Criteria Matches ...

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

Macro Code: Find Cell Or Row Multiple Criteria

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

Insert/Add New Worksheet When Cell Value Matches

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

To Use =MATCH To Find A Value In One Column That Matches A Cell

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

Find TextBox Text In Worksheet If CheckBox Is True / Checked

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

Linking Textbox In A Chart To Cell Reference

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

Delete Row If Specific Cell Value Matches Value Found In Another Worksheet

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

Macro To Find Variable User Defined Cell In Worksheet

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

Convert Cell Reference To Worksheet Reference

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

Multiple Criteria To Find A Value On A Worksheet

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

How To Reference Another Worksheet In A Macro

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

Macro To Find Reference In A Column And Delete Everything Below It

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

Formula/Macro To Reference From Other Worksheet

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

Workbook / Worksheet Reference Macro?

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

Fill ListBox With TextBox Matches While Excluding Those Matching ComboBox Choice

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

Macro Code To Add Reference On Sheet To Next Worksheet

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

SUMPRODUCT Criteria Via Cell Reference?

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

Averageifs Using Cell Reference In Criteria

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

Sumproduct By Reference Cell As Criteria

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

Excel 2013 :: Macro That Can Reference Worksheet It Has Created

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

Find ComboBox Value & Enter TextBox Into Corresponding Cell

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

Vlookup:keep Cell Reference As Lookup Criteria

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

Macro - Paste Formula Into Another Worksheet Using Active Sheet Name As Reference

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

Macro To Jump To Cell That Matches Text Selected From A Dropdown List?

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

Type Value Into ActiveX Textbox Into Cell In Another Worksheet

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

Copy One Value Of Textbox ActiveX On Worksheet To Userform Textbox

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

Using Cell Value To Reference Worksheet Name?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved