I am writing a equation in if statement...Is there a procedure to select the the second and third letter from a sentence and write a condition based on that...
I mean, say if I had 1XY in cell A1 and I want to write a if condition based on the XY from 1XY term, how can i do that?
I looked for a long time through the past entries and couldn't find an answer for this specific situation. I have a spreadsheet with multiple occurances of names in column A
A
green
green
green
blue
blue
white
white
white
and in column D I need a total of how many occurances of that name there are. that total needs to be listed in that row for that name specifically. There are many names on there currently and many will be added via copy/paste from another spreadsheet so i need one formula to paste into each row to cover ALL recurring names. Basically i need to enter in column D "=COUNTIF(A:A,"a term that would total the reoccurance of each name from the A column the corresponding D cell")
A D
green 3
green 3
green 3
blue 2
blue 2
white 3
white 3
white 3
The goal is to be able to copy/paste new entries into the spreadsheet, sort them by name, and have them automatically update the totals in each row even if they are new names. i guess it would be a "number of occurance of each term" code to cover all terms.
I have attached a spreadsheet...I'm trying to select Range A1:N16 using VBA code...
Range(activecell,activecell.end(xltoright)).select
Range(selection,selection.end(xldown)).select
When I use this code is only gets A1:N6...: is there anyway to get it to select the range all the way to the end of the data. I tried CurrentRegion and some other stuff but can't get it to work.: The range may go upto line 500 or 3 I just never know.
Use a drop down menu (Combo box for example) to look at all the sheet names (these will be names of people, sheets added all the time) be able to select a name which when selected takes me to that sheet. So the amount of sheets will increase so everything will need to be dynamic.
I dont want to see all the list behind the drop down so that why i was thinking of a combo box.
What code to use to tell VB to select the "search" button on webpage "http://66.179.23.21/DP1/Metroplex/FairfaxCounty/permit/WIZ_INSPECTION.asp"?
View 9 Replies View RelatedSub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long
Dim newRow As Long
Set ws1 = Sheets("SUMMARY")
Set ws2 = Sheets("WIP")
Application.ScreenUpdating = False
With ws2
Range("A2:C2").Select
Range(Selection, Selection.End(xlDown)).Select ' Clear
End With
x = 10
lastrow = ws1.Range("C5").End(xlUp)
Do
The code is run from ws1. I put in the .select for now because I don't want to loose my info on ws1.
I trying to clear the range A2:C2 all the way down to where info is before doing the Do Loop which will repopulate the same range from ws1.
If the rest of the Do Loop code is needed, let me know.
know the VBA script to select only specific cells within a worksheet. To be more precise, on sheet 1 - A2, A6 and A10 cells are filled with red color. I want only those cells which are highlighted in red to be selected, copied and pasted to sheet2.
View 3 Replies View RelatedI am trying to select a block of data from a remote worksheet but I keep on getting the following error
Run time error '1004':
Application-defined or object-defined error.
I have attached the file and I am working on worksheet 21 "PT Monthly Report 1"
I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'. Loop Through Rows & Copy Each Row To New Workbook
For lLoop = 2 To 251 'first row of data to last row.
If ws1.Cells(lLoop, 4).Value = "x" Then '4 = Column D
ws1.Activate
ws1.Range("e" & lLoop & ":g" & lLoop).Copy
ws1.Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Rng1.Copy
Workbooks.Add
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("A1").Select
ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls"
ActiveWorkbook.Close
Else
End If
Next lLoop
how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.
I am trying to calculate what date 2/3 of a lease would be. If start date is 5/8/13 and expiration date is 3/19/12 on a 36 month lease, how do I find out the date 2/3rds into it?
View 2 Replies View RelatedI am trying to sort first by year then by term (i.e. summer, fall, spring) for a school year. I know how to do custom sort so i set up a list. The problem comes here:
Term Year
Spring2002
Summer2002
Summer2002
Summer2002
Summer2003
Summer2003
Summer2004
Summer2004
Summer2004
Summer2004
Summer2004
Summer2004
Fall2005
Summer2005
Summer2005
Because there is no spring 2005, the last entry it sees is summer so it puts fall 2005 when in reality i want summer 2005 next. How do i sort so that any time there is a new year in the year column, it starts the sort over with spring in the term column?
How would I go about summing every 10th term using the offset function, since the data needed is on a different sheet.
An example of what I have to sum every 10th row.
=SUMPRODUCT((MOD(ROW(Z1:Z100),10)=0)*(Z1:Z100))
I need a formula to calculate the end date of a monthly term when the start
date is not the 1st of the month. This is to show the proration for the
first month only, so the end date should be the last day of the initital
month/yr. Then when showing the rates for each period, all subsequent
periods would start on the 1st of the month. For example, start date
8/16/06, initial end date needs to be 8/31/06. The current forumla I am
using is: ....
we still run reports on tiny term emulator v4.3.1720 is there a way to have each report export to excel like this report each colunm each row in there own
cell here is a example of a report
I am trying to use a lookup formula to search for a word in a column, and return the value of the column directly to its left. I know that this cannot be done using the vlookup function, and I am not able to move the columns around. Is there another possibility? In plain English then, I would like cell E2 to look up the value of D2 in column B2:B4 and return the relevant value in column A2:A4.
View 3 Replies View RelatedI have a 14 000-item list of product codes that are generated from different attributes. What I am trying to do, is to create a cover sheet where the user can select attributes from dropdown lists and get the first matching product code. I have tried using index/match, but I am struggling with wildcard lookup.
Example of generated product code:Cell A1:
ABX**J
Here, AB is the product group, X is the pressure class, ** is material (which is, for the example, unknown), and J is the end connection. What I now want to do, is to look in the long list of codes and find the first match:
Cells A2:A5:AAY02J
ABY03F
ABX01J
I have a dropdown list in C24:C50 (=CategoryList) with data validation and a sub list in D24:D50 (=ItemList) with data validation. I am looking for a way to have code automatically run after selecting an item in the data validation dropdown list in column C.
Example; I click on C24 and make a selection. I what it to trigger code that would move me to D24 and open up the data validation list in D24. After the selection in D24 I would like it to move me back and down 1 row to C25. I have not found anything directly related to this but I have found that code can be run after a selection in a valadition list.
I could really use some excel function. Within my office, we work with several hundred files. Each employee is assigned files based on the last two digits of the file number. What I need is a way to identify what file is assigned to which employee based on the term digits of the file.
So for example, I have the following list of files:
1002856101
22781721
1044863815
1008799064
1044779765
1006511115
1007641804
0729939256
5303486020
8364709
0014094759
0019921519
8172717
I'm able to do a formula to get the term digits (meaning the last two numbers), but i'd like to have another column that can put names based on the term digit column. For example, Tom might work 00-04, Sally works 05-09, Greg works 10-15, Lucy works 16-21.. etc
I came across the below IF formula that is exactly what i need, except it only works for two associates and not the multiple that i need.. but it looks to be a good starting point nonetheless.
------------------------------
=IF(C2<50,"Sheryl","Lisa"). You should enclose Sheryl and Lisa with quotation marks as these are string values.
You can also use (if A2 is where the Loan # is):
=IF(Right(A2,2)*1<50,"Sheryl","Lisa")
The formula will acquire the last 2 digits of the loan and check it if it's for Sheryl's or Lisa's.
-----------------------------
I'm using excel 2007 for analyzing my students grades. I ran two exams and look for a way to compute a final grade score, which should be equal to the grade of the first exam for those students who took just the first exam and should be equal to the second score for those students who took the second exam. (Clarification: for those students who took both terms, their final grade should be equal to their score in the second term).
View 9 Replies View RelatedI need to produce a summary of the monthly spend with parts suppliers for a number of vehicles, with each vehicle having it's own worksheet. The suppliers' names are in column C with the costs in column H.
So what I need to do is sum the results of column H on each worksheet when the supplier's name (which is on the same row) matches "Triple 7" and it's in the same month. The idea is to produce a sheet which displays the total spend each month with our suppliers.
I am building somewhat of a rudimentary search engine that looks for a name, address or unique identifier, using an Index Match formula that searches with a wild card:
=INDEX(Name,MATCH("*"&F3&"*",Name,0))
"Name": is the defined name for column C on a separate sheet.
F3: is "search field"
The current formula (above), only returns the first match. The source data is only 927 lines so not overly large. I would like to have an additional page that will show the potential results from the wildcard search via a formula. Therefore needing results; 2, 3, 4, 5 etc. from the Index Match. I have searched high and low for solutions but I have found none in regards to retrieving multiple results from a wildcard search, only on a specific term.
=COUNTIF(Sheet1!$A$1:Sheet1!$A$32515, IndexSheet!B2)
+COUNTIF(Sheet2!$A$1:Sheet2!$A$32515, IndexSheet!B2)
+COUNTIF(Sheet3!$A$1:Sheet3!$A$32515, IndexSheet!B2)....
+ COUNTIF(Sheet20!$A$1:Sheet20!$A$32515, IndexSheet!B2)
Above formula check for the value in IndexSheet!B2 and searches it across all 20 sheets and returns total sum
Is there any shorter version of this?
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.
Is it possible to convert a students DoB into their term of birth (ToB).
I.e. a student has the DoB of 1/1/2001 which would mean their ToB would be 'Spring'
I suppose what I'm after is...
If a DoB falls into one of three groups
1/1/year to 30/4/year = 'Spring'
1/5/year to 31/8/year = 'Summer'
1/9/year to 31/12/year = 'Autumn'
The year of birth is irrelevant, it's the month which determines the 'term of birth' (Spring/Summer/Autumn).
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies View RelatedI've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.
View 8 Replies View RelatedHow would i go about selecting a row to the left of any active cell. As far as column B
View 14 Replies View RelatedI'm trying to make a macro that selects the 3 highest values and give these cells a blue backcolor. (The code should skip any blanc cells).
View 10 Replies View Relatedi did write code to search for the data..and if the data has more than one record then a button will be appear to find all data that related to entered data... and user will have option to press on the select button to select the wanted recored and delete it or update it....the problem is that the select button show the selected data in the form but its not selecting the right data in the sheet. So when i click on delete button it delete the first row which has the same data that entered by the user.
for example, user wants to see all data the belong to the user "Tim", the forms will show all data related to the use Tim... but i want to delete the selected row not the first row with the name of Tim!
i learnt the code from this link [url]
here is the codes for find. find all, and select
Private Sub cmbFind_Click()
Dim strFind, FirstAddress As String 'what to find
Dim rSearch As Range 'range to search
Set rSearch = Sheet1.Range("a2", Range("a65536").End(xlUp))
strFind = Me.txt_num.Value 'what to look for
Dim f As Integer
With rSearch
I have a table of adressess and clients with contacts ie
a,b,c,d
client,contact,address,select
in the list there are about 300 entries.
what i want is to put an S in the select column on those clients i am due to visit and then these records to be exported into say a word doc to print off so i have a list of visits i have to do with company contact and addresses
