Collect Formula From User & Add To Range
Jul 7, 2007
I create a macro which ask the user to input a formula(user can select a range in the excel screen), then I copy this formula to a specific row of every worksheets.
The problem is Application.InputBox("Insert a Formula", "This accepts Formula", 0+2) can not return the formula, it will automatically evaluate this formula and return a value!
This code is:
Sub CreateNewItem1()
Dim bFormula As String
hi=10
Application. ScreenUpdating = True
' here is the INPUTBOX-method :
On Error Resume Next
Application.DisplayAlerts = False
bFormula =Application.InputBox("Insert a Formula", "This accepts Formula", 0+2 )
On Error Goto 0
Application.DisplayAlerts = True
MsgBox "You have inserted bFormula is:" & bFormula
'Cells(hi, 2).Select.FormulaLocal = bFormula
' MsgBox Cells(hi, 2).Select.FormulaLocal
Call CreateNewItem2((hi)) 'call the copy formula macro
End Sub
View 7 Replies
ADVERTISEMENT
Jun 27, 2008
I'm getting a cell address from user. I need to check whether the cell address is a valid cell address.. How do I do it.
Dim s As String
s = InputBox("Enter a valid cell reference")
If (S Is a valid cell reference) Then
MsgBox "success"
Else
MsgBox "Failure"
End If
I tried doing this using Left and Right string functions. But thats going very complex.
View 3 Replies
View Related
Dec 8, 2006
Date = InputBox("Enter the production month date in YYYYMM format", "DATE") 'This is the only info I want them to enter
Windows("PCN4.xls").Activate
Sheets("Bring the data in here").Select
Range("A" & NextLine).Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;W:C30MonthlyMonthly NEW TCs200610 otconsa.txt", Destination:=Range _ ' I only need the 200610 to change every month
("A" & NextLine))
.Name = "totconsa"
End With
View 9 Replies
View Related
Sep 9, 2006
I have an existing macro which I am enhancing and I would like to have the user provide a date, either with a popup text box in a userform and then use a command button to hit OK and have the box dissapear, or in a calender which the date is selectable, and the date supplied go into a variable.
View 2 Replies
View Related
Aug 31, 2007
want to do something simple, and it just wont paste. not sure why. anyone care to look it over and tell me what i'm doing wrong here?
'Insert Date Information
Dim vDate As Date
Range("D2").Select
ActiveCell.FormulaR1C1 = Date
vDate = Date
'Get date from user
Dim dDate As Date
vDate2 = Application.InputBox(Prompt:="Type in the due date for the location." _
& Chr(13) & Chr(13) & "*If you want the date to default to " & Date + 5 & " then leave the field blank.", _
Title:="Due Date", Type:=1 + 2).............
View 3 Replies
View Related
Jan 29, 2008
I have an input box for a password, and my vb code checks the password against a list, and if it is correct allows the user to continue. I want to change the input box for a user form, as it allows more text on the form, and I also want to include a checkbox that the user must tick to show that they have read and understood the instructions. I have defined the form, but am having trouble getting the form to interpret the user input.
The form has one checkbox, which needs to be ticked to continue, otherwise give a message saying that you have to confirm. It also has one textbox, for the user to input the password. I dont understand how to name the contents of the text box, and use it in the following code. Also do I need a "send" button on the user form, for the user to click after entering the password. If so how does that work?
View 4 Replies
View Related
Jun 21, 2008
We have an excel file which is made every thursday for comparison purposes etc. I've made an macro (excel 2003) which - on start - asks via an inputbox for the extension in date format for saving purposes. Later on, the macro will search for the same file but from last week to open it and copy/paste a 'comments' column into this new one. All went well, everybody happy. However it may be well possible that file is made and saved but with an extra extension: example:
dir to save = C:file
file when starts runs inputbox "Please enter a date (e.g. 2008-06-20)"
The macro records this string as mydate:
Sub Start()
Dim AnyString
Dim MyStr
Dim DirString
Dim mynum 'As String
Dim resp As Long
Dim get_mynum
Dim mydate
'Define extension for the file name to be saved and the correct path (dir) where this file will be stored.
get_mynum:
mynum = Application.InputBox("Enter the filename's extension to save in yyyy-mm-dd (e.g. 2008-06-30)", vbOKCancel)
If mynum = False Then
msgbox ("You do not want to continue? Ok, programm stopped")
Exit Sub
ElseIf mynum = "" Then..............
View 9 Replies
View Related
Feb 18, 2009
I'm working on a spreadhseet which has several macros that can take up to 5 minutes to run. When the macro(s) complete, I pop up a userform to provide instructions and collect some additional data from the user.
The "problem" is that when the user moves to another application to do other work, the user form remains hidden behind the active app. I've added some text to the status bar to let the users know the processing is done, but they would like something more obvious - so, is there any way I can force focus back to Excel?
View 2 Replies
View Related
Apr 4, 2008
I have a cell B10 which contains a formula.
I would like a macro which copies this formula across the same row.
However, the macro should prompt the user to select a range.
Based on the range selected and in particular the columns in this selected range, I would like the macro to copy the formula only for the columns specified in the selected range.
View 9 Replies
View Related
Dec 17, 2013
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
View 2 Replies
View Related
Dec 30, 2006
I bought 32 laptops out of my own moeny (used) to get my 5th grade students into the 21st century. I have several programs working on these laptops which output data in text and xls files. I would like to either:
1. Poll the files on each workstation at the end of the day and bring the data into excel (I have worked in VBA). Computers are named Comp01 to Comp33.
2. Better: have the workstations save the files to my NAS device so they are on one hard drive.
I would like to keep it simple (I'm a teacher not a good programmer.) Is the "Import External Data" the way to go for the text files? Can a macro go out and loop through the 32 laptops using that or some other add-in?
View 10 Replies
View Related
Jul 26, 2008
I have a workbook with multiple worksheets and would like to use a macro to do the following:
1. get data from cells: c2, m29, n29 & o29 in individual worksheet
2. enter in summary sheet
3. do the same for next worksheet and put info on next row
View 12 Replies
View Related
Feb 21, 2010
I'm importing data from the internet. The cell I'm trying to get is B8 on "Temp_Data" onto cell E3 on "Other Data". When it imports the cell into Temp_Data for some reason the cell expands, and I can't have that. I would manually copy and "Paste Special", text, into the proper cell, and that works. I can also copy the fist 7 letters in the string from B8 and paste them into E3. My code currently looks like this:
View 2 Replies
View Related
Nov 25, 2011
I have a need to collect cells from different worksheets based on several criterias.
The worksheets all have one column and cell value in common, "Project name".
I need to collect some cells from different worksheets and visualize them on a new worksheet.
The cells I want to collect is:
- Project name
- Status of Project
- Income
I have been able collect these data. But the trick is that I need to sort these collected data on "Status of Project". The status of a project can be one of three: Committed, Uncommitted or Unspecified.
So what I need is three bulks, something like this:
HTML Code:
Committed
"Project name" "Income"
"Project name" "income"
Etc.
Uncommitted
"Project name" "Income"
"Project name" "income"
Etc.
Unspecified
"Project name" "income"
"Project name" "income"
Etc.
The point is that this can be done manually, but to have a macro doing this would obviously save ridiculous amount of time for me..
View 9 Replies
View Related
Jul 11, 2007
Is there any way to force some sort of garbage collect in order to free up memory? I am running some code which takes values from one workbook and pastes them into the control workbook. It currently works when 15 sheets of data are being moved however, my data has just grown to 16 sheets and now the code gives me the not enough resources message. If a garbage collect would solve this issue would I need to also reset any variables I had defined in the code?
View 9 Replies
View Related
Jun 1, 2008
I have 2 excel files(X and Y), and I would like to collect data from file X to file Y.
.................
In file X, I have a products prices lists that I got from my supplier. It look like that:
column:
A = LIST NUMBER (NUMBER)
B=ITEM NUMBER (TEXT)
C=DESCRIPTION (TEXT)
D=PRICE (TEXT OR "CALL" OR PRICE LIKE $90.00)
E= LIST NUMBER (NUMBER)
F=ITEM NUMBER (TEXT)
G=DESCRIPTION (TEXT)
H=PRICE (TEXT OR "CALL" OR PRICE LIKE $90.00)
Rows of A,B,C,D,E,F,G,H start from 1 to 400
..................
In file Y, I have 1 column and it looks like that:
column:
A= MODEL NUMBER (TEXT OR NUMBER)
..................
The calculation I would like to do is:
pass in each ROW of Coulmn A in file Y and search if there is the same MODEL NUMBER (TEXT OR NUMBER) in file X at ROWS C,G (The DESCRIPTION (TEXT)) for example:
If in file Y Column A1 = FG45673 (MODEL NUMBER ) the macro will look in file X in Columns C,G to see if it finds this MODEL NUMBER. It can find text like: "3 pcs of FG45673 " so it means it find it !
THEN the MACRO should copy from the price Columns D or H to Column B of file Y.
In other words to collect the products prices from file X according to their model number field and to copy it to the same MODEL NUMBER of file Y.
in file X there are 2 lists of products prices as you can understand from my info.
View 9 Replies
View Related
Aug 13, 2009
I am trying to use VBA to copy data (Invoice Process information from a few different excel worksheets and paste it all onto one master sheet). There are probably 10 sheets and each sheet will contain varying rows but a fixed numbers of columns. One sheet may have 20 rows the next 50 then next 75 and so on.
Batch# PO# Invoice serial# Process details Skip reasons Processed/Skipped
With a formula, I don’t know how to read each page and paste into a master sheet.
View 10 Replies
View Related
Dec 29, 2009
I've been trying to familiarize myself with MS Excel's Web Query function (MS Excel 2007) in order to collect statistics from a baseball game.
Here is this particular page I'd like to start pulling certain data from: http://www.mmobaseball.com/stats.aspx
I can get the first page of data easily, the first 50 or so players and their stats, but I cannot get anything from the other pages (which are accessed by the 'next button') nor can I get the defensive stats of any player (accessed by clicking the button labeled "defensive stats"). When I look at the page source I see that regardless of the page I'm viewing, excel only pulls data from the original page.
How can I collect the data from these other pages?
View 9 Replies
View Related
Mar 4, 2014
I have attached my particular spread sheet I an referring to.
I set up a spread sheet to write out all the material I buy to go into a product I make. I work my way through a layout diagram and type all my data in to a spread sheet, one row per item I need to buy.
Now I want to order everything. Is there a way to sort the spread sheet so it brings all my like items from Column A (item) together, so I Can count the quantity required to order?
I have tried to sort, but am worried I might be messing up the rows and column - all the row information must stay together for it to work.
567 BOM REV 0 - 16 12 2013.xlsx‎
View 3 Replies
View Related
May 13, 2009
we log all customer communication on a daily spreadsheets a report. Multiple projects are listed on that one sheet every day. Is there a way to extract project related communication to a individual project log adding the date from the daily report to the log. The goal is to generate for each project a log will have the daily (or any) communication for one project from the daily report. either adding a work sheet via project name or a separate file. Also is there is a new project a new project log should be created.
View 4 Replies
View Related
Jun 9, 2009
I have two worksheets: "1" and "2" Now i want to filter all symbols used in Column A sheet "1" and list them in Column A of sheet "2". The results should contain no duplicates. For example:..........
View 5 Replies
View Related
Nov 13, 2008
I'm trying to make a spreadsheet that looks up values for a number of individuals and if they meet certain criteria they are collected in a box.
It is for a class of children's test results. I would like to be able to write the results each child got across the page (names at side, headings across top). If a child gets, say, level 2a in his test, his name appears in a cell elsewhere. I can do this with an 'IF' statement... Thing is I want to collect all the children who got that level in the other cell. The cell might end up with 10 or 12 names in it as a result of their scores being looked up. I would have cells for all of the levels, so the children can be automatically grouped together by attainment.
View 10 Replies
View Related
Nov 20, 2011
I have a spreadsheet here that collect information through vb, now when the first agent is done with the information needed and click on the save command button the information is saved in the spreadsheet.
For example:
A | B | C | D | E
1 Date | Name | Birthdate | Gender | Age
2 11/20/11 | Greg White | 04/12/1977 | Male | 34
3 11/20/11 | Greg Blue | 04/12/1977 | Male | 34
4 11/20/11 | Greg Green | 04/12/1977 | Male | 34
5 11/20/11 | Greg Black | 04/12/1977 | Male | 34
Now theres no problem with this instance.... the problem arise when the second agent do the same thing..... when i check the spreadsheet the information was overwritten.
What really should be happening is that when the first agent click the save command button the spreadsheet will be saved so that when the next agent click the save button it will go to the next line.
Another thing that i noticed, since the spreadsheet is shared through the network, it takes time before the information is saved.
1. When Save button is clicked the spreadsheet will be saved.
2. A code (if there is any) to speed up the saving of shared excel file.
View 2 Replies
View Related
Jun 4, 2007
I want to select a cell that contains a job number, use the text from that cell as the new file save as name (got the file creation part sorted out) and then continue from there to go back and create a hyperlink in the original cell to the newly generated spreadsheet. I know it involves variables, have tried things like currentcell.value but can't get what I want.
View 9 Replies
View Related
Oct 18, 2007
I was wondering how I would go about witting a vba for that can be connected to a button which would allow me to enter a row of input.
Some of these values I would like entered for example are: Quantity, Shape, Weight, length....
I would like the user to be prompted to enter these values and then have these values entered into columns, ABC... in a row on the spread sheet.
The catch is I would like this new date to be entered on one sheet via its weight (so that lightest material is on top), on another sheet in this workbook entered via its location so material in the same locations are adjacent to one another, and then finally I would like this updated onto another worksheet.
View 3 Replies
View Related
Dec 3, 2008
I have a form that asks at what row the user wants to start with a selected range then how many rows to select. There will always be 21 columns selected with this range. I am still learning but it has to do with something on how I am setting the Start object. It is not being recognized.
Dim Row As Long
Dim Selection As Long
Dim Start As Range
Row = txtStartRow
Selection = txtHowMany
Set Start = Range("A" & txtStartRow).Select
Range(Start & ActiveCell.Offset(Selection, 20)).Select
View 9 Replies
View Related
Jul 14, 2006
This code works fine if i use static ranges, but i would like the user to input the column in which the data is stored.
'Get data for the locations of the gaming license numbers needed for the comparison
varPlayerHost = InputBox("Please enter a single letter for the" + vbCrLf + _
"Column that the Player Host License" + vbCrLf + _
"numbers are in.", "Player Host Number Location", "H")
varHostLicense = InputBox("Now enter the column letter for the copied employee" + vbCrLf + _
"license numbers", "Employee License Number Location", "U")
'Set the ranges for the data to be compared
'This is where I am getting the error???
Set rRangeA = Range([varPlayerHost,1], Range(varPlayerHost, 65536).End(xlUp))
Set rRangeB = Range([varHostLicense,1], Range(varHostLicense, 65536).End(xlUp))
'The actual comparison and deletion of record that match the license numbers copied.
'When this function finishes you will be left with a spreadsheet that only has patron
'information left for the patrons that have an invalid host number.
For Each rCell In rRangeA
If WorksheetFunction. CountIf(rRangeB, rCell) > 0 Then
rCell.EntireRow.Delete
End If
Next rCell
This exact code is kicking an error
"Run Time error 1004"
"Method 'Range' of Object '_Global' failed"
Not quite sure where i went wrong.
View 9 Replies
View Related
Jun 9, 2014
I have an excel file that contains around 8 sheets, each have a table that contains data. I want 2 master sheets that can automatically update itself if i enter data in any 1 of the 8 sheets.
master sheet 1 = summarized sheet that contains lesser columns with only major details.
master sheet 2 = contains all columns of all the 8 sheets.
the 8 sheets have unique names and i want those names to be in a drop down list in master sheet (summarized ) so i can select which ever sheet i want summary from.
Also i used tables for filtering data as i find it easier to track records from filter.
View 2 Replies
View Related
Feb 12, 2013
I have a question around making a summary table from a model. I will try to explain this as plainly as possible.
I have a model where you can toggle 3 different inputs across two variables. The final output number of the model will vary accordingly.
I would like to create a 3X3 table with the final output for each combination of variables. Is there a way of doing this using formulas or would I need a macro?
View 2 Replies
View Related
Feb 15, 2014
I have a pivot table with slicers. I would like to collect into a cell all the item names that have been selected (filtered). (through VBA or Formula) For example if I select A, D, and E into the (A, B, C, D, E, F) list. I want the labels A, D, E to appear in a cell.
[URL]
View 1 Replies
View Related