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.
So I have 2 sheets from a much larger worksheet where I wish for the first sheet to extract a row of values, one at a time from the second sheet using a range of numbers I enter as reference for where to look for the data. Sheet 1 can be thought of as a summary page and Sheet 2 is where data is stored.
I need to first check if any data under Sheet 2 column B fall within a specified range of numbers and if any of those numbers are found I want it to grab the largest and latest number from that range and pull all the data from certain columns in that same row and place it into Sheet 1, one column value at a time.
Sheet 1 A1 = A range of numbers as text. 10-15, 16-20 etc Only one range is entered. This also tells me what results I'm looking at.
Sheet 1 B4:B14 = Destination cells for the individual data I'm wanting to pull from Sheet 2 columns H:P. B4 wants H, B5 wants I, B6 wants J etc
Sheet 2 column A = A3:A102 are numbered 1:100.
Sheet 2 column B = A series of numbers anywhere from 10-70. Always in sequence and they can repeat. 10,10,11,12,13,14,15,15,15,16 etc. Ranges from B3:B102. Only one number per cell.
Sheet 2 columns H:P = Data in H3:P102 I want to extract to Sheet 1 B4:B14.
Now lets say I want a formula for Sheet 1 B4 which wants a value in Sheet 2 H. If Sheet 1 A1 = 10-15, I want to check whether Sheet 2 column B has any values equaling those and then tell me which row that last number appeared in.
Example: Sheet 2 B4=14, Sheet 2 B5=14, Sheet 2 B6=16. Use B5. (B6 is outside range and B4 isn't the last time the 14 appears.)
Then, knowing B5 is the value I want, find which row it is in (row 5 in this example) or use the number in A5 (3) and then find my way to column H (H5) where the value I want to pull is.
Example 2: Sheet 2 B4=14, Sheet 2 B5=14, Sheet 2 B6=16. Use B5.
Sheet 2 H4=10, Sheet 2 H5=32, Sheet 2 H6=42. Place "32" from H5 into Sheet 1 B4.
I'd like to also have some error control so I'm not trying to pull data from blank cells if it's relevant. Maybe check if Sheet 2 X3=0 and if it is, do nothing as no data appears if the cell is 0.
I have put a lot of time into trying to solve this myself but I feel way out of my depth. I've tried going step by step but I can't seem to figure out which functions are relevant and also things like how to return the range that the A1 values appear in or if using MAX, not having it return values outside of A1's range also.
I have managed to write a macro that almost does what I require, but it inserts the copied row at the end and I want it to insert before the row it's just copied. Any ideas how I can modify the code to do this?
If possible could someone please help with changing the below code so that column "D" in the rngIn part of the code can be a variable. i.e so the user can select one, two, three or four columns (with visible data) to be copied. The "b3" part is a constant.
View 14 Replies View RelatedI have a range of cells that I want to sum based on a range inputed by the user. The range begins with cell c27 and ends with cell au27. I want the user to be asked the beginning cell and ending cell of the range they want to sum and then output the answer.
View 4 Replies View Related I need to copy and paste the specific dates from the specific city that the user will insert in the "dash" sheet.
Basically, I need the macro to read the city and range of dates that the user will type into the "dash" sheet and copy the columns "AA" to "AD" from rows specified by the dates input. Once it has been copied, it has to be special pasted just the value onto the sheet "1" beginning at location B2.
the the data will not be available as it is vlookups to another excel sheet that i have not provided. I have deleted the data, but you can pretty much put 1's everywhere.
Cell A5 has a Yes_No pull-down list for data validation. In cell B5, I want to display the value 0.002 only if A5 is "Yes". If A5 is "No", I want the user to be able to enter a number into the B5 cell without destroying the formula.
View 4 Replies View RelatedIs there a way to prompt the user to pick an input composed of the values in a given column. Also, if there are repeats in the column, those need to be ignored.
View 9 Replies View RelatedI'm trying to create a formula similar to this: =sum(B7:B & 8*A2+3). What I want the formula to do is start in B7 and go to B###. The cell number is based on user input. So if the user enters "24" in cell A2, then 8*24 = 192, 192 + 3 = 195, so the formula would be: =sum(B7:B195). Can I do this in a cell, or do I need to run it in a macro?
View 2 Replies View RelatedI'm trying to create a formula similar to this:
=Calculations!(Indirect("N"&A2)-Indirect("AB"&A2)+Indirect("AA"&A2)
The idea is that the user will enter a value in cell A2. That number will determine the appropriate rows in the formula above.
My workbook has sevaral sheets reresenting the payment methods used by our customers. Each sheet has a range of cells F9 to Q33 which should hold the value of payments for each working day. e.g. F9 represents April 1st, F10 represents April 2nd.
A daily list of values is supplied which then transfers that day's value into cell E1 on each sheet.
On each sheet I manually have to take the value in E1 and copy and paste special: value into that day's cell e.g. today I will paste into cell K21. The cell value then looks like this '12134.12'. I then edit the cell to put a calculation on the end to divide the value by the value in another cell on the sheet. The cell value ends up like this '=12134.12/$G$5'. This is so I can see the values in thousands of pounds or by changing the value of G5 to 1,000,000 in millions.
Tomorrow I will do the same but in cell K22.
I have to do this on 15 worksheets and I have been struggling to get a macro together to do this. I can get as far as copying and pasting but I don't know how to add the calculation onto the end. I also would like to be able to input the cell destination daily probably with an input box so I can be flexible and potentially run it sevaral times if I need to catch up on previous days.
based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Code:
Dim projStartDate As Date
Dim projEndDate As Date
Dim DateStart As Date[code]....
I'm having trouble getting a User Defined formula to work. I received an email containing a User Defined formula for SUMCOLOR and COUNTCOLOR. The first sums all the values in like-colored cells, and the second counts all the cells of like-color in a given range. The VB code for the module is as follows:
View 2 Replies View RelatedI would like to alter this code so that the user chooses the column where the numbers are. They aren't always in column D....
View 9 Replies View RelatedI have a spreadsheet which links to an external source, runs a sql msquery and retrieves data based on dates selected by the user from two drop down lists. From Date and Date To.This works fine. However I also need to total any fixed data which resides in the same spreadsheet based upon the same dates selected. The end user selects 2 dates , say 15/04/2007 (this relates to a week number,week15)and 28/04/2007(week 17)
A column of data lists the week numbers (in cells A3 - A22) and next to this their respective production quantities in cells B3-B22.
I now have a problem in totaling the production quantities in the worksheet as my user is not just selecting the week numbers 15 and 17 but 15,16 and 17.
qty
week 15100
week 16123
week 1789
How do I sum from 15 to week 17 inclusive or any other range selected?
I have looked through the forum and found pits and peices but can not put the puzzle together. Found the VBA code in my example from a earlier post but there was no final answer to the post.
Trying to have the user put in a date range via command button. Fro mthis date range the data thats falls within that range is copied to a report sheet. Will also need to have all the old data from a earlier querry removed. Have attached example sheet.
I have a workbook with 2 - worksheets "DATA" and "LETTER".
The "DATA" sheet has columns "First Name" "Last Name" "Address"...etc.
The user can enter in either a "X" or a "x" in cells B6:B100 on the "DATA" worksheet.
After the user enters a X or x and hits the macro button I need it to copy certain cells from "DATA" worksheet to "LETTER" worksheet. I have listed a example below.
In this example if a "X" or "x" is entered into B6 on "DATA" worksheet and the macro button is hit, then: ....
Indicate that a user-defined function can only be based upon the calculations that can be placed in a single cell. If you have too many calculations to put them into a single cell, e.g., an entire page of calculations based upon a few starting parameters that eventually yield a single value, then how do you reuse this entire page of calculations?
Is there another Excel mechanism that allows an entire page of calculations to used as a stored procedure?
Passing Named Range into User Defined Function
MrExcel.com | Excel Resources | Excel Seminars | Excel Products mcm91201
Depending on time of day and computer I am sitting in front of I am using:
WinXP Pro SP2 with Excel 2003
Win7 Pro SP2 Excel 2007
Win7 Pro SP2 Excel 2010 on PC
Win7 Pro SP2 Excel 2010 on Mac Mini running Boot Camp
OSX Excel for Mac 2011
I have only tried this on Win7 Pro SP2 Excel 2007 but need it to work on all.
I enter the values 0, 1, 2 ... 89, 90 in cells A1 to A91
I select A1:A91 and name the range 'angle'
I create a user defined function:
Public function sindeg(value As Double) as Double
sindeg = sin(worksheetfunction.radians(value))
end
I want 'value' for the function in a cell to be replaced by the corresponding value in the same row (or column) in the named range 'angle'. For example (using commas as column separators). This works for Excel functions like sin, cos, radians, etc.
********** Worksheet Contents **********
A1 = 00, B1 = sin(radians(0)), C1 = sin(radians(A1)), D1 = sin(radians(angle)), E1 = sindeg(0), F1 = sindeg(A1), G1 = sindeg(angle)
A2 = 01, B2 = sin(radians(1)), C2 = sin(radians(A2)), D2 = sin(radians(angle)), E2 = sindeg(1), F2 = sindeg(A2), G2 = sindeg(angle)
A3 = 02, B3 = sin(radians(2)), C3 = sin(radians(A3)), D3 = sin(radians(angle)), E3 = sindeg(2), F3 = sindeg(A3), G3 = sindeg(angle)
......
A91 = 90, B91 = sin(radians(90)), C91 = sin(radians(A91)), D91 = sin(radians(angle)), E91 = sindeg(90), F91 = sindeg(A91), G91 = sindeg(angle)
Column A = input. Columns B, C, D, E and F all calculate the same value by row. Column G fails with a #VALUE. In row 1 the value of angle[1] = 0 therefore column D = C = B = sin(0) = 0. In row 2 angle[2] = 1 therefore B = C = D = 0.017452
How can I get the user defined function sindeg(value) in column G to accept the named range variable 'angle' like the Excel function radians(value) accepted it in column D?
This functionality should work horizontally as well as vertically. For example enter 'angle' A1 to CM1 then have sindeg(angle) filled from A2 to CM2. It should also work in the case where the named range 'angle' is a single cell.
I am sure that this is a simple variable type definition problem in my user defined function: should the input variable be defined as type Range? Or something more exotic?
The brute force approach is to have the function determine the input value by passing in the named range, working out dimensions, calculating offset between the cell the function is in and top (left) of named range, then counting down (right) to pick the correct value. However I cannot see adding all that code to EVERY function. Occam's Razor says there has to be an easier way since Excel built in functions seem to do it readily.
I have dates & times in column A1:A20. In B1:B20 I have the corresponding temperatures for each date. I have set up the following dynamic ranges to refer to these ranges.
DateRange refers to A1:A20
TempRange refers to B1:B20
I have also made a user defined function that will determine if a date/time is between two times. Eg., If 21/05/06 07:30 is between "07:00" and "17:00". This function isn't concerned with the date, just if the time falls between the start and end times.
Function BetweenTimes(dDate As String, dStartTime As String, dEndTime As String) As Boolean
dDate = CDate(dDate)
dStartTime = CDate(dStartTime)
dEndTime = CDate(dEndTime)
BetweenTimes = False
'If the end time is before the start time, see if date/time falls between start and end..........
i am trying to project future date by adding certain number of days, the problem is that i m trying to catch starting date(C2) based on user defined(B2) date criteria is
a. if B2 is normal working day then C2 is the next day i.e. B2 + 1 for example if user enters 1 jan 14(B2) then C2 should be 2 Jan 14.
b. if B2 is saturday then C2 should be monday i.e. C2 + 2 eg B2 = 4 jan 14 then C2 = 6 jan 14.
now the main problem part
c. if B2 is saturday and monday is holiday then C2 should be date corresponding to Tuesday or if tuesday is also a holiday then C2 should be Wednesday and like wise..
d. same for last day of leave is to be calculated similarly to starting date..
I tried lots of IF combinations but it's not working...
Trying to convert an Excel 2003 macro to work in Excel 2007.
The problem line is
Dim MyDataObject As DataObject
I suspect the problem is a Missing Reference, but I cannot figure out which one. I have the same ones (in 2007) as 2003 except for one which is not showing
Microsoft Forms 2.0 Object Library
Is this the one it needs? It is called something else in 2007?
The ones I do have ticked are
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Scripting Runtime
Microsoft XML v2.6
I am using Microsoft Excel 2010 and Microsoft VBA 7.0 on my system. I would like to eventually create a PowerPoint and insert charts generated in the Excel workbook. In the meantime, I cannot get the basic PowerPoint created.
The line in red is highlighted blue when the compile error "User-defined type not defined" message box appears.
Public Sub TryAgain() Dim myPowerPoint As PowerPoint.Application
'
' do nothing for now
'End Sub
I have set the references such that Microsoft Project 14.0 Object Library is indeed checked. The Excel file only contains this code in a module. All sheets are blank. Nothing else is written yet.
I had a working subroutine which I moved to another workbook (rather than linking to it and having both workbooks open at the same time).
Now I get "user-defined type not defined" when it runs. Here is the beginning of the
Global oApp As Object
Sub UpdateForecasts()
Call Update1
Call Update2
End Sub
Sub Update1()
Dim dPath As String
Dim dDest As String
Dim dCondition As String
Dim dName As String
Dim FSO As New FileSystemObject
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.
I'm using a worksheet as a surveying program. I need to be able to enter a cell reference in my "input cell", ie "=A11". Then in cell the the right of the input cell I need "=B11" to be entered. And in the cell to the right of that, I need "=C11", and to the right of the I need "=I11". I can get so far working with one cell at a time.
If I enter "=A11" in T3, in T4 I can enter: =IF(T3=A11,B11,IF(T3=A12,B12,IF(T3=A13,B13..and so on. But 8 conditions is all I can enter before I get a message saying that the formula uses more levels of nesting than are allowed in the current file format. And I need to be able to enter at least 50 conditions in 3 adjacent cells.
Base 64 Encoding.
Dim objXML As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMElement
Are causing the User Defined error, but the same workbook is OK in XP and Win 7.
VB6: Free, Easy and Quick Base64 Encoding and Decoding in Visual Basic [a NonHostile article]
Says you need a reference to Microsoft XML, v2.6 (or later) and the Win8/2010 workbook does have one for Microsoft XML, v6.0
I am trying to capture with Worksheet Change a command bar action like paste but I am getting an error :
“User-Defined type not defined”
Do I need a library of sort in References ??
Public Sub Right_Click()
Dim oControl As CommandBarControl
For Each oControl In CommandBars("Cell").Controls
Debug.Print oControl.Caption
If oControl.Caption = "&Paste" Then
oControl.OnAction = "MyPaste"
End If
Next oControl
End Sub
I'm trying to get a users' selected range to display in a cell as stored text.
therefore, If a user selects A1:D7. The text in Cells("G", 10) will be:"A1:D7"
w/o the quotes.
I've attached an example workbook.
Here's the formula thus far:
Sub SelectRangeBox()
Dim rnBody As Range
Dim vaMsg As Variant
Do
vaMsg = Application.InputBox( _
Prompt:="Please enter the message-text:", _
Title:="Message", _
Type:=2)
Loop While vaMsg = ""................................
Actuall maybe my thread should be Titled. user selected cell range displayed in input box save to cell as a text value.
I have searched for code in the forum to help me here but could not get any of the possible choices to work for me. I have a spreadsheet that is password protected but would like the user to be able to change the color of the fonts based on the user's choice. The protected page (one of many in the spreadsheet) has various cells that are unlocked so that the user can enter data. The rest of the page is locked. I have allowed all users of the worksheet to Select Unlocked Cells only. I have attempted to allow them to Format cells as well, but each time the program is re opened this feature is no longer working. (The program has a macro that ensures it opens in protected mode each time.)
So I would like to set up a Macro that allows the users to set the color of 6 rows and 5 columns based on their choice. An example of the configuration is:
1234Color Choice
A11016221
B21117232
C31218243
D41319254
E51420265
F61521276
Using the above format, I am imagining the I would have a Key with color codes (using the ColorIndex Properties). The user would type in the appropriate number for each row and click a button and the rows (5 cells each) would change to the selected color.