Using String Manipulation To Create And Change Wroksheet Name.

Dec 5, 2008

i have a worksheet called 'week 1' and want to write vba code so that a new sheet called 'week 2' can be created. However i want to write code so that it doesn't matter what sheet i am on, a new sheet would be created with the name week and the next number up. ie. the last worksheet is 'week 7' then the code should be able to create a new worksheet called 'week 8' ect. I currently have this code but it keeps returning an error message. anyone know how i could do this???

Sub nSheet()
Dim nm, i As Long
nm = Worksheets(Worksheets.Count).name
i = 1
Sheets.Add
Do While WorksheetExists(Left(nm, Len(nm) - 1) & i)
i = i + 1
Loop
ActiveSheet.name = Left(nm, Len(nm) - 1) & i
End Sub

View 4 Replies


ADVERTISEMENT

VBA String Manipulation

Sep 1, 2009

I need to be able to take someones name which is in a single cell in the format "First Middle Last" (could be more than one middle name) and then split it up so that it fits the following format:

Last name (must be 50 characters)
Middle name (must be 15 characters)
First Name (must be 15 characters)

I'm unsure on the right combination of operators to use to variably concatenate strings in this way. I'm confident I can figure out how to add the white spaces after a name to make it fit the character requirement but I don't know how to split the initial name up into it's 3 parts. Names longer than the character requirement will just be truncated.

View 9 Replies View Related

String Manipulation With Exceptions

Jul 23, 2009

I have a macro that loops through a column of text and removes all text after a hyphen. Example: AU9929-PK becomes AU9929. I need to set up some exceptions that when it processes certain text it does not truncate it. Example: AU9929-ASST stays as AU9929-ASST. So far there are twelve exceptions but this could increase.

Note: The list which includes shop number, description, sku, sku description, quantity, unit price and more is sorted in a particular order as other operations are performed on it that requires it be in a specific order.

View 4 Replies View Related

Fancy String Manipulation

Aug 1, 2008

I am trying to let the user call my subroutine in the following 5 ways but am lost using string manipulation -

Call FindReplaceAll(“C”, “123”, “1.23”) ‘ In Col C replace 123 with 1.23
Call FindReplaceAll(“2”, “123”, “1.23”) ‘ In Row 2 replace 123 with 1.23

Call FindReplaceAll(“C:E”, “123”, “1.23”) ‘In Col C-E replace 123 with 1.23
Call FindReplaceAll(“2:4”, “123”, “1.23”) ‘In Rows 2-4 replace 123 with 1.23

Call FindReplaceAll(“C9:E19”, “123”, “1.23”) ‘In region C9-E9 replace 123 with 1.23

Here is what I am trying to do in psudo
Sub FindReplaceAll(sType As String, sFind As String, sReplace As String)
If sType does not have a colon in it then
If sType = “A-IV” or sType = “a-iv” only then
Columns(sType &”:”& sType).Select
If sType = “1-65000” then
Rows(sType &”:”& sType).Select
Else

View 9 Replies View Related

Numeric String Manipulation Query

Dec 23, 2009

I have a sizable spreadsheet, which has one column where the cells are mostly numeric strings of up to 6 numbers - there are variations, which I will mention.

What I need to do, is to treat the numbers as individual figures, and add them, then divide by the number of figures. e.g. cell value '123456' I need to process as (1+2+3+4+5+6)/6.

The variations are that '0' has to be added as 10, and any letters need to be added as 12. '-' and '/' which are the only non-alphanumeric values should be ignored.

View 9 Replies View Related

Create Email, Font Manipulation.

Sep 17, 2009

Im Creating a email based on useform control values. Here is a part extract of how im creating it.

View 4 Replies View Related

Change Current Cell Value If Number String NOT Letter String?

Apr 7, 2014

In sheet1 I have a simple database consisting of 5 columns of data

Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500

what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.

View 4 Replies View Related

Create String With Wildcards

Jun 6, 2008

I have two questions. Below is a code snippet that demonstrates what I'm trying to achieve.

Const D_WIL_SearchString As String = "[HTML ON]*<a title=""*"" target=""blank""href=""*"">*</a>*[HTML OFF]"

If D_Info(i).WIL Like D_WIL_SearchString ThenDo AElseDo BEnd If

The double quotes ("") are supposed to represent a single double quote within C_WIL_SearchString.
The asterisks are supposed to represent any characters of any length.
All other characters (except for the initial double quotes (")) should be present within the string D_Info(i).WIL

I'm having issues ensuring that this code works correctly though, as it does not appear to search for the string that I'd like it to.

As a second question, I would like to know if there is a way of ensuring the asterisks (or another similar character) only allow whitespace? (i.e. spaces and new lines)

View 6 Replies View Related

Text Manipulation

Dec 30, 2009

Column A

Column B
Column C

Economy
BRITZEconomy
Question

Compact
BRITZCompact
What formula do I need to use in column A to get the desired results as per Column C?

Medium
BRITZMedium

Large
BRITZLarge

Bus
BRITZBus

Bikes
BRITZBikes

4X4
BRITZ4X4

View 9 Replies View Related

Clipboard Manipulation

Oct 6, 2007

I am looping through and copying screens of mainframe data and appending them to the clipboard. Once done, I start a Notepad and paste it for future massaging. Now I need to perform a check on each screen which requires copying something from the screen, assigning to a variable, and checking it. This overwrites what I've already stored on the clipboard. Is there a way to store what I've got on the clipboard, then use the clipboard for something else, and then reload the clipboard?

View 3 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

Data Manipulation And Weighting

May 25, 2009

The data is arranged as such, there are a list of call types and in another column, there are a list of call lengths. Currently I have a COUNTIF which searches the call type column for certain call types ie BA is off peak but this only tells me how many off peak calls they have made, I need to make it tell me how many minutes of off peak calls there are.
IE this is an example
Call Types Call Lengths (minutes)
BA 1
BA 1
BA 3
BA 2
at the moment my formula would only count this as 4 but I need it to calculate it as 7. This is a very big data set containing around 900 entries per sheet.

View 11 Replies View Related

Date Display Manipulation

Nov 14, 2012

Below I have a table with dates. The actual dates will be added in over time. I want to display the amount of days in the column btwn planned and actual but since it is subtracting a date from a blank cell, the #VALUE function is displayed. What function can I use to tell excel to keep the cell blank until a value other than #VALUE appears.

PLANNED Spud
ACTUAL Spud
Days

5/30/12 12:00 AM
5/30/12 2:30 AM
(0)

12/15/12 12:00 AM
#VALUE!

1/19/13 12:00 AM
#VALUE!

2/23/13 12:00 AM
#VALUE!

View 1 Replies View Related

Info Manipulation Formula

May 13, 2007

With my limited knowledge I am having trouble constructing formula to break the following info that I receive frequently from my credit card company into 3 columns - Date, Details, Value

21 Apr 2007 SAINSBURYS PETROL BURPHAM £10.00

View 9 Replies View Related

Manipulation Cell Colours

Jun 22, 2009

Is it possible to create a formula that looks at a cell (say A20) and if TRUE changes the colour of A22 to green if false to red?

View 9 Replies View Related

Formula For Text Manipulation

Oct 25, 2006

I have a file with a physician name and title in the cell as follows:

"James A. Jones, M.D."

I need a formula to reverse the last name, drop the title "M.D.", get rid of any additional periods after the middle initial and put it in upper case:

"JONES, JAMES A"

There are some names without middle initials also so this may present an additional problem.

View 7 Replies View Related

Data Manipulation From Txt File

Jan 16, 2007

I have test.txt file. The data in test.txt file so messy (there are headers and some data lines start with letter "A" and need to ignore). There's no <TAB> in data file, all are single space and <enter>. I can do nothing about data files.

Task:
Open test.txt file
SUM all product quantity, put result in xls file in A1
SUM all Total price in the txt file, put in xls file (same file) in B1
Close test.txt file

View 4 Replies View Related

Create Find Box Into Which Search String Inputs

Nov 9, 2011

I need the code to create a find box into which a search string is input. The code should find the string at a particular cell, then copies this cell and four cells to the right to a location in sheet 2 of the same workbook

The code runs but comes back with a runtime 424 error. I believe the issue is being caused by "nextCell" but I cant see why.

Sub FindStrings()
Dim firstCell, nextCell, stringToFind As String
' Show an input box and return the entry to a variable.
stringToFind = _
Application.InputBox("Enter J Number and Stage Number. For Example J1234 ST1", "Search String")
' Set an object variable to evaluate the Find command.

[Code] ........

View 3 Replies View Related

Excel 2007 :: Create String From Array?

Jul 23, 2012

I have this formula, ( which i found the basis of on a You tube video and Richard Scholar was accredited with improving the soloution)

=SUMPRODUCT(--ISNUMBER(F4:AH4)*10^{-29,-28,-27,-26,-25,-24,-23,-22,-21,-20,-19,-18,-17,-16,-15,-14,-13,-12,-11,-10,-9,-8,-7,-6,-5,-4,-3,-2,-1})

This forumla generates a number for each player, the higher the number the more inline they are to get a game

Problems are this works for the 29 weeks of this year but more weeks need added to the end of the year as we get there. Can i generate the array numbers from a formula and shorten.

This is a sample data ignore row 2( just a count of players) and data actually goes back to 6th Jan

Excel 2007BCDEFGHIJ1Wk21Wk22Wk23Wk24Wk25Wk26Wk27Wk28210101010101010103Player 1111104Player 210001105Player 300101016Player 4010101107Player 50110118Player 600009Player 7111000110Player 8011100011Player 9000012Player 101011113Player 11101114Player 121010115Player 13016Player 141010017Player 15118Player 160101Sheet1

View 7 Replies View Related

Create String Function With Range Of Numbers

Sep 11, 2012

Is it possible to create a string with a range of numbers

1
3
4
5
6
8
10

Result: 1,3:6,8,10

Or

1
2
3
4

Result: 1:4

Or

1
3
5
7

Result: 1,3,5,7

Where consecutive numbers are separated by a ":" and other numbers by a ",". I use the following function to create the ","-concatenate, but I don't know how to create the ":"-ranges.

Function AConcat(a As Variant, Optional Sep As String = "") As String
'By Harlan Grove, March 2002
Dim Y As Variant
If TypeOf a Is Range Then
For Each Y In a.Cells

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

View 9 Replies View Related

Data Manipulation & Grouped By The Name Field

Aug 4, 2009

Example:............

Where all question 1 fields are put into a row, 2 in another row, etc (this may be more than 2 sets of questions), grouped by the name field. I have had a look at Pivot tables but they dont seem to do what Im after.

View 3 Replies View Related

Pivot Table Manual Manipulation

Apr 27, 2007

My pivot table is not auto summing each column properly, and I can't manually enter a formula to do so. I tried adjusting the settings of the rows and columns through the pivot table wizard, but no luck. Is there a way around this limitation?

View 6 Replies View Related

How To Change Font Of String

Oct 18, 2011

i have a string in a cell, that would say "Your results are -10% for the week". is there any way to change the font to red, for the number, including the minus sign preceeding it, and the percent sign following it. all are in one column, about a thousand rows. the rest can stay black.

View 5 Replies View Related

Change Letters Within String

Apr 10, 2014

I am looking for a while for a macro or a function, which should change needed letters in string into needed ones.

Example:
A1 cell contains words "John Johnson Martinson". I want to switch "n" letter into my specified, lets say "m" and the result should be "Johm Johmson Martimsom".

I would like to get it within range of columnA. Is it possible to get?

View 3 Replies View Related

Chart Title Manipulation With Macro Code

Sep 2, 2006

I have a program that updates values quarterly when run. The spreadsheet that it works with also contains a couple of pie charts that correspond to the updated data. the charts only have one series with category values. I recorded a macro to see the code excel uses for creating a new chart and tried to modify the statements to my needs but have been running into runtime error 1004 ("Method ' Cells' of Object '_Global' failed"). The code is as follows I don't have any trouble until the last two statements.

Sub Chart_Updater()
Sheets(Chart1var).Select
With ActiveChart
.HasTitle = True
If Chart1var = "COLI VUL 1 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 1 Allocation by Fund Provider " _
& sday & "-" & sday & "-" & Lyear
ElseIf Chart1var = "COLI VUL 2 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 2 Allocation by Fund Provider " _
& sday & "-" & sday & "-" & Lyear
ElseIf Chart1var = "COLI VUL 7 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 7 Allocation by Fund Provider " _

The variables fundtr1, fundtr2, and emptycolvar are public, and are all returning values, so there is no problem there. Also I can't use ranges like "A1:F7" because the range needs to be variable.

View 5 Replies View Related

Extract First Character Of Each Word In String To Create Code Letters

Nov 3, 2012

Junior Fit Softstyle T-Shirt
Antique Cherry Red

Junior Fit Softstyle T-Shirt
Antique Cherry Red

Softstyle T-Shirt
Antique Heliconia

Softstyle T-Shirt
Antique Heliconia

Softstyle T-Shirt
Antique Heliconia

This is column a and b. Looking at b I am looking for a formula that will pull the first capital letter out of each word like ACR to create color codes.

View 1 Replies View Related

Change String Uppercase After Mark (Dot)

Apr 18, 2014

how to make upper case after mark "." (dot), see this below :

sample data
after formula
Tabrani,Ny.yusmaniar
Tabrani,Ny.Yusmaniar
Badu Zai,Tn.surjaah R & Darussamin
Badu Zai,Tn.Surjaah R & Darussamin
Zainal Arifin,Ny.elvyati
Zainal Arifin,Ny.Elvyati
etc..

it's possible with excel code? if could perhaps not macro.

View 5 Replies View Related

Image Manipulation In Photoshop To Lose The Cell Data

Oct 12, 2009

I've created a humongous spreadsheet in Excel- 26 million cells and a file size of about 500mb. The result of these calculations is a pattern derived by conditional formatting. I'd like to get the pattern into Photoshop- It would actually simplify image manipulation in Photoshop to lose the cell data, and the underlying formulas are of no use.

The file seems to be too big to save into a PDF file. Is there another way to get the Image into Photoshop?. The other alternative is to reduce the file size by stripping out unnecessary cell data and formulas without losing the pattern.

View 2 Replies View Related

Table Manipulation Based On Header And Details Tables?

May 19, 2014

I have a table which is extracted from a database using 2 tables.

1 Header and 1 details table.

I want to display the latest date in column E.

As Column A,B,C is the header table and column D,E is the details table.

Can this be done in vba or a pivot?

View 1 Replies View Related

String Search - To Create A Combobox To Filter A Set Of Data By The Month That Is Entered

Jun 7, 2009

I am trying to create a combobox to filter a set of data by the month that is entered.

The below code worked fine when there was just the month entered, but now all the entries in the sheet are in the format 01 January 2009. So I need a section of code which will search for the combobox value as part of a string in my range.

The cbodate values are Jan, Feb, March etc ....

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved