I have a column of data with various values and a bunch of blank spaces. Essentially I want to leave the values as is but fill in the blank cells with a number. I’ve written a loop to do this in VBA, which grabs the value in the cell above, but it’s somewhat slow. Is there a more efficient way to do this?
Sub Downfill(Max)
'
'This count variable is used to run the loop
Dim i As Long
i = 1
Do While i
I have two worksheets. One that contains material pricing and another that has misc data, formulas, etc. I have 100 rows of data with 2 columns. Column A I have numerical values. Column B I have a formula =(A1/36.4)*sheet1!C7. I try to drag down the formula but the value for sheet1!c7 changes as I go down (sheet1!c8 then sheet1!c9 and so on). How can I drag down the formula and have the SINGLE value from worksheet 1 carry to all rows?
View 3 Replies View RelatedI have an excel sheet with 2 tabs. 1st tab provides the data for downtime of a manufacturing line. The last column (shift) is blank and needs to be filled based in shift schedule in tab2
On the 2nd tab I have the shift schedule, which tell which shift is working on particular days
Month
Date
7:00am- 7:00 pm
7:00pm - 7.00am
Feb
1
Shift A
Shift C
[Code] ........
Is it possible to write a macros that will look at the Date and time from tab 1 and assign the correct shift number in the shift column. For example the 1st row of tab 1 reads (Month=Feb, Date = 3:55:59 AM) so according to shift schedule it would fill C shift.
I have lots of data and to assign every event to shift # manually is not possible. So i wanted to know if there is a macros i can use to make life easier and learn at the same time
If column B is not empty, I want the corresponding cell in the same row of column A to contain the value "1".
View 9 Replies View RelatedI want to be able to have a macro that will compare one cell to the rest of the cells in a collum. I have used the COUNTIF() formula and it's getting on my nerves a bit because it seems as though everytime i sort the data, it totally messes up the formula (as in it changes the cell refernces to seemingly randomly generated numbers, but that is a different matter! lol). here is the formula, and it will be followed by the formula that is in the cell directly below it: =COUNTIF(F2:F65536, F2) and the one below it: =COUNTIF(F2:F65536, F3). Only one number needs to change, but when i fill down in excell, it wants to add one to every number, even when i click COPY cells... oh well. So i want to write this macro that will do just that. That way, it has fixed reference parameters e.g. F2:F65536, and one variable parameter e.g. F2, F3, F4 and so on. I'm not too savvy with this whole VBA stuff... the university insisted that everyone uses C++ nowadays, lol. I would like this macro to output its data into an adjacent cell ex. if F2 was compared, the data will be placed in H2.
View 10 Replies View RelatedIn my worksheet I have a column with the number 1 in cell G3 ,I want to fill down the column with the number 1 as far as there are rows that have text in them. How do I tell Excel to fill down only as far down as the end of my table.
I would like to run this in a macro.
Is there a way to fill all rows of a column with the same formula without having to copy and paste it? My table is huge (about 6000 rows) and I need to perform the same computation on each row.
View 8 Replies View RelatedI am trying to fill a column with a number series starting with 1 whenever a value changes in the adjacent column. So for instance I am trying to do something like this :
87 1
87 2
87 3
88 1
88 2
88 3
88 4
89 1
89 2
90 1
91 2
92 1
92 2
92 3
92 4
92 5
92 6
I am trying to auto-fill a column based on certain criteria - in other words, fill in blank cells based on a cell above, just a little more complex version. I have added an attachment to give everyone a visual of what I am describing and then maybe this explanation will make a little more sense.
First, if you open the attachment, the left hand side is a small example of what I have. The middle is the condition. And the right hand side is what I'd like to see happene.
In the example, I am matching up a time and when I hit that time, I am adding on a certain number of miles per hours based on the condition and the cell above. Meanwhile, I am filling in the blank cells with the condition until the condition changes. So a cell is incremented based on the condition and gets its value from the cell above.
I have a spreadsheet that will eventually consist of tens of thousands of rows. Several columns contain formula's. Rather than having to "highlight" a cell and then "drag" all the way down the column over thousands of rows before finally using the "fill down" function I am needing a method to short cut this process?
Is there a function that allows you to, say, nominate a range of cells that you can fill down over? For example, starting from the last row with data entered into it down to say row 12500 i.e. D22:D12500.
Is there a way that I can make a macro that populates ComboBox1 of sheet 1 with the values in the B column, where the number of elements in the B column might be any value?
For example, if there are values in B2, B4, and B8, I want ComboBox1 to show those three values.
I have 6,000 rows of check detail. The check number is listed in both columns B & C.
If the check paid multiple invoices the check number is not repeated in columns B & C.
I would like to fill the cells in those columns with the appropriate check number. Data looks something like this-
item ck_01 ck_02 vendor ck_date
1 10100 10100 ABC 09-01-09
2 10101 10101 ALLIED 09-01-09
3 ALLIED 09-01-09
4 ALLIED 09-01-09
5 10102 10102 BEACH 09-01-09
6 BEACH 09-01-09
7 10103 10103 CAP 09-01-09
8 10104 10104 DART 09-01-09
How can I fill the balnks with the check number (above)?
I need a VBA macro for filling the blank cells in whole column "D" with some conditions.This condition work for whole column.
ABCDCN
EA
Polymer
DE
MA
Polymer
Medium
CN
EA
Polymer
Fill the blank cell in D column with "Easy", if Column A is "CN" and column B is "EA"
Fill the blank cell in D column with "Medium", if column A is "DE" and column B is "MA".
After Run the macro:
i.e
ABCDCN
EA
Polymer
Easy
DE
MA
Polymer
Medium
CN
EA
Polymer
Easy
I was used "if" condition but I can't able to run.
Works fine up to and including .List(i, 9) however when it bangs out with an error when it gets to .List(i, 10). Error Message reads: Run-Time Error '380': Could Not set the List property value.Invalid Property Value. If I Switch to using .column(10,i) I get the same error but for column instead
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String, HASH As String
Dim D1 As String, D2 As String
Dim i As Long
HASH = Chr(35)
'*********TEMPINFO********
D1 = "12/3/2007" '*
D2 = "12/9/2007" '*
Agent = "Team_Stats" '*
i = 0 '*
'*************************
glob_DBpath = ThisWorkbook.Path & "databaseTS-Stats.mdb"
glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_DBpath & _
"; Jet OLEDB:Database Password=********;".......................
So I have exported a canned report showing me payments by day and it is EXACTLY what I need EXCEPT the dates don't repeat (and it has spaces and some headings but those aren't a big issue). Anyway, I need to dynamically fill in these dates for an entire year.
To be clearer, column A starts off with 1/1/2013 and column B has payment amount, column C check number, D invoice number etc.
But the rest of the cells in Column A for 1/1/2013 after the first row are blank until you hit the payments for 1/2/2013. Then 1/2/2013 is listed once and then blank (or junk header data at a page break) until 1/3/2013 and so on. And of course there are different numbers of payments for each day.
I'm looking to dynamically fill in the dates so that I have a real data /flat file that analysis can be run on. I have some ideas ... Could probably incorporate a "do until" statement somehow...
I have a list of data in a column that has blanks along the way.However, I don't just want to fill it up with the one above, or the one below.
If the data is in column B, I want to fill it such that where the row above has the same criteria in column A, column B for the above row is returned.Otherwise, column B in the row below should be filled in instead.I have set up example data below to illustrate.
Initial Data Set:
A
B
1
Criteria
Number
2
a
1
I'm simply inputting zeroes and ones down Column A. Basically, I either put a "0" or a "1" in A1, then A2, then A3, etc. However, I want to create a Macro that allows me to simply type in a string of 1's and 0's and it automatically takes each 1 and 0 and fills it in down the column. This website has the basic idea (Automatically Moving from Cell to Cell when Entering Data (Microsoft Excel)) but I need only one number per cell and for it to move down the column, not across the row. Here's what I have so far:
Sub SAMPLE()
Dim str As String
Dim x As Integer
Dim y As Integer
str = InputBox("Enter string")
y = 0
For x = 1 To Len(str) Step 4
ActiveCell.Offset(y, 0) = "'" & Mid(str, x, 4)
y = y + 1
Next
End Sub
*I can't get it to only break down into one number per cell down Column A. I think the Mid function or something needs to change.
Here's the outline of my problem...I'm building a directory of hedge fund contacts and need help in cleaning data and automating the entry of fields that will remain the same. I have one workbook with 2 pages... One is for the Companies and the other is for the contacts that work at those companies.
ex. Company #551 is 1794 Management - on this page we have fields for address, floor, city, state, zip, phone, fax and website.
on worksheet 2 is where the contacts are kept... the identifier is #551... on the contacts page all contacts under a specific company number will correspond to that company.
Here's where it gets tricky.... for the contacts the addresses are all formatted differently - first I'd like to sort by management co and address and have excel copy the first listed address format and copy this to the other contacts for that company.... when the formula reaches the next contact with company #552 it will look for the new first address and use this one for all that companies contacts.
after the formula finishes the fomating I'd like the sheet to become intuitive( dont know if this can be done but what I'm looking for is say when I add a new row and enter #551 as a company and enter the new first name, last name, and job title - if the excel sheet noticing that all contacts under that format have the same address, phone, fax, company name etc...
I am working with monsterous excel sheets (named Data) and need to a column that is governed by the ZIP. I have a sheet named Source in my excel folder with all the matching information next to the excel sheet. The column my zip codes in Data starts on J6 to J290 and the zip codes in Source are from A2 to A2671. The information I am trying to pull from Source to Data are names and branches located in Source from B2:E2671.
What function will auto fill the columns with the correct information. If it can only be done by putting a function into each column, that is not a problem
As you can see, I have 2 worksheets. I want that all entries in columns B,D,E and F automaticly fill in in multiple tables in sheet2. Also, if possible that macro creates tables (for example: if I have 100 rows, I want to extract 100 separetly values from column B,D,E and F and to enable automatic creation for new table - for 101 entry )
here is the dropbox link of the file:
HTML Code:Â
[URL]
So, I want when I click on the button I want to automaticly import all mentioned values into sheet 2 (I have marked with RED where to put values from which column). In this example, I expect, when I clik on Print all tables button, to print 7 tables in sheet2. In sheet2 I have 2 tables as you can see.
I have a peptide sequence that is 4500 amino acids long. I have the numbers 1 - 4500 in column A and the corresponding letter code for the amino acid in that position in column B. I have done a bunch of other calculations and isolated small series of amino acids within the entire sequence (example: positions 25-42, 153-166, 381-297, etc).
I would like to fill column C with either a blank space or, if it is in one of the selected series, the letter code again. So that the first 24 rows of column C would be blank but rows 25-42 would mirror the letter shown in B. Then blank spaces again until we reach row 153, etc.
I've used a vlookup to paste the letters with the chosen parts of the sequence, but now I want to space them out along the entire 4500 so that I can see if there are overlapping areas between several sets of criteria for choosing series (these would go in columns D, E, etc).
I'm attempting to make a simple userform that inputs data onto an existing worksheet. I have the userform but would like to use a combo box to choose a "category" item of data, however I dont know what the categories are! I would like the combo box to, somehow, look at the spreadsheet and read off the already entered categories and offer those as choices.
A picture is worth a thousand words:
http://www.copestake.org/images/excel.png
Is there a simple way to fill the combo box (using the form initialize I assume) with the existing categories?
[Edited to link to image instead of displaying on board~admin]
My worksheet looks like this: column A - ID, B - Group #, C - data. When data has been entered for all the IDs in a group I would like a message box to appear stating end of group #.
View 4 Replies View RelatedI have previously used an Excel file - created by someone else - which had pre-entered data. It was set up in such a way that if I typed a person's ID number into a cell, their name popped into the following cell. I know that the names and IDs were pre-entered somewhere else - in another sheet? In a file linked some other way?
What is this called? How do I do it? I want to end up with a file where I can type a department code into one column and have the department NAME pop up in the following column. I am mostly doing this as a time- saving measure when completing long lists of supplies being distributed to different departments (long story.) If anyone can even tell me what this is called, so I can look it up correctly, that would be... sublime.
I have a userform and on it is a combo box with the weeks in each month in it. A user has to select a week to enter figures for and then enter the figures into text boxes on the same form. When the click submit (command button), based on what week they've already selected, my code will put the figures into the spreadsheet in the appropriate place.
Sub december1()
Worksheets("Dec").Activate
If Figures1.TextBox1.Value >= "1" Then Range("c16") = Figures1.TextBox1.Value
If Figures1.TextBox2.Value >= "1" Then Range("c17") = Val(Figures1.TextBox2.Value) / 100#
If Figures1.TextBox3.Value >= "1" Then Range("c18") = Figures1.TextBox3.Value
If Figures1.TextBox4.Value >= "1" Then Range("c22") = Figures1.TextBox4.Value
If Figures1.TextBox5.Value >= "1" Then Range("c23") = Figures1.TextBox5.Value
If Figures1.TextBox6.Value >= "1" Then Range("c28") = Figures1.TextBox6.Value
If Figures1.TextBox7.Value >= "1" Then Range("c29") = Val(Figures1.TextBox7.Value) / 100#...........................
Starting to struggle a bit here. Trying to create a user form, with help from this website I was able to create 2 dropdown lists based on data validation and information from another sheet in the same workbook. I have one cell B7 that uses data validation and sheet "Eq List" to create a drop down list of departments that the user can select from. Once B7 is filled in, it creates the 2nd dropdown list in B9 so the user can pick "Equip Desc".
Where I am trying to fill B11 which should automatically give me back the Mach ID that is related to B7 & B9. Am I going about this the right way? I have ttached a sample file to make it easier to understand.
I got my invoice and my journal. Once I finish my invoices I send all to the journal, but I am having a little problem.... Here is my code
View 2 Replies View RelatedI'd be very grateful if s.o. help me find solution for the following task:
I want to fill certain worksheet with data from external .tsv files. The .tsv files are with a almost a common name (something_date.tsv), located in folders for each month.
What I'm usually doing and want to automate:
- I'm opening the first .tsv file from the monthly folder;
- Creating AutoFilter on the first row;
- Selecting a custom criteria from the AutoFilter;
- Copying the cells matching this AutoFilter criteria;
- Pasting in a predefined worksheet (with AutoFilter on first row);
*All of the copied cells are not being altered in the predefined worksheet, i.e. the first rows of the .tsv file and the worksheet are the same.
- Doing exactly the same with the next file in the monthly folder (but pasting below the already copied cells in the workbook).
- Etc.;
- Etc.;
I've set myself a project to try and automate some repetitive filling in of word documents and would like a point in the right direction. I've done some research on the MSDN and some sites on the web. The way I was thinking of doing it would be,
make a template with either Fields or Bookmarks (which would be best?) for each piece of data on the paper work.Put the repeated data into an excel sheet (with a button for the macro to be assigned to).
write some VB script that will, define and "label" the data in the XL sheetopen the word templateenter the data into each Field/BookmarkSave as a new file in a "New" folder (name of file and folder taken from the Data)open the next template and repeat.
would this be the best way of approaching this problem?Whats the best way of defining points in a word template, Fields, Bookmarks?what objects would I need to use to rename the folder?
The below formula is in Col F6, how do i get this formula to adjust to data range without manually filling down, my data changes everyday therefore i need the formula to auto fill according to data range.
=IF(ISBLANK(E6),"",Summary!$I$2-E6)