i want to write a macro which searches for data in a xl sheet.It should then format that data in a particular format.The data can be present in multiple places.For eg. formating a 5*6 matrix which starts from d4 cell and a 4*7 matrix which starts from e15 cell(please note that d4 and e15 location are not fixed).
View 9 RepliesI am working on a VBA macro, using the following
With wsSheet.Range("A:A")
ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
MyArray = wsSheet.Range("A:A")
For i= 1 To .Rows.Count
For j= 1 To .Columns.Count
wsSheet.Cells(i, j+ .Offset(0, 3).Column) = MyArray(i, j)
Next: Next
I want to select alla values in column A, but when I specify the range as "A:A" the code results in an infinite loop! How can I come around this?
I have created the below macro to show the user (when he / she clicks on a button) how many days are left till the deadline day (taken from MS Project).
Sub DaysLeft()
Dim rng As Range
Dim count As Integer
Set rng = Sheets("Programme Reporting").[E2:E200]
count = Application.Evaluate("NETWORKDAYS(Today(),E3)")
Range("L3:L200").Select
ActiveCell.Value = count
End Sub
The range I want to output the days to is L3:L200 that seems to be okay. I have an issue with trying and putting a range in the NETWORKDAYS area, if I try and put in E3:E200 (the range of dates) it fails, as does $E$3:$E$200. At the moment the above code does work but it only puts in the days for one of the entries (E3).
I am trying to select rows in excel using a macro and then hide the rows, but i want to be able to add rows within this selection and still be able to hide the complete selection of rows.
The macro i am currently using is shown below: This hides rows 131 - 205, but if i was to add in another row in between so the range i now want to hide is 131-206 can i get the macro to update to this new range?
[Code] ......
I have 2 worksheets where I need to transfer a certain amount of data from one to another depending on dates.
Sheet ‘A’, is the main data source. Col A holds all 365 dates from 1 Jan to 31 Dec(starting at A2), and the subsequent data for each day is held in cols B to P.
Sheet ‘B’ is the destination where the user will specify a key date in cell A3, then in A4 to A32, formulae populate the next 29 dates (ie so there is a continuous run of 30 days).
What I need to do, is to identify the same 30 day range from sheet B on sheet A, then to copy the data in Cols B to P for that range back onto sheet B (pasting in at D3)
I currently have this beast of a code running in Excel 2013:
HTML Code:
Sub OBTAIN_RAW_DATA_MACRO()
Dim NextCol As Long
'Copy data
Workbooks.Open Filename:= _
[Code] ........
However the issue lies in that once the data is refreshed, the slicers reading off the above raw data/pivots, automatically select all weeks, however this is not beneficial for the data being presented.
Is there anyway to have, once the Pivots have been updated, for the macro to then check and select current week range (beginning Monday) only?
I am using the following code to re-name filtered data in column 4 of my worksheet, but I am running into an issue when the Autofilter returns only one row of data. The End(xlDown) is selecting a range that extends to the last row of the worksheet (1,048,576).
Code:
rfiltered.Cells(1, 3).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Receivables"
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
How can I write the code so that in the event there is only one row of data, it selects just that row for the paste (and yes it would be redundant to paste over the same value) and then continues on? I would usually use a Cells(Rows.Count, x).End(xlUp).Row to find the last row of data, but I don't think that will work in Autofilter mode.
I m trying to make a button to add values to another sheet in my xls. Ive done that... now i try to autofill the percentages from left and above one row....
View 9 Replies View RelatedI would like to loop through cells row wise.
Columns which have data are A:E
modify below code to select the range from columns A to E?
[Code] ........
Selecting the range from Multiple Column data.
Currently, it is:
[Code]....
I have data from columns A:E
I have to select and cut a column with on several lines empty cells. I use the following code
'Range("c2", Range("c2").End(xlDown)).Cut' but this code select only the data till the first empty cell.
How can I select the whole column till the last cell. The column B is filled with data on every row so I think that I have to use the offset code.
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
My VBA is very rusty, and I can't get the following code to work. I need to workout the number of rows that contain data in a range so that I can eventually write a formula in a adjacent cell, but I keep getting errors (Type mismatch or Subscript out of range) at the first step!
Code:
Sub create_product_column()
Dim num_rows As Range ' The number of rows in the datarange
Dim my_workbook As workbook ' The name of this workbook
Dim my_worksheet As worksheet ' The name of the worksheet to work on
[Code]....
if a combobox in a userform can be populated by the NAMES of defined ranges in a worksheet?
ie. worksheet contains 3 NAMED arrays: W1 = $C$5:$E$6, W2 = $C$8:$E$9, W3 = $C$11:$E$12. Have the combobox in the userform actully list W1, W2, W3 so that when W2 is picked (for example), cell $C$8 will be selected.
I have a Range on one sheet formatted like this and sorted by Sup then Rep:
Rep Sup
John Sally
Mark Sally
Joseph Sally
George Betty
James Betty
etc..
I have a Validation Drop Down list where a Supervisor is chosen. There's a specific range that I would like to automatically fill in with the reps assigned to the Sup. This is driving me crazy! I already know how to obtain that list as another Validation Drop Down, but not quite sure how to make those values appear in a range instead of a Validation List.
I have a range of data in columns A2 to Mx. The length of the columns varies, and all cells are not always populated. I want to be able to select the entire range, no matter what its length, no matter how many cells are populated (as both of these will vary on each tab) However, I do not want to select A1 to M1 as this is column headers which need to be excluded from the data for compliation purposes.
View 9 Replies View RelatedI created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:
09-15 16-22 23-29
with the month manually put in above it.
then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.
Budget Picture.jpg
I want to fill down and across a formula but the ranges wary depending on how many cells the data has been populated with.
At the moment i'm using the autofill function but can i use a do until function:
Range("F4").Select
Selection.AutoFill Destination:=Range("F4:EK4"), Type:=xlFillDefault
Range("F4:EK4").Select
Selection.AutoFill Destination:=Range("F4:EK1838"), Type:=xlFillDefault
I am looking to change a row colour if any type of data is entered into a specific cell.
Context: In column H entitled 'Start Date' when someone enters a date n any format into this box, I want the whole row to change colour to easily see what projects have started and what ones havent.
Any way to edit data that has been populated in a Userform Listbox? I am trying to create a userform which has a multicolumn (3 columns) listbox and data being populated from Sheet1!A:C. I am thinking that there would be an Edit button where when clicked the data for the row that is selected in the listbox is shown in 3 textboxes (one for each cell on the respective row) on the same form which can be edited. When the user clicks Save. The Listbox would be updated with new values (e.g. write over the values in Sheet1.
View 9 Replies View RelatedI am using the below code to pull information from multiple sheets into one master sheet. Currently, it is funcitoning fine with one slight problem. In each of the sheets I have formulas copied down until row 500. However, some of the sheets may only have formulas populated up until row 50 for example. edit the below macro to where I am only pulling in the rows that have populated formulas?
Code:
Sub Consolidate()
'Author: Jerry Beaucaire'
'Date: 9/15/2009 (2007 compatible) (updated 4/29/2011)
[Code]....
I have a .dot word template that has the little 'grey' boxes that is awaiting information to be filled in, this information is already stored on a master excel sheet and the doc is simply for 'archiving' and users benefit, from my point of view its pointless.
So what I want to know is...
If I always have a .dot file which has :
Username : DATAHERE
Password: DATAHERE
Email: DATAHERE
and I have an excel file which is A1 : Username B1 : Password C1 : Email how easy / hard is it to create a button macro to automatically go to the .dot file location, open it and then put the create data in.
I would like to create an spreadsheet that prompts me to input data into a cell (eg; 'C1' - 'Quantity Ordered') as soon as another cell on the same row becomes populated (eg; 'A1' - 'Item Code'). I need this function to apply for several rows (25 rows in total), so that if data is then entered into cell 'A2', I get a prompt to enter data into 'C2', all the way up to a prompt for data in cell 'C25' when cell 'A25' is populated.
As it is imperative that I never forget to populate the relevant cells; I would also like to 'enforce' the prompt - perhaps by preventing the worksheet from being saved until the relevant cells have had data entered into them, or perhaps by using some kind of form, rather than a messagebox.
I have designed a simple user form to populate a finding tracker spreadsheet. Updating the tracker works fine.
Although I only need to update certain textboxes in the user form, I find myself having to enter the same data in every textbox so that the next row of the spreadsheet is filled. In all cases, if a textbox is not updated for the next row, then the data should copy the data from the previous row.
For example, last data Transferred from the user form are as follows:
[Heading] Col A - Col B - Col C
[Row 1] Apple - Red - 10
Assuming I would only update the textbox for Col C in the user form, the next row in my spreadsheet would look like this:
[Heading] Col A - Col B - Col C
[Row 1} Apple - Red - 10
[Row 2] (empty) - (empty) - 20
As such, I would like to add a code that allows the data (Apple and Red in Col A and Col B) from the previous row to be copied automatically and only updates Col C with the new value 20.
Oh, I should add that I have mostly Textbox values (about 20 columns) in the spreadsheet with the exception of three columns with CheckBox values although I can always repeat the checkbox fields.
I have a workbook with three sheets: Collections, Client and Interest New sheets created from a range in Sheets("Interest"). The range in Sheets("Interest") holds the client names.
Then the template in Sheets("Client") is copied and pasted into the new sheets
For each new sheets cell B6 holds the client name and .Range("A10:A1317") holds the dates
Sheets("Collections").Range("D10:D1317") holds the dates and .Range("D8:WC8") holds the Client Names
if Sheets("Collections").Range("D8:WC8") = new sheet.cells(6, 4) then
if Sheets("Collections").Range("D10:D1317") = new sheet .range("A10:A1317") then
copy the value in Sheets("Collections").Range("D10:WC10").offset(0, 3) to new sheet .Range("C10:C1317")
end if
move down on row and repeat the steps.
To initialize some cells/ranges, I am copying a given range and pasting it to another given range using the. Copy and .PaseSpecial methods. However, it would seem that both methods actually select the range(s) for the operations, i.e. the given ranges(s) are activated/selected thus changing the focus on the spreadsheet. I would like to perform both operations without actually selecting the given ranges.
View 5 Replies View RelatedI am receiving a 'subscript out of range' error on the lines of code below.
I would note that all variables are declared and all seem meaningful as regards what you would expect at that point.
Below is a snippet from the immediate window which indicates what the values are:
completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls
cashsheetname Formatted Sheet
cashcurrcolumn A
cashfirstrow 2
cashlastrow 876
Also the workbooks are both closed at this point (but it makes no difference)
Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _
Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value
Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _
Range((PELCurrColumn & PELlastrow)).Value
I have this formula =COUNTA($A:$A)<=4 that limits amount of cells that can be populated in column A, I use data validation with "Allow costume" option and using that formula. It works fine from worksheet it displays the message when the limit is reached but it doesn't work when data is inputed/populated from userfrom, it allows userform to put more entries than set limit 4 in this case.
View 3 Replies View RelatedWhat i'm trying to do is select a range based on the date which happens to be in the first column of the range.
For example, I have a column (A) that contains dates and column (B) that contains names.
Its easy enough to select the entire range using
Range("A1:B4").Select
I need someting that will analyse column A and conditionally selct the range based on the date in column A (ie. it will only select the rows where column A has todays date).
In cell H5 there is list of dates and in H7 there is codex, based on these two conditions in H11 a data validation list should be populated from the code_sheet using column B. The populated list should be unique entries, as the column B in code_sheet has duplicates.
View 9 Replies View Related