I am have some code that takes values from a range (sourceAll) defined on my worksheet as:
Set sourceAll = Workbooks(pathnm).Worksheets(m).Range("A1", "S34")
I'd now like to be able for the range to be generated dynamicaly, but my problem is that the far right column (S) does not always have a value for every entry. Is there a way of using a column that does have a value for every entry, say column B, but then extending the range accross to S? If possible I'd like to avaoid having to move one of the columns with values for every entry. Auto Merged Post;I was looking at some of the suggested threads and found some code that I worked into my own
Set countRow = Workbooks(pathnm).Worksheets(m).Range("B1250").End(xlUp)
Set sourceAll = Workbooks(pathnm).Worksheets(m).Range("A1", countRow)
sourceAll = sourceAll.Resize(sourceAll.Rows.Count, sourceAll.Columns.Count + 17)
now this seems to work mostly, but it has deleted everything that was in column B.
What I need to accomplish here is to randomly (or at least seemingly enough) generate a number between A and B values for many cells.
I know i can accomplish this through =Rand()*(B-A)+A
However lets say that the lower limit is 96 and the upper is 100. How would i get my data spread to appear to center more around 98-99, with a few randomly placed a little farther out but still within 96-100? I'll say i have 500 instances of this generated number. So what i need is the appearance that most of the numbers lie within the inner range with some anomalies outside that but still within the greater range, having fewer and fewer instances as the value gets closer to the outer limits.
I'm also placing this value in cells using VBA (most likely "for each cell in range(xxx)...")
I have a list on names that is in the range on Sheet5!A1:A149 I'm using the function to randomly select one of those names.
What I want to do: I want to add the randomly generated name to a range that I can then refer to later to see if that name has already come up. So I know if that name has already been used.
I have a macro that copies rows from my DAILY OCCURENCE sheet that have a YES in them to my MANAGER SUMMARY SHEET and G1 is selected which has a hyperlink that opens a new email with the manager's email address and Subject pre-assigned.
I then have to manually go back to the newly generated summary sheet and select the cells and paste them into the email that was just opened.
Is there a way that the generated rows are already copied in memory and all i have to do is CTRL+V into the new email, or even better, the cells are pasted in the email automatically? So basically either the information being copied over stays in memory, or after it is copied into the summary sheet it is copied again. I just dont know how to copy unpredictable ranges generated by macros.
The code that generates my summary sheet for my manager is below.
I have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange.
I have another named range that actually refers to a range. Call it AllData.
Column A Row2 56 Row3 44 Row4 65
AllData is a named range that refers to the range A2:A65536 AllData_UsedRange refers to A2:A4 by way of this formula. =OFFSET(AllData,0,0,COUNTA(AllData))
How to I obtain an address of AllData_UsedRange in VBA code?
These do not work... ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address Evaluate(ThisWorkbook.Names("AllData_UsedRange"))
I have a 'receipt' worksheet with about 500+ lines of data, here is an example of what could be in cell A166: 1.1 NET_AMOUNT 742,523,253.83
In another worksheet, is there a way to look at the whole worksheet for column A in receipt to say: if 1.1 is true, capture text to the right of 'NET_AMOUNT' then format using =Dollar?
I am using the STDEV function on a range of values: STDEV($I$26:I2545). My starting cell is constant and I would like to make my ending cell variable based on a value I enter in a master cell (A1). The value I enter in the master cell is multiplied by 252 the product would equal the row number I would like to stop at. So if I enter 1 the formula will be STDEV($I$26:I252). I tried: STDEV($I$26"I"&(A1*252))) and other such variations to no avail
I maintain Production data in an Excel Sheet and have the code which will create Chart for each Employee. Sample Sheet Attached. However, I want the Chart Range to be updated in a Single Chart and exported to a .Gif File.
Need assistance with this code provided by SHG. See attached sample. I need to create a range called DaysOff so that this function will work. As it is the function is perfect; though the accuracy if the result is dependent on the exact range, thus I need this named range to be dynamic. I have tried but it does not seem to work correctly. The named range should refer to the list of names between 1-10. The range should dynamically include any names that are listed.
Public DaysOff As Range Function datNext(datLast As Date, DaysOff As Range) As Date Dim datBeg As Date, datEnd As Date Dim iLastPd As Integer Dim iDay As Long, nDay As Long Dim iYr As Integer, iMo As Integer iYr = Year(datLast) iMo = Month(datLast) datBeg = DateSerial(iYr, iMo + 1, 1) datEnd = DateSerial(iYr, iMo + 2, 0) iLastPd = Pd(datLast) nDay = DaysOff.Rows.Count Do.......................................
See the attached example. I have data listed as shown, but need the chart to automatically update whenever a new sales figure is added to the next month. I know you have to use the OFFSET to create dynamic ranges but I can't get it to work horizontally, only if my data is laid out vertically.
I have a challenge I'm trying to surmount. I have an excel sheet in which I want to be able to select Zone codes from a drop down, click on a link "search" which takes me to a place in the document that has been named according to the Zone code i pick in the drop-down. I want to achieve this using only formulas as i'm distributing the file to a wide audience who are not versed enough with computers to know how to enable macros.
I have 100s of name ranges in my spreadsheet. I am using a series of drop down boxes for the user to select the info that they want to see. Based on the dropdown box, the macro "dynamically generates" a range name from which to pull the info.
For instance, if they Select Manager = Terry, Analyst 1, and Month = Jan, then the range name would be JanTA1
The if statement would dynamically create this name by concatenating the info
If manager = Terry mnger = "TA" if analyst = 1 anlyst = 1 if month = Jan mnth = "Jan"
rangename = mnth & mnger & anlyst
So, I am dynamically creating the range name based on user selections...then to display it, I want to set the display cell = to the value
I have a combobox whose list is filled with different ranges, on different sheets, selectable using option buttons. The code is shown below:
Private Sub OptionButton1_Click() Dim ray Dim Last ray = Sheets("Trades").Range("B2:B500") If OptionButton1 = True Then With ComboBox1 .List = ray .ListIndex = 0 End With End If End Sub
Setting the XValues property gives an "Unable to set the XValues property of a series class".
Using the chart wizard, trying to set the category labels parameter of the chart wizard to a range doesn't give an error message, but does leave the category labels section of the chart (when I click and view it) blank.
When I step through the code, tempstring is "='LineReg Results'!R9C1:R19C1", which is correct, but I still get an "unable to set the xvalues property of a series class" error.
Interestingly, if I record a macro of me setting the category labels, and play it back, I also get the error.
I'm hoping this is a simple question someone might help clear up for me.
I've defined a few cell ranges on a worksheet (in my example I've defined their names as site1, site2, site3, site4, site5). All these ranges are identical in # of columns and rows.
On a separate worksheet, I'm trying to display a single named range, depending on the value in a dropdown menu.
In my example, I have a pulldown menu with the following items: site1, site2, site3, site4, site5 (mirroring the named cell ranges). I'd like to display one of these ranges, depending on which name is selected in the dropdown.
I have the following vlookup formula in my spreadsheet.
IF(ISNA(VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE)),0,VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE))
It works perfectly for my current requirement, but it looks at data in a pivot table which is created from a dataset. If I name the columns in the pivot table so instead of looking at column 67 it looks at a name range will this name range change dynamically when the pivot table is refreshed? If not how can I get it to.
I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
I’m creating a spreadsheet to control corrective actions. Each corrective action will have a date that it needs to be completed by. When the completion date plus one day is passed, I need the cell that the completion date is in, to turn a specific colour and a warning mail to be sent via outlook When a further six days have passed I need the cell to turn a different colour again and another email is sent
Ive generated the following code to call an Outlook subroutine if the correct conditions are met. The subroutine runs ok but I can get the sheet code to run.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("A1"), Target) Is Nothing Then If IsDate(Target.Value) And Now() = Target.Value + 1 Then Target.Interior.ColorIndex = 3 Call Mail_with_outlook ElseIf IsDate(Target.Value) And Now() = Target.Value + 6 Then Target.Interior.ColorIndex = 5 Call Mail_with_outlook
I'm trying to sort some data that is generated from a PDF. When I copy the PDF into Excel all of the data is placed into one cell. Is there a function or formula I can use in VBA or within the spreadsheet to accomplish this?
For example, the cell may contain "Employee John Employee Steve Employee Dave Employee Jed Employee Mark" in cell A1. I want the function to list John, Steve, Dave, Jed and Mark in separate cells. So I need to get rid of the space bar hits, the word employee and to separate the names to different cells.
A1 currently is "Employee John Employee Steve Employee Dave Employee Jed Employee Mark"
I wish to create a new sheet/workbook which promts the user to enter a start number and an end number. I want to then display 20% of randomly picked numbers between the start and end figures.
For example, the user enters 1 as a start and 20 as an end figure. 20% of this is 4, hence I want the system to randomly return 4 numbers between 1 and 20.
I have the following code that will look through an individual worksheet, if the value in column D is equal to 5 and the value in cells E6:L1000 is "a" then it should generate an email. The problem I am having is that it generates 8 emails, one for each column. What did I do wrong in the code? It should only generate one email for the cell which has the "a"
Sub Test() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Dim cell As Range Dim wsWH As Worksheet Dim lngMax As Long Dim intFiveDays As Integer Application. ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") On Error Goto cleanup Set wsWH = Worksheets("WH") lngMax = wsWH.Range("A65536").End(xlUp).Row For intFiveDays = 6 To lngMax...................
We are having a sporadic issue throughout our company where when users attempt to open an ASP generated spreadsheet in Internet Explorer and then hit CTRL+A to copy all contents and attempt to paste into a regular Excel-launched spreadsheet (i.e. launching Excel independent of IE), the last two columns are not being copied. Further, when attempting to just copy the two columns, themselves, only the first is copied, and when attempting to copy one cell each in the two columns (that are in the same row), only the first cell is copied. Additionally, when examining the clipboard in this instance, only the first cell is being copied to the clipboard, so for some reason attempting to copy it out of the Excel spreadsheet that opens in IE is when the issue occurs (vs. having the issue occur when you are trying to copy into the new Excel spreadsheet from the clipboard).
I have a file here that already has macros in it. The file is basically a excel document generator. When you click create sku, the document will generate multiple documents based on the user inputs.
1) In the generated documents the original "generator" file creates, I need generated files to have column B and D formatted to TEXT, currently all the generated files are formatted to general. (This code is in module 1, line 84 col 34.)
2) The other problem is I need to append the value in Cell N1 in the "sku data entry" sheet to be appended to the generated file names. (this part of the code is is in module 1, line 150 col 28.)
I was unable to attach my file to this thread because it was to big. however I copied the module where I think the code is causing problems:
Sub procData() ' Starting point for read/extract process Cells(4, 6).Select ' Range = F4 (row 4, col 6) For x = 6 To 52 Step 2 ' col F to col AZ
Prior to Excel 2007, the functions provided by the Analysis Tool Pack (ATP) are provided by a separate add-in. You cannot call them using the methods shown above. First, you must load the Analysis Tool Pack - VBA add-in. Note that this is different from the Analysis Tool Pack item. Once this add-in is loaded, go to VBA, open your project, and choose References from the Tools menu. In that dialog, choose atpvbaen.xls in the list of references. Once you have that reference in place in your VBA code, you can call the functions in the ATP as if they were native VBA functions.
I have thousand rows of data in the following format:
Despatched Time Batch Date Time Amended Time
20/4/2013 3:45:00 1 20/4/2013 03:45
[Code] ..........
'Date' is generated by =IF(A2="", "", TRUNC(A2)) 'Time' is generated by =IF(A2="", "", IF(E2 = "", A2 - TRUNC(A2), TIME(LEFT(E2,LEN(E2)-2),RIGHT(E2,2),0)))
And sometimes I may want to change the Time, so I used another column 'Amended Time' to insert a 4-digit value to do so.
I want to arrange them into batches according to the hh:mm, so for the column 'Batch', I used the following formula: =IF(D2 = "", "", IF(EXACT(D2, D1),MAX(B$1:B1),MAX(B$1:B1)+1))
The problem is, when I changed the time by inserting 4 digits in 'Amended Time', the time value is actually different from those generated by TRUNC(), so even two cells have the same time '03:46', the time value is not exactly the same and so they are arranged into different batches (please refer to the following table).
I have written the VBA code to color any figure above 15, but it display rows above 15 as red including other rows.
Below is the VBA code
Code: Sub FillCells()Dim lRow As Long, lColumn As Long 'Using Cells property to refer to range 'Loop through rows For lRow = 1 To 10 'Loop through columns For lColumn = 1 To 5
[code].....
Which shows the output as Workbook1.jpg But my query is how to get only rows above 15 with red color
