After adding worksheets, data to ranges, etc. I want to step through each worksheet and "Select" or "Activate" a specific cell (Mainly to clear all the range selections).
My code does the job except the second to last part of cell selection/activation.
Win7, Office 2010
Im creating some labels for users to be able to enter data with ease. i want to add color to one cell by selecting the other. example:
if i put a (date) in cell B1 i would like cell A1 (Day) to colored once a date is in cell B1
Note: I am using excel 2010.
I know there is a simple answer for this, and I did a search but could not find what I am looking for. I am comparing two different workbooks. I loop through the first workbook (oldfile) and then find the corresponding value on the other file. I find the corresponding value using
With Worksheets(1). Range("b2:b" & blah)
Set c = .Find(MyVar, LookIn:=xlValues)
I know the cell address of the value that was found by
firstAddress = c.Address
Now I need to offset a few columns and copy the data. If the cell address was in R1C1 format then I could do this easliy by ActiveCell.Offset(Row, col + Offset).Copy
(where row and col are given from the c.address function. But I don't know the RC value since the c.address provides the value in "A1" format. I do not want to actually go to the cell and then do an offset and copy, since it is a large spreadseet and would be slow. I don't want to do a bunch of string manipulations (unless it easy). Is there a way to have c.address output it's values in R1C1 format? Or is there a different command I could use?
I wish to select all the cells which contain the word " Available " in column A at once . But i didn't find any option to do so using excel's Go TO Special feature.
I am using Excel 2010.
I have a table in columns C:J, starting in row 7. I have a macro that selects and copies the whole table (after I filter the table) and pastes to another sheet. Recently I've realized that I can only copy/paste C:I or it will mess up my formulas during the process.
This is the code I have right now, which works beautifully for the whole table:
Code:
ActiveSheet.ListObjects("Table36").Range.AutoFilter Field:=6, Criteria1:= _
"=Alan", Operator:=xlOr, Criteria2:="="
Range(ActiveSheet.ListObjects(1)).Select
Selection.Copy
What I need is to know how to copy everything in the table except column J. I can't just copy a certain range cells because the number of rows is variable. There has to be a way to modify the ActiveSheet.ListObjects("Table36") right?
Using Excel 2010.
I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'
I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'
See the attached image to make it clearer..
Excel2010
Excelhelp.jpg
I have Columns A to C which are hidden. I have tried to unhide these bey selecting the entire worksheet but to no avail. I am using Office 2010.
View 4 Replies View RelatedThe for loop don't work in my excel 2010 macro. Probably the problem might be the Range("Fi") construct with the variable "i"?
Dim i As Integer
For i = 4 To 10004
Cells(i, 63).Select
Cells(i, 63) = Application.VLookup(Sheets(3).Range("Fi"), Sheets(4).Range("F:AY"), 45, False)
Next i
I have various spreadsheets that get into infinite loops sometimes when I search for things. Hitting break breaks it, and the search window (find all) shows the same exact entry hundreds of times.
This is Excel 2010.
How can I get this code to loop through every line on sheet?
Code:
Selection.Copy
Windows("SheetA.xlsm").Activate
Cells.Find(What:="Part Number", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
[Code] ..........
I am running excel 2010. I have set up a variable " testvalue" type variant
I would like the prog to step through a row of data which can contain text or numbers. If any number is greater than 3,000,000 I want exit the do. However, if no number is greater than 3,000,000 I want to record this as a "bad file". I have the follow code which trips at the first "case".
Code:
testcol = 1
testrow = myTextRows - 1 'penultimate row
Do While Len(wbText.Sheets(1).Cells(testrow, testcol)) > 0
testvalue = wbText.Sheets(1).Cells(testrow, testcol).Value
Select Case testvalue
[Code] ........
getting a function working within Excel 2010. What I'm trying to do is to first look in a specific directory and loop through each foldername then check to see if the foldername exists in column B of my worksheet. If the foldername exists then check Column E of the same row for a specific value ("assigned") and then make sure column F of the same row has no picture inside the cell. If those three things exist (column B foldername and column E "assigned" and no picture in column F), then add a picture to column F of the same row as the foldername with a hyperlink to a filename of "notes.one" in that specific folder. Then just loop through each foldername in the specific directory until all foldernames have been checked.
View 1 Replies View RelatedTo 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 RelatedWin7/2010
I have an array PeopleList(6,320) that contains
PersonID, FirstName, LastName, Email, Phone, Notes
What I'm trying to do. I have two separate requirements:
(1) To add the whole array to a listbox on form initialization - see Sub UserForm_Initialize()
(2) To clear the listbox and re-add only certain items based on what's typed in a textbox - see Sub txtSearchTerm_Change()
I have two errors:
Error 1 in UserForm_Initialize()
The listbox contents need transposing! It is displaying as
Code:
1 2 3 4 5
Tom Ben Heidi Julie Mark
Smith Jones Evans Simpson Petersen
x@yo.com a@bo.com c@do.com e@fo.com g@ho.com
02071001022 02071001026 02071001027 02071001028 02071001029
Friend
When it should be displaying as
:
Code:
1 Tom Smith x@yo.com 02071001022 Friend
2 Ben Jones a@bo.com 02071001026
Is there a way to transpose the array?
Error 2 in txtSearchTerm_Change()
I cannot find anywhere - even on MSDN - all the information I need how to correctly add a single record to a multiple-column listbox! What I'm trying is:
Code:
For i = 0 To UBound(SearchList) If InStr(1, SearchList(i), SearchTerm) 0 Then
With lstPeople
.AddItem
For j = 0 To UBound(PeopleList, 1)
.List(c, j).Value = PeopleList(j, i)
[Code] ......
How do I add a record to the listbox????
Full code for reference:
Option Explicit
Private PeopleList As Variant
Private SearchList As Variant
Private Sub UserForm_Initialize()
[Code] .......
I'm trying to create a popup message that appears when a given worksheet is activated. I can't figure out the syntax to get it right.
View 3 Replies View RelatedI am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.
I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.
A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."
The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.
Code:
Sub Email()
Dim rng As Range
Dim OutApp As Object
[Code].....
I have created a macro that loops through column 4 and gives the value of 2 other cells acording to what is found.
The problem comes when the macro only works sucecefully if i click on a cell in that column. Therefore i think the macro is not doing this.
Im sure that i have used the right code does anyone know what is wrong.
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
I am trying to write a small bit of VBA code, so that I can record the row and column of the active cell. I have got it working when I CHANGE the value of the cell, but I need to get the code to fire off when I ENTER the cell.
The code section is below:-
Private Sub Worksheet_Change(ByVal target As Range)
'Store the row and column into G1 and G2 for the drop down loading
Call RowNum(target)
End Sub
[Code]....
how I can do this, as I cannot find reference to a Worksheet_Enter function ?
I have a requirement where I have to count number of "-" character in particular row
I am using a excel database for promotion records of my team....The data base works as follows. I am using countif function to calculate number of associates in specific grade for specific month. One associate is getting promoted to next level, I will add one more row with the same employee name with change in designation and diff start and end date. I want to generate report for monthly promotions, The report should depict number of promotions in each month. ie in Jul there is one promotion from trainee to Engg for EMP1. There are hundreds of employees, eligible for promotion in each month.
A B C D E F G H I J K L
Emp Name Desig From Date To Date Apr-13 May-13 Jun-13 Jul-13 Aug-13 Spt 13 Oct-13 Nov-13
EMP 1 trainee 4-1-13 6-30-13 trainee trainee trainee - - - - -
EMP 1 Engg 7-1-13 11-30-13 - - - Engg Engg Engg Engg Engg
Upon workbook open I have specified that I need a specific sheet & cell selected - so that the user can start typing as soon as the sheet is activated.
The cell is highlighted but I can not type into it unless I manually re-select it with the mouse or keyboard. Below is what i have attempted as well as a few other experiments. I even tried using the same code on sheets("ENTRY") using Private Sub Worksheet_Activate() - which works great as long as the workbook is already open but again not when freshly opening workbook -
Private Sub Workbook_Open()
'====================
Sheets("ENTRY").Select
If Range("E2") "" Then
Range("F11").Select
Else
Range("E2").Select
'Range("E2").Activate
'ActiveCell.FormulaR1C1 = "test"
End If
End Sub
When I use the mouse pointer to select a cell I can't use the arrow keys to move to another cell while the pointer is over the cell and I can't edit the cell while the pointer is over the cell. If I move the pointer away from the cell then I can move around and edit as normal therefore I don't think this is a scroll lock issue.
This issue also happens when I select a tab. If I select a tab and then leave the pointer over the tab I selected then I can't use the arrow keys to move around the worksheet or edit a cell; if I move the pointer away from the cell then I can move around and edit as normal.
I am using MS Excel 2010.
I am trying to see if it is even possible to have data validation applied to a specified range of cells, but if the value of cell 'B3' is '1' then the data validation will not run on the other cells (H4:G7)
View 2 Replies View RelatedI have an Excel 2010 spreadsheet consisting of many worksheets (20 or so). Each of these worksheets contain detail level data regarding different projects. One of the columns in these worksheets is the 'Status' column (column F). There is conditional formatting on this column where if the text is 'G' then change background to a green color, 'Y'=yellow, 'R'=Red and 'U'=Grey.
The first worksheet is a summary sheet that I would like to pull information from each of the detail worksheet's columns B, D, E, G and H if the status column (Column F) is 'R' or 'Y'.
The number of rows in the detail worksheet can change each week (as few as 0 and as many as 100)
I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:
Number of variable entered: 5
q9001
q9002
q9003
q9004
q9005
q9006
etc.
Should give me: "q9001 q9002 q9003 q9004 q9005"
I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.
(Using Excel 2010)
Formula that will repeat a cell number as it drags down and as soon as the number changes. I am using helper column that shows the cell number. I need to drag this down about 1000 rows.
Excel 2010
A
B
C
1
Helper Column
Desired Result
[Code].....
I am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
Ive also attached the worksheet
we have a Excel 2010 worksheet containing multiple tables.
Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10
Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17
On a separate worksheet we want to calculate with the first worksheet's values.
For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange
How do we do such a calculation/formula?
I am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
I am using Excel 2010.
In my worksheet I have 'Column A' and 'Column B', In 'Column A' are product I.D. numbers. In 'Column B' is a text description of the product, whose I.D. number is in 'Column A,' and should also contain the I.D. number from 'Column A' somewhere in the midst of the descriptive text. However, some of these in 'Column B' do not.
I need to create a function that looks for the value in 'Column A' and determines whether or not it is present in the text of 'Column B'. Therefore, spitting the answer out in 'Column C' so that I can copy it down for 100,000 cells.
