Find The Longest String Including A Variable

May 22, 2007

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.

The formula i have tried (but doesn't work) is:

=INDEX(A6:A65536,MATCH(B2&MAX(LEN(A6:A65536)),A6:A65536,1),0)

(B2 is the record the user wants to amend)

ADVERTISEMENT

Displaying Longest String In Column

Mar 13, 2014

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.

View 2 Replies View Related

Longest Series Of Consecutive 2 Chars In String?

Aug 23, 2014

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.

View 9 Replies View Related

Find The Longest Run Of Zeros In A Column

Nov 10, 2008

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.

View 7 Replies View Related

Find Variable Size Number Within Text String

Sep 20, 2007

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.

View 4 Replies View Related

Find Unknown Char Contained In A String And Store It In Variable

Jun 4, 2013

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".

VB: tool.Worksheets("TRB Database").Cells(row1, "D").Value = Chr(Asc(tool.Worksheets("TRB Database").Cells(A, "D").Value) + 1)

This is where I increase the value.

View 2 Replies View Related

Including Column A String Into Input Box

Feb 24, 2009

I have a code of;

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 "

View 9 Replies View Related

Inputbox Value Can Be Compared To A String Variable Or A Numeric Variable At The Same Time

Dec 7, 2008

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.

View 3 Replies View Related

String Variable To Call Defined Range Variable

Nov 10, 2006

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.

View 5 Replies View Related

Insert String And String Variable Into Cell

Feb 18, 2014

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.

View 2 Replies View Related

Find And Find Again - Object Variable Or With Block Variable Not Set

Jun 5, 2007

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

View 3 Replies View Related

Find Method Code: Object Variable Or With Block Variable Not Set

Sep 8, 2006

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"

lRow = rFoundCell.Row
lCol = rFoundCell.Column

End Sub

View 5 Replies View Related

Find Dates Macro: Object Variable With Block Variable Not Set

Nov 21, 2006

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

End Sub

View 9 Replies View Related

Find Method: Object Variable Or With Block Variable Not Set

Aug 28, 2006

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"....

View 7 Replies View Related

How To Get The Longest Sequence Of 3s

Apr 2, 2012

How can I get the Longest sequence of 3's. E.g.

CA
1
2
3
5
3
3
5
4

View 2 Replies View Related

Count Longest Run Of Numbers Above Mean

Jun 12, 2008

I'm attempting to summarise several hundred control charts.

One thing I'd like to do is be able to put in a formula to count the maximum number of successive entries that are all the same side of the mean.

Another related thing is to be able to count the longest run where successive values are the same.

View 4 Replies View Related

First And Last Date In The Longest Continued Range

Jul 16, 2008

I am looking for a formula that returns the date of the first and last value in the longest continued range.

In the example:

CD30CLA3101/02/200833201/03/20086
3301/04/200853401/05/20083501/06/200863601/07/200823701/08/2008

The two formula should return the two dates in blue.

View 9 Replies View Related

Extend Range To Longest Column

Aug 7, 2007

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 '---------------------------------------->

sNm = Replace(Trim(Target), " ", "_")
sRT = "=offset(" _
& Target.Address _
& ", 1, 0, counta(" _
& Cells(2, Target.Column).Resize(Rows.Count - Target.Row).Address & ") )"

ThisWorkbook.Names.Add Name:=sNm, RefersTo:=sRT

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.

View 9 Replies View Related

Count Longest Run Of Contiguous Zeros

Jun 18, 2008

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.

View 9 Replies View Related

IF.. Contains...then: Search Through The Text String In That Cell And Find A Certain Word, Find And Retrn A Value

May 23, 2006

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?)

View 4 Replies View Related

Getting A Loop Including "find" To End When It Gets Back To The First Result

Jul 2, 2009

I am wondering why the loop at the end of this macro loops endlessly.

View 3 Replies View Related

Indicate That A Variable Is String

Oct 30, 2008

I understand that a everything is already considered to be string, but how would I phrase this?

If txtBolow = (a statement which indicates the textbox is full) Then

txtBolow. SetFocus
txtBolow. SelStart = 0
txtBolow. selLenght = Len(txtBolow)

If txLowl = ("") Then
txtLowl. SetFocus
txtLowl. SelStart = 0
txtLowl. selLenght = Len(txtBolow)

What would I use to indicate that the user has completed the data in the textboxes, and the old data is highlighted to make way for the new?

View 9 Replies View Related

Using A String Variable For Sheet Name

Aug 13, 2009

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:

View 4 Replies View Related

Get Set Of Chars From A String To A Variable

Dec 24, 2009

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.

View 5 Replies View Related

How To Get Set Of Chars From A String To A Variable ..

Jan 8, 2010

CALIB/SENS,S(S5a90b-90),FA(CAL_15)

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).

View 7 Replies View Related

Parsing A String Variable

Dec 8, 2006

It's been a long time, but I use to parse a string in C.
How do you strip off the long directory path of a string to just the filename and extension?

String Example:
S:2006 jobs602f EID Bass Lake Recycle BPSConstructionSubmittal Review16940-Instrumentation602f-16940-01.rev.doc

Wanted result:
0602f-16940-01.rev.doc

View 9 Replies View Related

Use String-Variable In Formula

Sep 16, 2009

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?

View 9 Replies View Related

Set A Workbook Variable As A String

Oct 20, 2009

I have a string variable contacting a file.

I want to set a workbook variable to this file, WITHOUT actually opening it!

ie:
DIM wb as Workbook
DIM filenm as String

filenm = "C: empdata.xls"
set workbook = filenm
============================================

So I know this won't work, but is there anyway around it? (bearing in mind I don't want to open the file!

View 9 Replies View Related

Using A String Variable In Formula

Apr 21, 2006

Just a quick question, is it possible to use a string variable in a Excel formula (in VBA)? Im trying to do the following in VBA

mortgageSchedName = acName & " Schedule"
Range("B6").FormulaR1C1 = "=mortgageSchedName!RC[254])"

the above results in an error because there is no such sheet as "mortgageSchedName", but I want it to access the value of that string variable.

View 6 Replies View Related

VBA Code To Detect Longest Cell Containing Data And Copying Whole Range

Feb 9, 2014

I am trying to do the following with VBA.

What i need is to find out the last cell with data and then selecting the whole range and copying it.

Please see the below.

As you can see, the column with the "longest" data range is B9,C9,D9,E9.

I need a VBA code to detect which Column has the longest Data and from there copy the entire range.

Hence, in this case, the range to be copied is From A2:J9.

Column A
Column B
Column C
Column D
Column E
Column F
Column G
Column H
Column I
Column J

1
2
ttt
rrr
m
vvv
gg
ff
fff
fff
fff

[Code] ..........

Hence,in this case, the range to be copied is From A2 to J14.

Column A
Column B
Column C
Column D
Column E
Column F
Column G
Column H
Column I
Column J

1
2
hjhjh
ghj
gh
ghj
ghj
ghj

[Code] ..........

View 5 Replies View Related

How To Extract Variable Length String

Feb 9, 2014

i just wanna extract the date shown(last data until meet the first comma) in first column to another column.

column 1
abcde, 1234
abcd, 12345
abcdef, 123

column 2
1234
12345
123

View 3 Replies View Related

Store Cells Value As String Variable?

Aug 2, 2014

I have several words in sheet2.one word per line. every line in sheet1 should be checked and deleted if the line (colum 3 and 4) contains any of the words in sheet2. i decided to go with two for-loops, my Problem is a error in line 7 and 9 (indicated by arrows). it seems to me that ...Cells(...).Value is not allowed for strings. i already tried .Text, checked several VBA Forums but could not find a solution.

View 13 Replies View Related

Variable StrArray - How To Create A String

Oct 24, 2011

I have the following code.

Code:
ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array("ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"), Operator:=xlFilterValues

The values ELDU238 - ELDU247 are values in a column beginning at A2. I want to create

"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"

in a string variable strArray which will enumerate whatever values are in column A beginning at A2 and create the above. I can then have

Code:
ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array(strArray), Operator:=xlFilterValues

Hence if the number of entries changes e.g. say I have

"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247", "ELDU248"

the code will still work.

View 4 Replies View Related

Changing Font Of A String Variable?

Nov 28, 2012

I'm taking some university classes and one of them is called Applications Programming and we have been using excel VBA. I have a project that I'm working on, and it's very basic. Basically we need to create a userform that has 3 option buttons, 4 check boxes, a couple labels and a couple text boxes. It's suppose to be a ice cream selection terminal. Option boxes for basic flavors of ice cream, check boxes for toppings, one text box for user input of number of scoops of ice cream and the last text box to output a reciept/overview of purchase.

So here's the question... I was wondering if there's a way to change the font of a single string variable, or change the font of a word within a string. My output to the saleBox(My receipt overview) is this:

saleBox.Text = ("Thanks you for your Purchase") & vbCrLf & vbCrLf & _
"You ordered " & numOfScoop & " scoops of " & flavorChoice & "ice cream" & vbCrLf & _ "Topped with: " & toppings & vbCrLf & vbCrLf & total

(not sure how the code is gonna look in the message but I'm sure you get the idea)

i want to be able to change the font for the flavorChoice or toppings variables. They are both strings.

Anyways, I know I can get away with basically what I have there and get full marks, but I like going above what the basics are. If it's kinda complicated.

I'm gonna attach a screenshot, more info is better than not enough right... nevermind its askin for a url!

*** I just now got the idea of putting another textbox over the area that would output the text I want to change and hide the border or something and then change the font of that text box through the properties ***

View 3 Replies View Related

Creating String Using Variable File Name?

Dec 18, 2012

I'm trying to check to see if a file exists using VBA but the routines I see use a string that has the path and then the file name. I have a fixed path C:VBtesting and the a variable file name. I'm using a file name that consists of a username & date such as cwilliams201212. I'm having problems getting the variable file name into the string.

View 1 Replies View Related

VBA - Concatenating Formula With String Variable?

Jan 16, 2013

I'm getting the generic 1004 error on the Range.FormulaR1C1 line, but I can't seem to see the problem.

Code:
Sub UpdateFormulas()
Dim stockFund As String
For i = 2 To finalRow

[Code]....

The mouse-over on the stockFund variable in that last line shows the correct cell address as the value and I checked the If statement to ensure it actually finds the number. I would guess that it would be a syntax error with that line, but it looks correct to me.

View 8 Replies View Related

VBA Partial String Extraction From Variable?

Feb 12, 2013

I have a comment that I'm putting into a variable for the purpose of obtaining the persons name.

Cell C3 (in this example) just contains their employee number, but it's comment contains...

Tech ID: 123456
Name: John Doe

The Tech ID is variable from 3 chars to 6 chars and obviously the name is size is also variable. The end result will be a variable with just the persons first and last name extracted from the comment.

Code:
Dim mycmt As String
Dim techname As String
mycmt = Sheets("tech").Cells(3, "C").Comment.Text
techname = Right(mycmt, BIG-OLE-BALL-OF-CONFUSION)
MsgBox techname

My 2nd question. This is the 3rd time in recent days that I've needed a partial string and Mid, Left, Right, Len, Find just boggles my mind.

View 6 Replies View Related

Using Variable String In Shell Command

Nov 1, 2013

I'm trying to use a variable that hold a string in Shell string command. This shell is the command to take a snap picture over a given region and store the picture to a pre determined file name

***The code do compile***

The string variable is : StringAddress = "g: estsnapasnap1.jpg"

The following shell command will work

RetVal = Shell(("C:Program Files (x86)HyperSnap 7HprSnap7.exe " & " -hidden
-snap:x3602:y155:w1432:h830 -save:jpg g: estsnapasnap1.jpg"))

This one, with the string address variable, wont work

RetVal = Shell("C:Program Files (x86)HyperSnap 7HprSnap7.exe " & " -hidden
-snap:x3602:y155:w1432:h830 -save:jpg " & StringAddress)

View 1 Replies View Related

Setting A Range Using String Variable

Jan 8, 2014

I'm having trouble setting the Range "UtilizationRange" using a variable, "CurrentRange" to store that range. I get the error "Run-Time error 1004: Method 'Range' of object'_Global' failed".The rest of my code works when I set the utilization range using the actual range for my purposes, but the problem is the range will change each month and I don't want to change it manually each time. There's a cell in the excel sheet this refers to (AG3) that holds the range value, which in this case is [N7:N75], and I would just like to return that value to the variable "CurrentRange" and set UtilizationRange equal to that.

The value is returned to CurrentRange, but it's the last line that's giving me the problem.

Sub RangeTest()Dim UtilizationRange As Range, Cell As RangeDim CurrentRange As String
CurrentRange = Range("AG3").Value MsgBox (CurrentRange)
'This returns the value [N7:N75], which is what I want
Set UtilizationRange = Range("CurrentRange")End Sub

View 1 Replies View Related

Split String Variable And Use Some In Filename

Mar 16, 2009

I have a filname called ex: "Statusreport xxyyzz.xls"

I'm only interested to put xxyyzz in a variable and reuse it for saving another workbook.

How can I use the different string functions for this. The word "Statusreport" is used for apporx 20 different workbooks.

View 9 Replies View Related

Removing Characters From A String Variable

Jun 12, 2006

Let us assume x and y are strings. Now let us make x equal to a small sentence (ie. full of words and spaces). I now need to somehow make y equal to x, but with the last word removed. I need to do this in VBA.

View 3 Replies View Related

ADVERTISEMENT