I have a macro in which I have a string variable named RangeName. I use this variable to select a range of data to copy into an powerpoint slide. However I cant select multiple ranges of data to copy. For eg. when I type:
View 11 RepliesI have four ranges of data in a worksheet that are sorted in order by the value in the final columns. Based on this value I would like to flag the highest five values from all ranges by adding a value in the adjacent cell. Is there a formula/function that I can use to identify the highest values from a number of data ranges?
View 9 Replies View RelatedI'm trying to get multiple seperate ranges selected but Excel interprets it as criteria even when I use Insert> Function. The problem probably comes from the use of commas in formulae. Example:
I want A1:E1 and A3:E3 and A5:E5 to be the range involved which leads me to construct:
= COUNTIF(A1:E1,A3:E3,A5:E5...
However, Excel interprets the 2nd and 3rd values after the commas to be criteria or something else.
I am using Excel 2007 and the following code is generating an error 1004 (Method 'Range' of object '_Worksheet' failed) on the .Range select line. I am trying to select a bunch of noncontiguous ranges and then format them all at one time.
Code:
With Sheet1
.Range("B9:G9,B10:D11,E10:E11,F10:G11,A13:G13,A14:D20,E14:E20,F14:G20,A22:H22,A23:D24,E23:F24,
G23:H24,A26:H26,A27:D28,E27:F28,G27:H28,B30:G30,B31:C32,D31:E32,F31:G32,B34:G34,B35:B36,E35:E36,C35:D36,F35:
G36,B38,B39:C40,D39:D40,E39:F40,B42:G42,B43:D50,E43:E50,F43:G50,A52:G52,A53:C54,D53:D54,E53:G54,
G61:G62,H65:H66,A56:H56,A57:H60,A61:F62,A64:H64,A65:G66").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With
I've written longer lines of code, so I don't think it's a line length issue. Is there a limit to the number of ranges or cells that Excel can select at one time?
I have a macro that has automated 95% of a task but one thing is still evading me! If I use the keys then this is what I do:
Select cell C192
Select Shift and R-Arrow so that C192 and D192 are selected
Select Ctrl, Shift and Up Arrow so that C1:D192 are selected
Select Shift and Down Arrow so that C2:D192 are selected
I have two named ranges, LV1 and LV2 and wish to select them for printing. If the ranges were fixed I would use - Range("A2:H4,A8:H10").Select - how do I do the equivalent using the named ranges in vba?
View 2 Replies View RelatedI am writing a UDF to find the average of two ranges, which start at the StartDate and then go back a certain number of days, that are on another sheet. When I use Union to try and unite my two ranges the resulting range just equals the first range.
VB:
Public Function ModifiedAverage(StartDate As Variant, SheetTenor As String, History As Double)
Dim DateRow, NbCols As Double
DateRow = Application.WorksheetFunction.Match(StartDate, Sheets(SheetTenor).Range("A:A"), 0)
NbCols = Application.WorksheetFunction.CountA(Sheets(SheetTenor).Range("6:6"))
[Code] ....
I run a macro on a spreadheet in which the rows increase daily. In the macro I simply recorded an end.down to select a column of data which ends up being fixed row numbers in the code so the next time I run the macro it only selects the range I selected last time.
View 1 Replies View Relateddoes anyone have any suggestions for selecting (highlighting) all the named ranges on a sheet?
View 9 Replies View RelatedI have a list of data that displays data by day, I want to select only the data for the for the current month and then the same day time frame for previous months in the data set.
E.g.
Today's date 08/07
Full days into month 7
Formula to sum data in columns B that only looks at dates 01/07-07/7
I will the adjust that formula for a January date that only pulls data for 01/01-01/07
Then repeat for Feb-June
I have variable length strings, as shown in the sample cells below
TopographyTAB_FormatSP9200
TopographyTAB_FormatSP9400
OS_DataLandline
ITNTileTabTQ2286
I want to select any record where 5th and 6th character from the right hand end of the string which = either "SP" or "TQ". Where this is the case I'm then looking to truncate the string to remove the last 7 chacters.
None of what I have so far found using mid or right string appears to be particularly applicable.
I need separating parts of a string of text to go into separate columns.
Fortunately the spreadsheet is in the same format in relation to spaces and numbers of characters with the exception of the red text which can be any where between 2 and 50 characters.
I actually need AL46 to go to Col B, 001488616 into Col C, Valve, Pressure Equalizing, Gaseous into Col D and D03079/0002 into Col E.
(65) AL46 ALSPO 0299999999 A20LS DISPOSALS.(001488616) Valve, Pressure Equalizing, Gaseous.D03079/0002
I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.
Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):
VB:
Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String
StringReturn = Str1 & Str2 & Str3
End Function
Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3
I've got several worksheets that all have the exact same layout that a user will enter unique information in to each worksheet. Then I've got a final worksheet that I want to have a button that the user can click and when they do, it will look to each worksheet and do the exact same process for each worksheet as follows:
It first looks to see if the worksheet is visible. If it is, I want it to copy the range A5 to K5 down until it gets to the last non-blank cell in column C. The first non blank cell that will be referenced will be C7. Then I want it to paste this information into the range A5:K5 on the final sheet named Sheet8 with the same values and keep cell formatting such as width and height, font. If the worksheet is not visible, it skips the sheet.
I want it to do this for each visible worksheet, placing the next visible worksheet info under the previous visible worksheet info. My current code as shown doesn't do that. It requires that something be inSheet8 A6 before it will even paste, then it pastes the info from A5:K5 but it doesn't do just the values nor does it keep the formatting. What I mean about not doing just the values is some of the info that needs to be copied comes from a drop down they can choose from and it copies the actual drop down menu. Also, it seems to copy all of the ranges from each sheet and paste it into just A5:K5 on Sheet8 and overwrites each other instead of pasting Sheet2 just below the information from Sheet1. So the only information shown after the entire process is completed is the information from the last visible sheet.
If Worksheets("Sheet1").Visible = True Then
Sheets("Sheet1").Range(Sheets("Sheet1").Range("A5:K5"),
Sheets("Sheet1").Range("C7").End(xlDown)).Copy
Sheets("Sheet8").Range("A5").End(xlDown)
End If
[Code]...
I am trying to select multiple rows so that i can sort. The code i have
View 2 Replies View RelatedI am trying to copy rows to a new sheet that meet a couple of conditions.
They have to meet condition 1, 2, or 3 and conditions 4, 5 and 6. Meaning have any of those search terms listed in columns L, M, N columns, but no terms in columns I, J, or, K.
Sub filter_data()
Dim rng As Range
Dim header_pos As Integer
[Code]....
I need to select the record that has the highest number in column C by using an input value like (red).
desired Result : red, gt3, 100
A..............B...............C
red..........rt2.............12
green.......rt3..............4
red..........st2..............33
blue.........st4.............45
green.......st1.............44
red..........gt1............100
blue.........ft4..............85
Is it some variation of the MAX function?
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click()
Sheet7.Activate
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.RightFooter = " Construction Assumptions"
.PrintArea = "CONSTRUCTION" 'the named range to print
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1 'this changes depending upon the page selected
End With
ActiveSheet.PrintOut
End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate
'Sheet3.ResetAllPageBreaks 'disabled due to errors
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.PrintArea = "DEVBGTALL" 'the named range to print
.FitToPagesWide = 4 'this changes depending upon the
.FitToPagesTall = 1
End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
I need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.
is there a way to track changes on multiple selected ranges on multiple sheets
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View 4 Replies View RelatedSo I am creating a board game in excel. I currently have a draw card feature to draw random cards. I also have section above where you can fill in the answer to the question and another 2 sections that flip to correct or incorrect. Those formulas work as =IF(AND(Sheet1!J6="When resubmitting the claim to another insurance should you be voiding an adjustment to the S code if one is posted?",Sheet1!I2="Yes"),"That is Correct!"," "). I have another for if Sheet1!i2="no" then it flips to that is incorrect. I want an OR formula that will be like if multiple cells have any text in them it will display them but only one cell at a time. So essentially a way for multiple questions to be able to be drawn and you can answer those questions and be told if they are correct or not.
View 14 Replies View RelatedI'm working with Excel 2003 on a peice for work that requires users to enter their current grade and, if they have one, a temporary grade. Another sheet in the workbook needs to add together all those in a certain grade and who answered a certain way to a drop-down table, so that I can use it to figure out what percentage of staff are of each grade. At the moment it looks like this:
=SUMPRODUCT(--(Work_Data!F2:F9827="Grade 1"),--(RIGHT(Work_Data!K2:K9827,3)="Top 25%"))
Which only draws information from the current grade (column F) and the answer they give (Column K). What I can't come up with is a formula that will only take column F as long as column G hasn't got a temporary grade, but if it does have a temporary grade will take column G instead.
When I click on a cell, for example T13, cell U13 will also automatically be selected. I have looked everywhere for something that is making it do that, but can't find an answer.
It doesn't happen with every cell. I have to do some more clicking around to see if there is a pattern.
I'm after a bit of programming which selects the same cell on the next sheet that I have already selected.
So, If I'm on Sheet1 and I have G5 selected, when I change Sheets to Sheet3 for example, I want it to select G5 on Sheet3.
Trying to select multiple shapes based on shape properties. The missing piece is the code to select the shapes. The examples in the help use the 'Array' function with a list of shape names.
Is there a way to select a random set of shapes without using the Array function?
Or how does one build a list for use with the Array function?
I'm working on a macro that's supposed to copy certain cells from a sheet, and paste them in another. But the problem is that I can't find a way to get them all in one go. The sheet that I am copying from has a mile-long list that looks something like this:
OR0220018NO-RING 21,95 x 1,78 N270 BS-0205,00
OR0220018N90O-RING 21,95 x 1,78 N290 BS 02022,00
OR0220018VO-RING 21,95 x 1,78 V275 BS-02010,00
OR0220018V90O-RING 21,95 x 1,78 V290 BS 02013,00
OR0220020NO-RING 22,00 x 2,00 N2709,00
OR0220020VO-RING 22,00 x 2,00 V28024,00
OR0220025NO-RING 22,00 x 2,50 N2705,00
It goes over 3 columns. I want to select column A, B and C in every row that contains the letters "N270".
I am trying to create a spreadsheet to select different values. I have 5 columns with type A values then 3 cols with type B values, a blk col then 1 cols with type B values and 5 cols with type A values then 1 cols with type B values and 2 cols with type A values a blank 1 col with a B value, 2 cols with A values and 2 cols with B values. [ A A A A A B B B Blank B A A A A A B A A Blank B A A B B ]
I need to select 5 lowest values from this set but a minimum of three A values need to be used. I have used the min, small 2, small 3 for the A set then used an IF statement for the fourth value. The formula returns the correct sum for values up to the fourth value. The problem comes on the fifth value. I am attaching the formula I started with.
=(MIN(F3:J3,R3:T3,V3:W3))+(SMALL((F3:J3,R3:T3,V3:W3),2))+(SMALL((F3:J3,R3:T3,V3:W3),3))+(IF((MIN(K3: M3,U3)<SMALL((F3:J3,R3:T3,V3:W3),4)),(MIN(K3:M3,U3)),(SMALL((F3:J3,R3:T3,V3:W3),4))))+(IF(SMALL((K3: M3,U3),2)<SMALL((F3:J3,R3:T3,V3:W3),5),SMALL((K3:M3,U3),2),(SMALL((F3:J3,R3:T3,V3:W3),5))))
in a vba module I currently have this
View 2 Replies View RelatedThe code im using all worksheets. How do I make this sort all but the first
For Each WS In ActiveWorkbook.Worksheets
If WS.Name "Sheet1" Then
Range("A1:X2270").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
i have the following table, i want my Vlookup not only get the description from Column K but to get column K , L , M City and Country as well and put them in Columns D for Description, E for City and F for Country. would this be possible with Vlookup formula or any other formula?
View 3 Replies View Related