I have a database application which appends an "A" to the sequence number each time an entry is amended and then posts it as a new row in the database. This means there can be multiple entries with the same number but with different amount of "A"'s afterwards.
e.g.
Cell A6 contains seq no 1
Cell A7 contains seq no 2 - Seq no 1 is subsequently amended so...
Cell A8 contains seq no 1A - 1A is subsequently amended so...
Cell A9 contains seq no 1AA - and so on and so forth..........
What i am trying to achieve is a formula which will look at the record number which is required (i.e. 1) and will search for record 1 with the most number of "A"'s appended. This way the user will only be amending the most up to date record.
How I can show the longest string in a column, I've tried to find a formula that does this but it only shows the number of characters, not the actual string contents.
I'm looking for a formula which returns the longest consecutive series of occurrences of 2 chars in a text string.
In the case I'm trying to count the longest consecutive number of nine-spares (9/) in a bowling game.
For example:
|7/|X|9/|9/|9-|9/|9/|9/|9/|X8/|longest series of 9/ would be 4. |9/|9-|9/|9/|7/|9-|(8)/|X|7/|9-|longest series of 9/ would be 2. |9/|(8)1|X|X|72|63|7/|8/|8/|9/9|longest series of 9/ would be 1.
I have a list of dates in column A (sequential from low to high). I have corresponding rainfall data in column S.
I am trying to find the longest run of 0's in the rainfall data and return the start and end dates. It would be great if i could tell it (in cell C8) to find the longest run below this number. ie: i say i want to find the longest run of numbers below 5.
I have a spreadsheet with a couple of hundred rows on each tab that looks like this: A1: #1 PENROSE TIJUANA MAMA PCH 2620039190 665794 12.00 CT 1 Front 1 1 1. The only constant is the 10 digit number followed by the 6 digit number in the middle and I want to use it to parse the text string. I want the output to be everything prior to the 10 digit number in A1, the 10 digit number in B1, the 6 digit number in c1 and everything else in D1 and so on through the rows.
I need to store a known index, unknown value in a variable so I can increase the value and use it in a database. It is used as a version number for a part and the versions go like: "00" -> "AA" -> "AB" -> .... -> "AZ" -> "BA"... etc.
VB: If tool.Worksheets("TRB Database").Cells(A, "R").Value <> ThisWorkbook.Worksheets("Design Calculator, Q").Cells(7, "C").Value Or tool.Worksheets("TRB Database").Cells(A, "AA").Value <> ThisWorkbook.Worksheets("Design Calculator, Q").Cells(5, "K").Value Or tool.Worksheets("TRB Database")
[Code] ....
That is a part of the increase and when I try to increase "AA" by 1 it goes to "B" and not "AB".
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set Target = Me.Range("c4:f100") Dim iSold As Long, iColumn As Integer Dim strTitle As String If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub iColumn = ActiveCell.Column Select Case iColumn Case 3
An what i need to happen id the input box saying "Please enter quantity of (whatever is in cell A11) assuming i am clicking on E11
so in other words, in cell A11 it says "MONITOR", i need the input box to pop up and say "Please enter quantity of MONITOR "
I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.
After doing a search, I need to ask an Excel 2003 question. I'm creating a tool to open three sparate workbooks. One is the driver and I need to use it to pull data from the second and then to place that data along with additional data into the third.
My driver data may consist of a single value or multiple values separated by semi-colons in a cell. A single value works fine. The first value in a multi-value condition works fine. the second find, however, gives me an object error. I'm using the " split" verb to separate the values. Here's my code. Have you any idea why the second find is throwing up this error when the first find works correctly?
varData = Split(strRef, ";", -1) For J = 0 To UBound(varData)
varSrchVlu = Trim(varData(J)) ' do the ARIS Exrtact matching Workbooks(strManualFile).Activate Workbooks(strARISExtract).Activate ' Activate ARIS Extract Sheets("Processes").Cells(2, 1).Activate Workbooks(strARISExtract).Sheets("Processes").Range("A2").Select Workbooks(strARISExtract).Sheets("Processes").Columns("A:A").Select
I need my program to: - find the cell containing the string "Datum/Tid" - record the column and the row of the found cell in two variables lCol and lRow
Here is my
Sub test()
Dim rFoundCell As Range Dim lRow As Long Dim lCol As Long
'Find method of VBA Set rFoundCell = Range("A1") Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False)
'for anyof the two lines down I get the message "object variable OR block variable not set"
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim startDate As String Dim stopDate As String Dim startRow As Integer Dim stopRow As Integer startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)") If startDate = "" Then End stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)") If stopDate = "" Then End startDate = Format(startDate, "dd/mm/yyyy") stopDate = Format(stopDate, "dd/mm/yyyy") startRow = Worksheets("sheet1").Columns("A").Find(startDate, _ LookIn:=xlValues, lookat:=xlWhole).Row stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _ LookIn:=xlValues, lookat:=xlWhole).Row Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select
I have a spreadsheet form with all kind of values and what I want to do is to find a data in a worksheet named "Config" based on the spreadsheet activecell value. With the find row adress I return data of another column but same row (in the worksheet) in two diferent label captions. If the spreadsheet activecell value it was found in the worksheet everything is ok but if the value is not found I'm getting the message "Object variable or with block variable not set"....
I had asked about automatically naming regions and this is an extension of that post because it's closed. The code we ended up with to name the region is: Dim sNm As String, sRT As String
If Intersect(Target, Rows(1)) Is Nothing Then Exit Sub '-------------------> If Target.Count > 1 Then Exit Sub '---------------------------------------->
My problem now is that I need to have the regions be the length of the longest column. I've tried using a few different ways using the worksheet range but I can't seem to get it to work.
I am trying to figure out how to create a formula or VBA to count how many contiguous 0's there are in a specific row...and then drag this formula down many rows..for example - if a row of data contains 1,3,0,0,0,4,5,0,0,0,0,0,0,0,5,3,1,0 I want the result to be 7, because 7 is the longest streak of contiguous 0's.
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
I am trying to use a variable for the name in the Sheets command so that I can put in the names of the CSV file that I have captured in a variable. Here is the macro that I recorded using Excel 2007:
I believe I should use the INSTR function, but I simply dont understand its use very well. I have a string in a cell, "MEAS/SPHERE,F(QUA_1),5", the characters I need start at the "(" and end at the closing ")", ref: (QUA_1). The start and end will always be the same, the length of characters and the spelling of characters be anything. Thus, QUA_1 can be CAL_1, CAL_2, SOMEOTHERTEXT_1 or 187bgo_199, etc, always enclosed in "( )". I wish to place the characters and the closing "( )" into a variable as a string.
This one has two sets of quotes, with the information from the noted post I can retrieve the string "S5a09b-90", now that I have a double set of brackets "( )" how do I get the value for the second set of brackets, "CAL_15"?
In the quoted string, CALIB/SENS,S, is constant. The first set of brackets are constant (string length is not), the second set of brackets are constant (string length is not).
if it is possible, to use a string variable in a Formula via VBA.
For example: ActiveCell.Formula = "=SUM(Sheet3!strVariable)"
Of course the code is not working that way. The Variable has the following structer: CellAdress;CellAdress;CellAdress... so it would perfectly fit into the formula-syntax.
Is there any possible way to do that? The reason behind it is, that I have to deal with a flexible range and I want to process this range with a formula. Actually the range is flexible, because of different filters and I only wanna count all numbers in the unhidden rows.
My varibale would then contain all celladresses, for the visible cells.
So in a nutshell: is it possible to somehow use a varible in a formula?
