I just stumbled onto this forum while Googling around, and it looks great. I'm pretty new to VBA coding so please bear with me. Effectively what I'm looking to do in this mock-up file, is anywhere the currency is "EUR" in column A, I'd like the relative cell in Column G to change to "INSERT TEXT", else I want it to untouched (i.e. to keep the comment it currently has).
View 6 RepliesI am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
I have an Excel sheet with a column (column O) containing expiring dates. The dates represent the day a certain product expires, and for each date, a name is attached (in column A). So there are 500 dates in the sheet, each with a different corresponding name. How would you have Excel create a list, in a new sheet, of the names that are expiring in less than 90 days from today? That is to say, if the date in a cell in column O is less than 90 days from today, then the information in the aforementioned cell and the information in the cell in column A (in the same row) is COPIED (not moved) to a list in a new sheet?
View 2 Replies View RelatedI would like to fill in a table based on a small database
this table then creates a graph.
But I need to look at 3 criteria before I can fill in the table
namely: customer - weeknumber - weekday
based on these items the time should be filled in accordingly.
Can this be done with regular formula or should I try VBA ?
please have a look at my file the data should go from sheet input to sheet ACL
based on the above mentioned criteria
Well, after reading "Best Practices", it looks like I've got lots to learn about Excel and VBA Hope I don't break too many rules. see code below. I have a form that uses a set of dynamically filled combo boxes. The difficulty I'm having is in filling the combo boxes with correct data when updating an existing record. As per the code below, the Activecell. offset(0, #) references in the "if" statement in the For..Next loop process correctly, but Activecell references that fill the combo boxes seem to behave like an absolute references to Row 1, which are the Column Names. I've tried creating an Activecell.offset(0, 0) before every fill combo box statement and then using Activecell.offset(i, #) to reference the relevant cells, but to no avail. Obviously, I'm missing something here...
Private Sub cboUserName_change()
Dim usrnam As String
Dim found As Boolean
Dim i As Integer
Dim intRowCount As Integer
usrnam = cboUserName.Value
found = False
If usrnam <> "" Then
ActiveWorkbook.Sheets("Data").Activate
Range("A1").Select
intRowCount = Range("A1"). CurrentRegion.Rows.Count - 1 ................
I have a worksheet that when a row changes based on the value of column B, I want to remove all of the formulas found in the row but keep the existing values, and then change the color of the row.
In the sample file attached, when the value is "Closed", that row will keep the existing values and then it gets grayed out. Rows that are still marked "Open" need to retain the formulas in case other information changes.
I have tried copy/paste special using autofiltering but that doesn't work because of the hidden lines. This file changes on a daily basis and I need a quick way to update the file.
SampleFile.xlsx
how can I automatically fill cells with certain colors based on the value of the cell. (i.e. I want to search an entire workbook and fill cells with values between 80 and 99 green, 60 to 79 yellow and 0 to 59 red.)
View 9 Replies View RelatedI need to be able to fill a cell in the same row based on values in two other cells in different columns.
Current Layout. Call these columns A, B, and C
I have 3 different states I work with, OH, IL and PA. There are different tax exemptions based on whether or not the class is RES or COM.
Conditions:
State = PA, Class = RES, Tax Exempt = 1
State = PA, Class = COM, Tax Exempt = Null (not "0")
For all other states, Tax Exempt will always = 1
I need the VBA code that will autofill column B with the proper Tax Exempt status based upon columns A and C.
The code I have now just looks at the first value in columns A and C then fills all of column B with that number.
refer to attached worksheet. If value in D7 is less than zero; then D8 = 0%
If D7 > 0, then D8 = value in C8, and this value must also be copied into 9 adjacent cells in row 8 from D8 onwards.
I have 2 columns, the second column has some empty cells in the end of the column
What I would like to do is to fill in those empty cells but only as many as the filled-in cells in the first column
(I have already tried the specialcells(xlCellTypeBlanks) but that did not work as the empty cells are not always "blank" as well)
I am looking for a way for Excel to fill in cells in a column that has data sporadically in it. I am importing a text file into excel and saving it as an Excel file.
In the example attached, I need the numbers filled in all the way down to the next number. I don't care about the text, just the numbers. I realize I can copy and paste, but these files can be several hundred rows long and it seems there should be a better way.
i have some problem in display the highlight cell to another worksheet. Below is in module, i would like to display the highlighter cell to another worksheet.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/2/2006 by user
'
'
Range("I351").Select
ActiveWindow.SmallScroll Down:=-12
Range("I349").Select
ActiveWindow.ScrollRow = 1
Range("E1:E400").Select
create formula/condition to colour fill different cells based on multiple Criteria.
View 3 Replies View Relatedhow to sum/count cells based on background color [url]
how to delete cells based on background color?
I want to highlight the cell of Column 'A' of excel only when it does not contain the text like "Verify", "Validate" or "Evaluate" in its content if, the value in corresponding cell of Column 'B' holds the value 'Y'. Secondly, the column 'A' can't contains the words like 'Verify', 'Validate' and 'Evaluate' if the corresponding cell in Column 'B' holds value 'N'. So just need to highlight those discrepancies if its there.
Column A
Column B
Expected Action
Press F3
N
Its Fine
Verify this..
Y
Its Fine
this....
Y
Need to Highlight Cell of Column A as Value in Column B is Y but the column A does not contain any value like "Verify", Validate" or "Evaluate"
Verify This.
N
Need to Highlight Cell of Column B as the Value of Column A contains words like "verify", "validate", "evaluate" but corresponding cell value in column B does not hold value "Y".
I am using the cell validation command to create a drop down list on sheet1. Drop down is created from sheet 3 column A
I would like sheet 4 to be filled by information from both sheets. first line would have item chosen from drop down and the next line would be sheet 3 column B.
It is for a catering company. Sheet 1 (menu) is the creation sheet. I would like to use a drop down list to choose from a long list of appetizers. Those of which are on sheet 3 (app list) and held in column A (1-42). As that occurs, I would like sheet 4 (prep list) to generate a list of operations. A1 (prep list) would reference the item chosen and A2 would be the list of operations (sheet 3(app list) column B)
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
When I enter barcode X in cell A1, I want B1 to say supplier and C1 to say product name and D1 to say package quantaty etc.
And if I enter barcode Y in cell A2, I want B2 to say different supplier, and so on, hopefully you get the picture.
The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.
View 2 Replies View RelatedSuppose in cell A1 I type in the number "100" and in A2 I type in the number "5"
What I want to do: Have the number "100" repeat 5 times in a vertical list (so 100 appears in B1, B2, B3, B4, B5)
I need to fill 2 other cells with varying colours dependant on what is chosen within another cell.
I have a drop-down list in cell A2 to select either ON SITE, ACTIVE or CANCEL.
What I need then basically is...
if A2 equals 'ON SITE' then fill other two cells (A3 and B1) GREEN
if A2 equals 'ACTIVE' then fill other two cells (A3 and B1) YELLOW
if A2 equals 'CANCEL' then fill other two cells (A3 and B1) RED
[File attached]
I have a calendar set up with dates going along P6:CU6. Column C9:C27 has various status' for the job such as "Complete" or "In Progress" etc. Next to that in rows D and E I have start and end dates for the job. What I am trying to do is change the fill colour of the empty cells that are in line with each job, so that between the correct dates they are the corresponding colour to the status. So a complete job on row 9 that started on 26/05/14 and finished on 28/05/14 would have three green cells along Q9, R9, and S9.
I have tried an IF/AND/OR statement which looks like this:
=IF(AND(Q6>=D9,Q6<=E9),OR(IF(C9="Planned","P",""),IF(C9="In Progress","IP",""),IF(C9="Late","L",""),IF(C9="Complete","C","")),"")
I am getting a #VALUE! data type error, I have tried reformatting the cells to various date formats but no luck so far. This could be because my dates are set up as =P6+1 etc. but I am not sure. The error only occurs when the cell falls within the dates, otherwise it remains blank.
I intend to hide the value in the cell and use conditional formatting to change the cell colour based on the hidden input.
Once this is working I also need to take weekends into account, column G has any 2 value combination of Y and N to say whether the employee is working Saturday, Sunday or both. Row 7 has a "1" value underneath every Saturday and a "2" underneath every Sunday. I can imagine how this would work but combining it with the above formula could be complicated.
I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.
Code to fill these blank cells automatically with some specific texts based on the attached conditions.
I have attached the workbook for more details. FillCellsTest.xlsm
I have a excel sheet of data numbers which blongs a packet of inventory where i found number series like this:
Start Range End Range Qty
2101200 2101499 300
I have to draw this onto a series like 2101200 then 2101201 then 2010202 etc.
but some time these numbers are in qty 30000 or more. I have questions
1- If any macro colud fill series suppose if write number in A2 and qty in B2 so it reads the qty of b2 and fill the series in A. If i write 1 in A2 and write 50 in B2 so the series should be auto fill 1 to 50.
My Second question is opposit of my above question.
2- if i have different number series in column a and i want them to be as start number in column b and end number in column c and qty in column d based like mentioned below example.
Series [b]Start Range End Range Qty
1 1 1 1
3 3 3 1
5 5 7 3
6
7
I've created seven names in my data validation source list on Sheet 3 in the following range of cells, A2-A7. My data drop down validation list is located on Sheet 1, A2 in the same workbook. I would like to trigger a macro based on the name I select in this drop validation drop down list that will fill out an address, telephone number, and email address in the following cells next to my data validation drop down list on Sheet 1: B2, B3, and B4. I want it to be designed so that users will select a name and have that person's corresponding address, telephone number, and email address fill right next to their name on the same sheet.
View 6 Replies View RelatedI am using this formula but I don't know how to get it to populate more than one cell.
Here is the formula:
=INDEX(APPROVED!$A$3:$A$1000,MATCH($F$4&$H$1,APPROVED!$D$3:$D$1000&APPROVED!$C$3:$C$1000,0))
It has multiple matches but I can only see the first match in the cell.
how to enter data in non-adjacent cells using a fill command.
Here is what I am trying to do:
in the column, I am holding ctrl button to select every 10th cell down the sheet. I need to enter a date in every selected cell that is exactly 7 days apart, i.e., 11/7/08 then 11/14/08 then 11/21/08, etc.... but no other dates or data.
I have tried to figure out a way to do this other than manually, but am confounded.
Here's what we need to do:
We are compiling one master spreedsheet from many smaller ones and I need to figure out how to copy them quickly and efficiently. For example:
Small Spreadsheet:
Column B is marked as P, M, C or S. This represents the type of the account.
Column C is the name of the account.
Master Spreadsheet:
We have a column for each account type (P,M,C,S)
Can we use a formula/macro to automatically copy columns B and C from the small sheet and organize them accordingly in the Master Spreadsheet?
For example:
Small spreadsheet:
P Account Name1
M Account Name 2
M Account Name3
C Account Name4
M Account Name5
would appear in the Master Spreadsheet as:
P M C
Account Name1 Account Name2
Account Name1 Account Name3 Account Name4
Account Name1 Account Name5
I have data like in excel, which I uploaded and I need "simple" thing, If in column G is text Australia then copy from D column each cell which has "AUS1*" + copy the same number cell from C column and copy it into cell A and B. I hope I wrote it understandable
View 2 Replies View RelatedI'm not sure why this doesn't work on the dummy data?
Problems:It doesn't recognize the September 2011 datesIt is supposed to pull dates between July and August (i.e. 2 dates) and pull everything except those dates.
The date strings have to be variable based since it can't be hard coded.
Code:
Sub copyrow()
Dim rc As Integer, row As Integer, i As Integer
Dim mm As String, fdt As String, pdt As String, mo As String, yr As String
Dim Date1 As String, Date2 As String
mm = Month(Date) - 1
mo = Format(Now(), "mm") 'ex. "08"
yr = Format(Now(), "yyyy")
Date1 = mm & "/01/" & yr
Date2 = mo & "/01/" & yr
[code].....