I'm trying to make part of a macro that will find user input.
User starts macro, is prompted to enter a date, and macro will find the date.
Problem is, the code I've used does not want to work for me.
I've tried Dim/Set, For Each/Next, and the below:
[Code] .....
Any way to determine which is the best selling single of that week and print a 1 in the row associated with that CD.
View 1 Replies View RelatedDue to the economy we are only scheduling days to work one week ahead which leads to alot of phone calls. The gaurds who get the calls then search thru a printed spreadsheet looking for a clock number or last name. I realized right away they should just look at the file on the computer and due edit - find. The problem is they due not use computers unless printing and they looked at me like I was talking in a different language. So I want to edit this macro below so it will allow user to type in search criteria instead of using same search criteria every time. Here it is 70039. I also need to place a shortcut or button to this macro on the toolbar or sheet and do not know how. How do I stop "remove macro or enable when file is loaded"?
View 3 Replies View RelatedIf I have 2 sheets names 'User Info' and 'Labels' what i'm looking to do is on the 'Labels' sheet under cell J2 to be able to enter a number, 505 in this case which searches for this number under column B of sheet 'User Info' finds it, looks to the entry in column D (the name Ken) and in turn puts the name into cell J2 in sheet 'Labels'.
The real life use for this is to search a phone extension number in User Info sheet, and use the user name at that extension number and place it in a label on the Label sheet for a reception phone. The Reception phone can have multiple extensions on it made up of any 3 digit number on any where from 3 keys to 12+. So the ability to search for 100-999 is needed.
I have a range (C3:C56). I have a set number in each of these cells. I also have a range out to the side that calculates a number after input a % in cell BI2. The formula reads as "=AE3-(AE3*$BI$2)". Basically it is reducing the values in my range. If a number is in my range is 8 and i input 25% in cell BI2, then my result would be 6.
I have a few steps I want to happen with this range.
1 Display an input box asking for a percentage
2 I want to loop through my range and use that percentage to reduce the numbers
3 (optional but would be amazing to have) after the reduced value is determined, round down to the nearest multiple of 4.
have a range of dates in a column i can match an input date with this code where the input date is in cell H20 (eg 04/04/2008)
With ActiveSheet
Range("D:D").Select
Range("D:D"). Find(What:=Range("H20"), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End With
the problem is that not all the dates in the range are consecutive and there may be the same date more than once eg
01/04/2008
03/04/2008
03/04/2008
05/04/2008
the problem is if the date in cell H20 (inputted by user) does not exist in column D, it all goes wrong.
what i would like to achieve is a way of incrementing the cell date by one day until it matches a date in the range, in this case if 02/04/2008 was entered 03/04/2008 would be picked up.
I am trying to do is create a button that when pressed, will bring up the Find screen (which can be brought up by pushing ctrl + f)...I tried to record it as a macro but nothing came up.
View 9 Replies View RelatedI am trying to write a match function to return the row of the value I search for. It is properly returning the value of the row I searched for. What I need to know is how do I get it to spit out information in the rest of the row. Here is my code so far:
Sub Rectangle2_Click()
Dim x As Long
Dim Row As Long
' Brings up input box
x = InputBox("Please Enter Tool Information", "Search")
'Exits if nothing is entered in the text box
'If x = "" Then Exit Sub
'MsgBox (SearchString)
'SearchString = x
MsgBox (x)
That gives me a Type mismatch error when I uncomment it out.
Something I was adviced of before in a situation like this way to Concatenate the columns, e.g
=A2&B2&C2&D2&E2&F2
in a far left column (insert 1 if needed).
That was supposed to be so that I can search through multiple columns. When I do that to test it, it never comes up with results. It always comes back as 0 meaning it wasn't found.
I am trying to find a value in an array based on partial input from a user.
The user will enter the last four digits of a number.
Then the formula should find the number in a list and return that complete number
I will explain using example.
Here is the list of numbers (array):
3003280197
3003283233
3003625456
User input= 3233
formula output = 3003283233
i have the following code, what it does is, it locate those empty cells in column M and insert the formula "=TODAY()". What i need the code to do is only insert to the empty cells in column M if there is a value(as long as is not empty) in the reference cell of column E.
VB:
VB:
Private Sub CommandButton3_Click()
Dim wks As Worksheet
Dim rng As Range
[Code].....
I want to enter a unique ID into an input box which will tell which row that id is available and then it will again ask me which column the cell needs to be highlighted. Once i enter the data, it will then ask me what is the change in data (again via input box). i can then enter the change data and then it stops.
View 1 Replies View RelatedWhat I have is an output of about 30 worksheets of individual stock data in a workbook, all with the same column headings, laid out like the following (so that the Column Headings are stock data as recorded on the Date in Column A):
<Stock Name>
<Date> <Price> <Price to Book> <etc.>
with the stock titles also as the names of the worksheet.
What I want to do is create a macro that outputs to a single worksheet where I can input the stock name and a certain date for the stock, and then have it insert the appropriate row from the stock's worksheet underneath.
So, if I say I want the data for Stock A on March 24, 2007, I can input those two and have it output the appropriate rows from the worksheet.
Or, more generally, I can create a list of stock names and dates, and have the macro output that data underneath.
I've created a userform that has one ComboBox (ComboBox1) and two text fields. I am trying to get the userform to return information to my worksheet in the same row as the name that is displayed in the ComboBox. This is my VBA code.
Private Sub Cmdpayment_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Sheet4
iRow = Cells. Find(What:=Me.ComboBox1.Value, After:=C5, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ws.Cells(iRow, 12).Value = Me.txtpdate.Value
ws.Cells(iRow, 13).Value = Me.txtpayment.Value
Me.txtpdate.Value = ""
Me.txtpayment.Value = ""
End Sub
Using a pivot table, I'm linking to an external data source in ACCESS. I use the pivot table wizard, select the database (spend2008.mdb), and then ONE table that is not in a relationship/linked etc, its just a plain table, and then I get the error
"The Microsoft Office ACCESS database engine cannot find the input table or query "mdb.summary". Make sure it exists and that its name is spelled correctly".
This is new spreadsheet, so new join to the data. I'm selecting the table from the Query Wizard, so not possibility of a spelling mistake. I've been all through the database to make sure I haven't set a link or relationship. There are a total of 4 tables in in the DB that are related, but I've deleted the relationships
I have Quote Master.xls open
I have a value in AA1 that carries a number
I want to open Quote Log.xls
I want to find the cell in Column A (Quote Log.xls) that AA1 (Quote Master.xls) directly corresponds to
I want to offset from that found cell 3 cells to the right and insert from T7 (Quote Master.xls) into this offset cell. Here is the code so far
Private Sub InputIntoQuoteLog7_Click()
Dim CostSheetBook As Workbook
Dim QuoteLogBook As Workbook
Set CostSheetBook = Workbooks("Quote Master.xls")
Set RFQQNumber = CostSheetBook. Sheets("RFQ").Range("AA1")
Set RFQQStartDate = CostSheetBook.Sheets("RFQ").Range("T7")
Workbooks.Open ("\ACT3ENGVAULT EngineersLsheriffDocuments (2008)Quote System MashQuoteLog.xls")
Set QuoteLogBook = Workbooks("QuoteLog.xls")
Set vOurResult = .Find(What:=RFQQNumber, After:=[A1], _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)
vOurResult.Value = RFQQStartDate.Value
End Sub
I get an un-numbered error on the .Find
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
I would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
I have to design an input user form (a button that opens an input box or dialog box), which asks user to "Enter number of "columns"".
User enters a number i.e. 2 or 3 etc
It then asks user for the headings of these coulmns and places first column heading in A1 and second column heading in B1, third in C1 and so on ...
I am just starting to get my feet a little damp with VBA and I am trying to make a macro that will act whenever any text is entered in any cell within a 3x3 square. Whenever any text is entered in any of those cells I would like it to enter a text ( "X" ) in a cell which is specified by a cell in the spreadsheet (this cell will output which cell the macro should write the text in in this format "A:1" or "C:3"). How can I make the program act only in reaction to the user entering thier text, and also, how can I get the macro to read A:1 and enter the text into that specific cell? Any help would be so awesome, the book I have is really difficult to navigate.
View 9 Replies View RelatedWhat I am trying to accomplish....
If the input changes in D5, K11 will reset to $0.00..
Example. If cell K11 is set to $300.00, and the corresponding input in D5 is '123', when I change the contents of D5 it sets K11 back to '0.00'.
I am in need of VBA code so that I can fill out an input form generated by someone elses macro input box. (I am the user, not the one developing the input box).
I am working to streamline a whole cluster of macros and this input box is currently my bottleneck. When the input box pops up, instead of me manually typing, I want to autofill the space with "104" and then click 'ok' uisng VBA.
I can not modify the actual VBA that created the input box (ie make 104 the default) because other users need the box to be blank.
find out how a developer would change the original vba code, not how a user can interact with an input box by using vba.
I want to create an input box that will allow you to input the column number it should start in... so instead of it being fixed at A, you can choose column B, C, D all the way to the end if you like.
Sub SplitSheetsNoDelete1()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Lmainsheet As String
Dim Lrow As Integer
Dim lcontinue As Boolean
Dim LColamaster As String
Dim lcolatest As String
'Retrieve name of sheet that contains the data
Lmainsheet = ActiveSheet.Name
'Initialize variables
lcontinue = True
Lrow = 2.....................................
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
View 4 Replies View RelatedAttempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub
DSee the attached.
The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in current orders tab.
BUT.....If two salesman pair up on up on a job then the formula doesn't recognise the joint name. eg Gary/paul in row 69 (current orders).
I need the totals to half the job and add it to the salesmans total accordingly. There is no 'Paul Tab' as he is our MD and doesnt have a target.
i have spent 40 hours, and still didn't find a solution. Please help is need it!!
example:
i have to find all articles with same code (222). first one has Q =100 second one Q=250.
soled Q=150
(i am talking about 5000 rows, 400 different or same articles per month- 12 months)
columnA-----columnB---columnC
222 ----------100---------0
111-----------50
333-----------70
222----------200---------200
333---------- and so on
first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the rest---it means 50
Is there any kind of formula with this possibility.
if it is poorly writen please let me know for more info.
i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
I'm using the below VBA
Dim c, DataRow
With Data
Set c = . Range("A5:A350"). Find(KPI, LookIn:=xlValues)
If Not c Is Nothing Then
DataRow = c.Row
End If
End With
Now,If KPI is for example = "Favourite Hotel" and if i have data that looks like :
Favourite Hotel - London
Favourite Hotel - Cardiff
Favourite Hotel
Favourite Hotel - Birmingham
Then it seems to not look be looking for an exact match (e.g. Favourite Hotel) and rather is finding the first item in the list that contains the KPI string (E.g. Favourite Hotel - London).
How do i make it search for an exact match?
I am maintaining a program which reads through a pre-prepared CSV file using Input #.
The first line of the CSV file contains field names, one of which has recently been changed from "Protection Type" to "1st Protection Type". For some reason VBA simply returns the number 1 instead of the full string.
I find this strange, since within the subsequent data lines there are many alphanumeric fields which begin with numbers and they are all read correctly.
Before I start making wholesale changes to the software (plan is to either use Line Input # and parse each line manually, or import the entire CSV file and move the data using an array) does anyone know if there is a way around this behaviour?