how to auto fill of same value in different cells in a row?
For Example:
A1 D1 E1 G1 K1
10 10 10 10 10
like that when i hit a macro button the values in the A1 D1 E1 G1 K1 must fill with A2 D2 E2 G2 K2
Working with 2 worksheets in the same spreadsheet. In worksheet A cell F6 is a solution (hex2dec) these solutions are staggered consistently in column F (F6, F9, F12, F15 etc) In worksheet B cell A2 displays workshop A cell F6 details (=ATR!F6)
Worksheet B cell A3 Autofil uses (=ATR!F7) I want (=ATR!F9) Worksheet B cell A4 I want (=ATR!F12) How do I condition autofil to follow my chosen sequence ie F6,F9,F12,F15 etc...
my macro simply activates a cell and all i want it to do is to auto fill downwards!
its intended to autofill down for about 100 rows but just cant figure it out!
but keep in mind the cell is always going to change so it cant be fixed
i.e this wont work:
Selection.AutoFill Destination:=Range("C95:C659")
Range("C95:C659").Select
ActiveWindow.SmallScroll Down:=513
because i need it to be autofilled from the CURRENT active cell only!
I would like to enter N/A in the empty cells E6:E33 with the click of a button. Not sure how to write the code.
View 5 Replies View RelatedI would like to auto-fill cells based on what I select from a drop down list.
For Example:
Drop Down List is located in A1(already created). Let's say it lists items like bathroom,front of building, dinning room etc.
I have created on sheet 2 a list of responsibilities for each item on the drop list. The list has a header for example bathroom and a below it list the responsibilities. I did this for every item on the drop list.
Now, is it possible to select bathroom from the drop down land when selected the list I created in sheet 2 show up in the cell next to bathroom? Also, is it possible to have this happen every time i select a different item from the drop down list?
I am trying to create a spreadsheet to record vehicle mileage at the end of each week, however sometimes we cannot input the information until 2 or 3 weeks later. I am looking for a way to auto fill specific cells when the user inputs the information using a userform. On the attached spreadsheet:
The first button input an 'Empty Row' ready for the new week and current mileage. The second button opens the userform to select the vehicle, date and input the mileage. I cannot figure out how to have the mileage inserted into the correct cell irrespective of when we insert the information.
I have this excel file with 5 columns on it and one of the columns is dependent on the four columns. I just want to automatically fill out the 5th column (Completed, In-progress and not yet started) based on the conditions set on the four columns. Although, the four columns have many conditions, it has been categorized with 3 colors. If all of the 4 columns are GREEN, 5th column will show Completed, If all of it are orange- Not yet started and if one of the columns had different color 5th column will reflect -In progress.
View 1 Replies View RelatedI have a startdate F1 and an end date G1. Is there a macro that can list all dates by day within that range into column I7:down.
View 9 Replies View RelatedI want a Macro to be able to copy a formula down from cell A2 down the entire column within the data range. But, the problem I've got is that the spreadsheets I'll be using the Macro on will be different sizes. Currently, the VBA looks like this:
ActiveCell.FormulaR1C1 = "= LOWER(RC[3])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A17340"), Type:=xlFillDefault
Range("A2").Select
So the "Destination:=Range("A2:A17340")" part copies the formula into all cells from A2 to A17340. Other spreadsheets might only have data up to cell A200, or Awhatever - is there a way of getting the macro to populate just the cells in column A but only where there is data adjacent in other columns?
Also, I want the Macro to be able to delete all rows wherever "DELETE" appears in a certain column - I had a look at the "Delete Entire Row Based on Criteria" Macro but I'm looking for a fully automated solution, rather than the question boxes coming up and asking which cell/criteria, I need to run this macro on multiple sheets and the criteria/column position will always be the same -
I need a formula that allows me to autofill a cell with "X" kind of data after I choose a validated data of Cell A1;
View 6 Replies View RelatedI need to create a formula that searches the first table and autofills the second based on the teacher name. So when Mr. M is listed in column e, all the data to the right automatically appears on the second sheet.
View 3 Replies View RelatedI have been working on a spreadsheet but I use autoformat for.
For example if the date in the cell matches today's date then it turns the text read and makes it bold.
I was wondering if there was a way to format several cells in a row if a certain criteria was met.
for example
A B C
1 Item Cost Quantity
2 Car £11,520 2
3 Bike £7,500 1
4 Tyres £50 4
Now for example if I set a condition whereas the quatity is greater than 3 then it would highlight the whole row.
So in the example above I would like to to highlight row 4 and Fill colour cells A4,B4 & C4 with a yellow background and Red Text and make the texted bold.
I need a simple macro to use for conditional autofill of cells below the active cell (to the end of the sheet) with the text that is being entered in the active cell.
DESCRIPTION: I have two columns of data, the first (call it Col1), has a word number which is unique to each specific word in a foreign language, every cell in this column has a number in it from 1 to 30000. Each number reoccurs many times in cells over the length of this column.
The other column (call it Col2) is blank. As I type words in english in each cell of this column, I need the macro to autofill the text that I have just entered, into every cell in Col2 where it's Col1 number is a match with the Col1 number where I am typing.
For example, in one row, Col1 has the number "21" and I type in Col2 the word "run". I need the macro to find every row where the number 21 is found in Col1 and automatically enter "run" into Col2 at that location.
The macro must only autofill cells which have no data in them. Preferably from that cell down, although if that is not possible, all cells would work seeing we are only replacing blank cells with data.
Is there anyone who can suggest a solution to my problem below ?
Basically, what I need is a macro which would ideally work like this:
if D32 = 1, then the background colour of D4 should be red
if D32 = 2, then the background colour of D4 should be orange
if D32 = 3, then the background colour of D4 should be yellow
if D32 = 4, then the background colour of D4 should be green
And then I plan to use it for columns E, F, G
Auto Filter Using Macro
I have a Spreadsheet with names and other data, my question is, can i somehow Auto Filter the First name with a macro that I can assign to a button, what I would like to do is type the First Name in A1 and in B1 have a button that I can press and it will filter all with the name and other data on the same row I have typed in A1 will only be shown.
I am trying to make a calendar spreadsheet to enable me to track allowance payments i am due from my company.
This payment is worked out by three conditions - where i am working (7 regions), how many days of the week (4,5,6 or7) and how many hours (<7,8-9 or 10-12)
I have made drop down lists to select each of the values for the three conditions and want to show the total for them
i can total the entire spreadheet for the month up using fairly large "COUNTIF" statements, but i want to be able to show what the exact amount is in each cell not just a complete total.
i.e. i am away for 6 days i get £15 extra, if i am in Europe i get another £50 and if i work 12 hours i get £30.
I want to be able to calculate this total in each cell for that day.
I can send a copy of the spreadsheet i have got at the moment if that will make things clearer than what i have tried to explain.
Let's say I have column C blank, but every time it's filled in with something I want column A to be today's date and B to be current time. Unfortunately, the functions NOW() and TODAY() don't give me what I want. They get recalculated every time that I update the spreadsheet.
Another thing that I can do is just press ctrl+shift+; to generate a non changing date/time, but I want it to happen automatically (being super lazy).
When I enter data in the cells A1 and B1, C1 needs to calculate the result. (not copy and paste)
For examle: I have formula C1=(A1+B1)/2
C column for formula, but shows result only when there is a data in A and B
I'm trying to find a way of hiding every sixth row on my sheet, however, no matter what i try I can't seem to get it to work.
I want to be able to click a macro button that will auto hide every sixth row. But I don't want to have to input every row reference in VB.
My data is now 500+ lines long but the button is on row 1. I have to get back to the top to click it. Can I auto move the button down with my data. Say to one line above my data. i.e. 499 and move as my data increases.
View 3 Replies View RelatedI'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.
Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"
UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................
I have a button in a spreadsheet that auto populate's certain cells with this script very simple.
Sub Bundle_8230()
[C242] = "HP NC8230"
[C243] = "Deluxe Laptop Bag"
[C244] = "Travel Battery"
[C245] = "Mouse"
[C246] = "Keyboard"
[C247] = "Port Replicator"
[D242] = "1"
[D243] = "1"
[D244] = "1"
[D245] = "1"
[D246] = "1"
[D247] = "1"
End Sub
What i would need is a script so that it doesn't look at those specific cells
but a range of cells and every time the button is clicked it will fall into the first empty cells.
So example when button is clicked
Case 1 if cells C242 has something look at cell C243
If cell C243 has something look at cell C244
If cell C244 has nothing then enter data that goes with that button.
I have around 6 to 7 different buttons.
I've created a worksheet that allows my team to build an order for a customer. I've created a PDF form with blanks so they can fill in the necessary values in the correct fields ie. Tariff Name, Handset Name, Minutes etc.
I'm hoping to create a macro button that will export all the the data needed and auto fill the PDF form to save time.
So far I've added a button with the following code
VB:
Sub CopyData()
Sheets("Quote").Range("v16:ab16").Copy
Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End Sub
This simply adds all the data needed onto a separate sheet ready to be exported as a csv.
I'm in need of the rest of the code to export/import Sheet1 into my fillable form.
So far attempts to export to .csv have turned my entire worksheet into a .csv file or caused errors within the code.
I've attached a sample of my Workbook and PDF form below.
Sample Documents .....
Is there anyway to automatically fill the empty added cell after inserting a row without using the fill handle? For example, for a series of numbers: [URL]
2. Drag the fill handle Selected cell with fill handle across the range that you want to fill.
Or running balance: [URL]
2. Extend the running balance formula into the new rows by selecting the last cell in the balance column and then double-clicking the fill handle.
I'm using 3 sheets within one Excel file. 1st sheet (target) is called Offer, the 2nd one (source) is called Stock and there's a 3rd one that only contains a button to which i'll assign the final macro. Now, back to it.
I need to copy from Stock to Offer contents of the entire column (basically there are a few more in Offer, so i need to copy each one and place it in a different part). I need to copy from ROW 2 and paste in ROW 6. The length of the document varies, as it's connected to the database (now i can have 3800 rows, in the next hour 3765). Previously i just added an X to row 4500 ( i don't expect to EVER have more than 4000 lines) and just copied from 2 to 4499
Second issue is, on the Offer sheet i have to fill a column with a certain value. Again, it needs to start from Row 6, and needs to be as long as the adjacent column. To get a better idea, i have for example 3800 price lines, so i need the column next to it to be filled with 3800 rows saying GBP (this will be a fixed value).
Also, none of the codes above should exclude blank cells in the middle of the string. Last thing, seems some of the barcodes i have only have 12 digits, so i'll need to add a 0 in front of all shorter numbers to go up to 13 digits.
[Code] ......
I should be able to figure the rest by recording bits and pieces. The Save part is done already, i'm using
[Code] ....
I needed it in XLSX format, not XLSM so that suits just fine. I might need to add hour and minute as well but i can sort it out (as i said, i can do some stuff on my own but this one got me to the bottom).
Col D has list of order #'s, starting in row 9. Col M is for COMMENTS about each order. Order #'s can appear in Col D more than once.
What I am looking for is a macro that will automatically detect if I have entered a new COMMENT in Col M, recognise the order # in adjacent Col D, and put my new comment in other cells in Col M if the SAME ORDER # is referenced in adjacent Col D.
Example:
worksheet starts with:
Col D Col M
1234 x
123 xyz
1234 zyx
4321 zyx
321 y
1234 yz
If I type the letter "Q" into Col M in a row adjacent to order # 1234, the result should be:
Col D Col M
1234 Q
123 xyz
1234 Q
4321 zyx
321 y
1234 Q
I think I would need to put this macro into a Worksheet Change event but beyond that I do not know what I need. UDF? Macro?
I wonder if soeone could change the following macro so that the bottomcell
is set to be as far down as there is content in Column "A". As it stands if I fill in blank cells in Column "C" and the last cell with data is say C10 it does just this. But if the actual last row with data in column "A" is A14 then cells C11, C12, C13, and C14 are ignored. C11, C12, C13, and C14 should be the same as C10 above them.
Sub FillBlankCells()
Dim topcell As Range, bottomcell As Range
Set topcell = Cells(1, ActiveCell.Column)
Set bottomcell = Cells(16384, ActiveCell.Column)
If IsEmpty(topcell) Then Set topcell = topcell.End(xlDown)
If IsEmpty(bottomcell) Then Set bottomcell = bottomcell.End(xlUp).Offset
Range(topcell, bottomcell).Select
Selection.SpecialCells(xlBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub
Other macros recognize the actual last row.... but have issues with columns formatted as Text. The macro above doesn't create problems with columns formatted as "Text".
I have some information in a column where I have different types of references in each cell with some blank cells in between. I am trying to write a macro that will go down the column until it gets to a cell containing the reference "THIS PAYMENTS" and then copy that cell reference down until it reaches a cell containing "Y-T-D AMOUNTS".But I want the loop to carry on until it reaches another cell containing THIS PAYMENTS.I want this to repeat for about 3000 rows See here before and after result.
Before
After
PERIOD
PERIOD
[Code]....
Would like a script that will go to each sheet in the workbook, and fill in light grey background, each alternate row that contains data, EXCLUDING the 1st row (header row).
Data file with few columns. There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
See here Fill Cells.xlsx