Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




[VBScript] Excel Macro: Split String In Cells?


I have a list of names [First Last] in a column of Excel cells (A1 through A340 or so). The person who did the data entry was silly enough to put both first and last name in the same cell... it fell on me to write a script that would split them. Too bad I know no VBA, only VB.NET...

Example: Cell A1 has "John Smith". The macro needs to split it into A1 "John" and A2 "Smith", or A2 and A3, or whatever.

From VB experience, I know I should use a For loop to step through the string until I find a space, then chop and move... but can someone help me with appropriate commands for a VBScript macro?

Thanks,
Seth




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
[Solved] VBScript: Split String...
Hi guys,,

I need to split incoming data into seperate items of an array... now in Visual Basic this is easy...

in vbscript: i dunno why but its crapped up.

I need to use it like the following:


VB Code:
Sub wskScript_DataArrival(Index, bytesTotal)'On Error GoTo ErrHand:'On Error Resume Next     Dim SplitDataA(), DataArrival(), DataRecieved    Dim i        wskScript(0).GetData DataRecieved, vbString        SplitDataA() = Split(DataRecieved, " ## ")        For i = 1 To UBound(SplitDataA())        DataArrival() = Split(SplitDataA(i), " :$: ")                Select Case DataArrival(0)


only problem is:

when i get to the following code:


VB Code:
SplitDataA() = Split(DataRecieved, " ## ")


i get the following error come up:


Quote:




Error> 9 - Subscript out of range: 'SplitDataA' on line 145






has anybody got a clue of why this error is coming up?...

i know for a fact data is coming, cuz i added a message box like:

Code:
wskScript(0).GetData DataRecieved, vbString
msgbox DataRecieved


and it come up with:

Quote:




---------------------------
VBScript
---------------------------
## cmdDL :$: LT ## cmdSN :$: 127.0.0.1
---------------------------
OK
---------------------------






anyone got a clue on dis one?.. its prolly jst the fact of the vb / vbscript differences.... but :shrugs:

Split Cells In Excel
Hello All

I am making a report in excel and am manipulating the first few rows for titles and details of the report.

I was wondering how would i be able to split 1 cell into 2 or 3

Reason I have a row of 7 columns and i want to create 3 equal coloumns so i merge the row into one cell


VB Code:
wSheet.Range("A4", "G4").Merge


then i want to split this new cell into 3 but i do not know the syntax.

Thanks.

Split One Cell Into Two Cells In Excel
Hi

How to split one single cell into two cells in excel 2003

Thanks

Accessing Excel Cells From ASP/VBScript
I posted this in the ASP Development forum to no avail - I'm thinking this may be more of a VBA thing...

I'm trying to grab cell values from an excel file using ASP with VBScript. This is the code I'm using:


Code:


<%
Set ExcelConn = Server.CreateObject("ADODB.Connection")
Set ExcelRS = Server.CreateObject("ADODB.Recordset")
ExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
ExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
ExcelConn.Open "c:inetpubwwwrootaspcalendar est.xls"
set ExcelRS = ExcelConn.Execute ("SELECT * FROM [Sheet1$]")

Response.Write(ExcelRS(1,1))

%>



I've tried Response.write(ExcelRS($A$1)) and every other variation I can think of. What is the syntax for refering to a cell location from VBScript/VBA/etc... ?

**RESOLVED** MERGE && SPLIT Cells In Excel Using VB
Hi guys,

i have problem in merging and spliting cells in excel sheet.
i have a template and i am using 8 columns.

either of the below solution is ok for me

Option 1
i want to merge 4 columns each (making 8 columns to 2)

Option 2
right now i am able to merge whole row altogether.
is it possible to spli the same row into 2 columns ?

pls suggest

thanx in advance

Input String To Cells Via Macro
new to excel and vb mixing just playing with macro how do i input a string into a cell i've tried

excel.cells(a,1) = mystring

but failed

anyideas?

Vbscript And Asp To Run Excel Macro
Hi,

I'm trying to get an asp page to run a macro on an excel 2000 file. Here is my code:


Code:
dim objExcel, objWorkbook

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:Macro05.xls")

objExcel.run("Macro1()")

objWorkbook.Close
objExcel.Quit
set objWorkbook = nothing
set objExcel = nothing
Here is the error message I'm getting :
Microsoft Excel (0x800A03EC)
The macro 'Macro1()' cannot be found.
/directory/macrotest06.asp, line 9

Running A Excel Macro From VBScript
Can anyone help with this ?

I am trying to Run a Macro in my Excel Sheet using VBScript So Far I have tried the Following

Code:
'This is a VBScript.
Dim strPath
Dim oXLA

strPath = "MyExcel.xls!CreateData"

set oXLA = CreateObject("Excel.Application")

oXLA.Run strPath

Set oXLA = Nothing
This is no good. I tried some Solution I found after doing a Google Search those did not Help Also.
Please point me in correct direction.

Thanks
Datta.

Vb Macro Needed To Split Excel Sheet
--------------------------------------------------------------------------------

Hi All,

I Need A Vb Macro Script To Make Multiple Copies Of Microsoft Excel Sheet After 65 Rows Are Exceeded. Since Am Using Excel It Limits Onl;y 65k Rows.

Can U Please Help Me With Code.


Vb Macro To Break Excel Sheet After It Exceeds 65k Rows
Million Thanks In Advance



Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

Help With Excel Macro - Transpose Cells
Hello:

I am pretty new to VB with Excel, and I'm hoping someone can help with write some code for something which is probably pretty easy for some of you.

I have a series of Excel files with cell values in a certain layout, and I really just want to create a macro that will "transpose" the values.

Its hard to descibe the layout, but the "headers" are in row 3, and the values follow in rows below (each file has different # of rows). What I need to do is extract a certain block of question headers, starting with the one column header that includes the text "Please", through the last one. To the right of each question in the block are names, with scores to that question in rows below. See below for the sample layout:

A B C D E
3 Q1 Q2 Q3: Please John Jane
4 blah blah 5 4
5 blah blah 4 3
6 blah blah 4 4

What I would like to do is move them to a new sheet with a layout like:

A B C
1 Please John 5
2 Please John 4
3 Please John 4
4 Please Jane 4
5 Please Jane 3
6 Please Jane 4 ......etc.

I have so much data that it takes forever to manually do this, so if anyone can offer some coding help, it would be so very much appreciated by this newbie.

Thanks,
EBox

Excel VB Macro For Moving Cells
Hello:

I am pretty new to VB with Excel, and I'm hoping someone can help with write some code for something which is probably pretty easy for some of you.

I have a series of Excel files with cell values in a certain layout, and I really just want to create a macro that will "transpose" the values.

Its hard to descibe the layout, but the "headers" are in row 3, and the values follow in rows below (each file has different # of rows).  What I need to do is extract a certain block of question headers, starting with the one column header that includes the text "Please", through the last one.  To the right of each question in the block are names, with scores to that question in rows below.  See below for the sample layout:

    A            B          C              D           E
3  Q1           Q2       Q3: Please John        Jane
4  blah        blah                        5           4
5  blah        blah                        4           3
6  blah        blah                        4           4

What I would like to do is move them to a new sheet with a layout like:

    A            B            C   
1  Please        John         5
2  Please        John         4
3  Please        John         4
4  Please        Jane         4
5  Please        Jane         3
6  Please        Jane         4  ......etc.

I have so much data that it takes forever to manually do this, so if anyone can offer some coding help, it would be so very much appreciated by this newbie.

Thanks,
EBox


Excel Macro: Getting/comparing Cells In Different Sheets
Hi,

I'm writing a macro that reads, compares and print values/strings in different sheets. I'm doing the following


Code:
If Sheets("Input").Cells(i, 16) Or Sheets("Input").Cells(i, 17) > 0 Then
mysht.Cells(row, 1) = Sheets("Input").Cells(i, 3)
EndIf


but it gives me an overflow error message. It breaks at the IF condition, when I moved to cursor to Sheets("Input"), it says "Empty"

Please help!

Excel Macro - How To Select Used Cells In A Column?
Hello!
solutin is in the last post

I'm trying to workaround this problem: http://support.microsoft.com/defaul...kb;en-us;815277

this changes data in a selection to make it REALLY of text type, provided the cells were already text type.

If anyone knows another way to achieve the same result, that is welcome too. (convert mixed numeric and text data in the same column to only text data).

This is NOT achieved by going to Format -> Cells -> text

Although this is a required previous step for the method in the link above. The numbers are only stored as text once a space is entered before the number in the cell. We can delete the space later and the number will remain stored as text.

So,
I need a macro to do the following: select used cells in a specific column.
I need to select column "values" but not the entire column, only the used rows (it is possible that there are empty cells in this column, that should not matter, I need to select the cells if there is a corresponding non-empty cell in column "dt")

for example:

Code:


column a | column b | dt | column x | values | column z
....a....|....b.....|.1..|....x.....|...123..|....z....
....a....|....b.....|.2..|....x.....|........|....z....
....a....|....b.....|.3..|....x.....|...456..|....z....
....a....|....b.....|.4..|....x.....|........|....z....


here i would need to select 5 cells from column values, counting title cell






I already know that changing data type is achieved with:

Code:
Selection.NumberFormat = "@"


all I need now is select the cells I want to change...

Hope anyone can help me
Cheers

Excel Macro Problem - Search And Replace Cells
Hi

Column 8 in my worksheet has names.

I want the name to change to Bob when the cell in column 4 has OU-BW in it AND the cell in column 8 has content ie, a name in it

As coded below

Sub MacroReplaceName()
'
' MacroChangeToOU Macro
' Macro recorded 13/04/2006 by Registered User
'
Dim Count

Count = 1

ThisWorkbook.Worksheets("reporting").Select
Do While Count < Range("H65536").End(xlUp).Row ' finds last row
If Cells(Count, 4).Value = "OU-BW" And Cells(Count, 8).Value <> "" Then
Cells(Count, 8).Value = "Bob"
Else
Count = Count + 1
End If

Loop
End Sub

A cell near the top of the sheet I can see is replaced properly but the Excel program crashes.

Any suggestions??

Easy Excel Macro Question: Shifting Cells
I can't remember the method to move to the next cell in a row or a column.

Excel Macro To Hide Rows Containing Cells Without Data...
I need some help with an excel macro. This is what it needs to do: For the column that the cursor is on, the macro needs to check from the 5th row to the 20th row and find out which cells are blank, zero or have no data. For all cells that meet the criteria the entire row on which that cell is found needs to be hidden.

This is how I select the range of cells whose rows need to be hidden:
   Range("C8,C9,C11,C12,C14,C15,C16,C18").Select

This is for the final cell of the range (I think)
    Range("C18").Activate

This is how I hide the rows that contain the cells that need to be hidden
    Selection.EntireRow.Hidden = True

All I need to know is how do I find out (for a certain column) which cells are blank, zero or have no data so that I can add those cells to a collection or array.
Any help in this regard will be appreciated...


"The answer is out there, Neo, it’s looking for you. And it will find you, if you want it to."--Trinity to Neo

Use Excel Macro To Delete Rows With Specific Data In Cells
I need some help writing a macro that deletes a row (or number of rows) in a worksheet. We have an appointment scheduling app that exports the data in .CSV format. I run a macro that formats this .CSV file so we can insert it into another .XLS sheet that gets emailed to our retail locations to post their daily appointments. What I would like to automate is removing multiple rows that have N A in the firstname & lastname columns. Is there someway to "delete any row that has N in column c and A in column d"? We use the N A as fake names to fill in time slots we don't want to make "real" appointments. Now I have those times blocked out and have to manually delete each one before copying into the formatted worksheet that's mailed to the individual stores. Thanks for any and all help! TommyT.

Vbscript To Extract String From An Excel File
BOnjour,

   Is it possible to extract in a text file the content of
one cell  ( cells E7 for example) of an excel file with vb

Many thanks

Elisabeth

Use String To Output Into Excel Cells
I would like to be able to create a new Excel spreadsheet or open an existing if it does exist. After that I would like to be able to use the value of a string and place it into a selected cell in the spreadsheet.

Best of all would be to all that in some kind of silent mode and after that to be able to make the spreadsheet saved by itself and close by itself.

Use String To Output Into Excel Cells
I would like to be able to create a new Excel spreadsheet or open an existing if it does exist. After that I would like to be able to use the value of a string and place it into a selected cell in the spreadsheet.

Best of all would be to all that in some kind of silent mode and after that to be able to make the spreadsheet saved by itself and close by itself.

SQL Insert Into String From Excel Cells
what I am tiring to do is insert specific cells from an excel sheet into a insert into SQL string. here is what I have been tiring.


Code:
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("R:Data .xls")
Set oSheet = oBook.Worksheets

If FileExists(App.Path & "Time-Single.mdb") = True Then
Kill (App.Path & "Time-Single.mdb")
End If
If FileExists("R:Data.xls") = True Then

Dim dbs As Database
Dim stringSQL As String

stringSQL = "CREATE TABLE Table1(NameID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, WeekEnding TEXT, TimeAverage NUMBER);"

Set dbs = CreateDatabase(App.Path & "Time-Single.mdb", dbLangGeneral)
dbs.Execute stringSQL
dbs.Close

Dim db As Database
Set db = DAO.OpenDatabase(App.Path & "Time-Single.mdb")

Dim strTemp As String

If Not oSheet(2).Cells(20, 13).Value = "" Then


strTemp = "INSERT INTO TABLE1 (oSheet(2).Cells(20, 13).Value,oSheet(2).Cells(20, 14).Value);"
db.Execute strTemp
End If

End If


db.Close
oExcel.Quit


Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing


Thanks,
Jason

Vba Excel- Stripping String Into Cells
hey all

is it possible to strip each item from the below string and then place each item into a separate cell?

ie. I have a cell content such as:

U 25 Stewart, Patrick, A 100.64 0.00 123.35 0.00 0.00 0.00 25000.00 3555.01- 290.33

and would like to place each item into ajoining cells in a row (the name can be taken as one long string)

thanks

Creating A String In VBA From Cells In Excel Worksheet
Hi Guys,

I have a range of cells in Excel that I want to string together in VBA so that i can that string in part of a SQL Query. I really have no idea where to start. I am guessing a LOOP statement, but have no had any experience with these previously.

I have attached the file that i can use as an example.

i would like the output to be something like;
'this','is','a','testing','range','of','cells'

Thanks for any help in advance.

Daniel

Trying To Split First Initial, Middle Initial, And Last Name/Suffix With Excel Macro
Hi,

I am a Data analyst for a average-sized pharmaceutical company. I have been assigned the task of creating a macro the separates names. I could use Text to columns on the column of names that need to be separated, but I only need the space before the last name to be separated. So, in Column A would be the Full name(ex: J H williams III). In column B, the first an second initials would be located(ex: J H). The last name and suffix would be in the Column C. When I record a macro and use Text to Columns, I find that I can't specify for the macro to skip to the last name(basically the next word with 2 or more letters) and split there. Does anyone have a macro that can do this. Below is an example of how the data looks and how I need it to work.


K R Russell IV

The data needs to be split into columns on the same row

K R in Column B

&

Russell IV in column C


Any help would be appreciated.

Dameon

Pass A String To A Excel Macro??
Is it possible to pass a sting to an Excel Macro?


I can open the macro but I can't pass a sting to it.

Split Function - Determine The Point In Which The String Was Split.
Hi Guys,

Iím trying to split a string using the split function. I can split it fine using the split function, but I want to know the exact point(s) that the string has been split. Anyone got a good way of doing this?

I really need to know the exact points of the split. I know that I could do this by making my own Split function that consists of a few Do Whiles - but that would be very CPU intensive, and I dont want to go there.

(p.s. The happy mothersí day logo scared the sh*t out of me. I only just realized that the US mothersí day is different to the UK mothersí day!).

Split In VBScript
How do you get to use the Split function in VBScript??

DHTML Edit Control-Split Cells
I got how to insert table and cells in DHTML edit control. How to split cells so that it span 2 rows.

Thanks

Barcode Split Input Into Three Access Cells
Using Wasp barcode wedge between keyboard and computer. Reading code 128 into cell.

Data is in NNNNDDAAAA formatt where
NNNN=4 charactor Customer ID
DD=2 char month
AAAA=4 char payment amount

I want to split incoming barcode data into these three fields automatically. Any help is appcriciated.

Split() Function, The Way To Split Up String
People i do know that split is the way to seperate a string through a delimiter, but how shd i do to get the number of strings that is seperated from the string?

How to obtain the number of strings by using split()?



=======================================================

For those that wants to know basic usage of split():

Code:
Dim Str1 as Variant

Str1 = Split("Water-Melon", "-")

'Result will be:
'Str1(0) = "Water"
'Str1(1) = "Melon"

'To find how many splits u have

Dim int1 as Integer

int1 = Ubound(Str1) - LBound(Str1) + 1

Thank You, Lar_19.

Looping Through Cells For Empty Cells Dynamically - Excel VBA - Need Help
Hi,
I need to run a SQL like 'Select * from xyz where xyz.column1 in {all entries from cell A1: A10}
The criteria here - cells in column A would be loaded dynamically and I would not know how many columns are there (it could be say, A1 to A10 or A1 to A 40). So basically I need to start at A1 , increment column by 1 and look for empty cells. When an empty cell is found, all values from A1 to the one before the empty cell would be included in the where query.
i would like to achieve this and would need your help in getting this done.
Thanks.
-VS

Macro/Split Workbook
Here is what I am having trouble with:

What exactly I need help with is hard to explain but I will do my best.

I have an excel workbook file that I use as a tool to determine everybodyís annual pay increase. The workbook has three worksheets. The first worksheet is called "Data" the other two sheets are hidden but are called "Tables" and "Grades"

The data worksheet basically lists all of our supervisors that have hourly direct reports. There are input columns for the supervisors to rate their direct reports in certain areas. A score is generated from that input and that score determines what percent of market value the rate of pay should be. If the result is higher than the current rate of pay then the employee gets a raise. There are columns that perform this calculation that contain references to the hidden worksheets.

I have written few macros that appear to do what I need but the references to the other sheets are lost. And the other sheets themselves are not in the individual supervisors workbooks

Ok now here is what I am trying to do exactly:

The first sheet in the workbook "data" has a listing of all the supervisors and their direct reports. I need to split this workbook up by supervisor so that I have a separate but identical workbook for each supervisor with only their direct reports listed.


This is how I have been doing it manually:

1-open the 2006NE.xls file

2-Select the row that contains the supervisor that I am making a separate file for

3-delete all other rows for the other supervisors

4-Protect the Data sheet-allow users to enter information into unlocked cells only

5-File---->Save As-----> LastName.FirstName.xls (of supervisor)

6-Close "LastName.FirstName.xls"

7-Reopen 2006NE.xls and select the next supervisor

8-repeat steps 2-7 until a separate file has been created for each supervisor

I have attached the file if looking at it helps at all, I had to cut it down a little because of it size. The real one has 1750 different supervisors, so you can see why I need to find a way to automate the above process, doing that for everybody would be far too tedious

How To Use Macro(VBscript?) To Save A Doc??
I want to know is there any way to use macro to store a winword document?
Let say i have a winword document called testing.doc which is just finished editing in the winword, how can i store this document into hardisk and close this document by using macro??

How Do I Split One Large Macro Into 2 Smaller
I've built a spread sheet that compiles a yearly data and I get an error that states: compile error procedure too large. As of now I have a one click button that and all data is processed. Can I spit a macro and still have it as a one click data compilation? I attached the macro reduced to too areas any help would be a bonus.

Code:
Private Sub CommandButton23_Click()

' EOC Import Utility
'
' store name of current file
destfile = ActiveWorkbook.Name
' open file
inputfile = Application.Dialogs(xlDialogOpen).Show
If inputfile = False Then Exit Sub
inputfile = ActiveWorkbook.Name
ActiveSheet.Range("D1").Select
inputEOC = Selection.Value
Select Case inputEOC
Case "2003G"
CS_SAC
Case Else
' this displays a message if the value of cell D1 is not 2003G.
MsgBox "The file you selected was not a valid SISAC 2003G input file."
End Select
End Sub

'____________________________________________________________
Public Sub CS_SAC()
' update area page with scores
' part 1 other
Windows(inputfile).Activate
Worksheets("sheet name").Activate
Worksheets("sheet name ").Range("D4").Select
Selection.Copy
Windows(destfile).Activate
Worksheets("sheet name ").Activate
Worksheets("sheet name ").Range("d3").Select
Selection.PasteSpecial Paste:=xlValues

'-----Here is where I would probably split any ideas

'WP SI COMMENTS SECTION
Windows(inputfile).Activate
Worksheets("sheet name ").Activate
Worksheets("sheet name ").Range("B23").Select
Selection.Copy
Windows(destfile).Activate
Worksheets("sheet name ").Activate
Worksheets("sheet name ").Range("b46").Select
Selection.PasteSpecial Paste:=xlValues
OLDENTRY = Selection.Value
If OLDENTRY = "" Then Selection.PasteSpecial Paste:=xlValues Else Selection.Value = OLDENTRY & Chr(10) & NEWENTRY

'CLOSE SOURCE FILE
Windows(inputfile).Close
Windows(destfile).Activate
Worksheets("areas").Activate
Worksheets("areas").Range("A1").Select

MSG = "CS SAC Data Import Complete."
yep = MsgBox(MSG, vbOKOnly, "Super SAC Data Import")
End Sub

Macro For Merging Cells
Excel does not allow to merge cells keeping both its values,

can it be done so using a macro ?

if yes how ?

thanks

How Can I Execute One String Selected From DB And Not Treat It As String?VBScript.
How can I execute one string selected from DB and not treat it as string?VBScript.

For example:
One record in the table is session("name").and I want to show the session variable's value.

thanks

Word Macro + Find/Replace + VBScript
Below is the code that i wrote to open a file, find some text, replace it and save the file under a different name, however it doesn't work properly, can anyone please help in fixing this problem. Thank you

Dim W
    Set W = CreateObject("Word.Application")
    set doc = W.Documents.Add
    w.Documents.Open "c:PaymentLetter.dot"
    
    w.Visible = True

   With w.Selection.Find
    .ClearFormatting    
    .Replacement.ClearFormatting
           .Text = "##brk_st_nm##"
    .Replacement.Text = "igor"
    .Text="##MgmtFee##"
    .Replacement.Text = "Hello"
    .Forward = True    
    .Wrap=wdFindContinue
    .Format = False
        .MatchCase = False
    .MatchWholeWord = False
        .MatchWildcards = False
    .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With

    w.Selection.Find.Execute


        ' Save the document.
    doc.SaveAs "c:PaymentLetter1.doc"
        ' Quit Word.
'    w.Quit
        ' Clear the variable from memory.
'    Set w = Nothing

Running Macro On Selected Cells Only
I want to run a macro which retrieve the cell values of selected cells only. How can I do this only on selected cells?

Thanks in advance

Useful Macro That Aligns Cells, Looking For One Improvement
Hi -

I am posting this again because I now have a useful macro others might be able to use to clean up their spreadsheets, although it could use one improvement and I am looking for help doing the finishing touches. (the working code is below)

(Mark, Mike, Wamphryi (and all the others)- many many thanks for your help, the code you posted works wonderfully. So, of course I have to come back to the well ..;-)

The original problem was lining up cells in multiple rows with column headers. Thanks to Mark, I've been able to create a form that does that with operator review. It works like a charm.

The next step I'd like to incorporate would be to create the first row with pre-defined column headers - that's easy, I can just type in the column names. The tricky part would be to look in the rows below for the first occurence of the matching string and parse the cell, pushing the matching string and all following cells right till it aligned with the column name. That way the operator doesn't have to do anything

Let's suppose that the text string "Ford Taurus: Sedan" was all in the first cell in Row 2, with Column A in the header Labeled "Ford Taurus" and Coulmn B was labeled "Sedan", etc. What I want to have happen (automatically) is have the textstring in the second row be parsed, so that "Sedan" and all the text that followed (as wells as following cells) would be pushed right till it aligned with column B -"Sedan".


A1_________B1____C1____D1______E1
Ford Taurus Sedan 1987 Applies Part Number (Column names)



A2________________________ B2__C2__D2___E1
Ford Taurus Sedan 1987 Applies (String before parsing, all in Row 2 cell1)



A1________ B1____C1____D1_______E1
Ford Taurus Sedan 1987 Applies Part Number (Column names)
Ford Taurus Sedan 1987 Applies <empty> (string after parsing)
Ford Taurus Sedan <empty>Applies 01357 (2nd row after parsing)


Mark's code works wonderfully, but I have to keep looping it manually. Many of these files are of such regularity, that it could be done without operator review. The only requirement is that as soon as the first match was made, it parsed the cell and moved on to find the next search string (coming from the column name) in the text in the row. If nothing was found it would move on to the next cell in the row.

It seems like a simple mod to the code would work, with a loop, that grabbed the search text from A1, B1, C1, and used it as the match string sequentially, and so forth, but for the life of me I cannot get it going.

The loops I've tried do not work... and after an all night session, the best I can do, well, it still stops at the first time a row doesn't have the search text. I also can't seem to get a cell to move more than one column right, till it aligns with the column header. This is caused by the empty cells.

Any suggestions would be greatly appreciated!

(here is my current code wwhich works very well, although it requires constant manual intervention):

********************************************************************** **************************
Private Sub CommandButton1_Click()

Dim colSearch As String, colAdd As String
Dim ra As Range, rng As Range, s As String, i As Integer, r As Integer, c As Integer
Dim col, cols As Integer
colSearch = TextBox2
colAdd = TextBox3
col = Range(colSearch & "1").Column ' set the column to search here
cols = Range(colAdd & "1").Column
Dim SearchText As String


SearchText = TextBox1.Value


Set ra = Range(Cells(1, col), Cells(65536, col).End(xlUp))
For Each rng In ra
r = rng.Row
c = rng.Column
s = rng.Value
i = InStr(s, SearchText)

'I've used 2 approaches here depending on the location of the search string in
'the text as this appears to be what you are after from your original post


If i = 1 Then
Cells(r, cols).Insert xlShiftToRight
Cells(r, c).Value = Trim(Left(s, i + Len(SearchText) - 1))
Cells(r, cols).Value = Trim(Right(s, Len(s) - i - Len(SearchText) + 1))
ElseIf i > 1 Then
Cells(r, cols).Insert xlShiftToRight
Cells(r, c).Value = Trim(Left(s, i - 1))
Cells(r, cols).Value = Trim(Right(s, Len(s) - i + 1))
End If
Next



Unload UserForm2

End Sub

Private Sub CommandButton2_Click()
Unload UserForm2
End Sub


********************************************************************** **************************

Thanks in advance, John

Word Macro - Splitting Cells
When I do the following line in Visual Basic to interact with Word:

oSel.Cells.Split numRows:=1, NumColumns:=2, MergeBeforeSplit:=False

It behaves differently when I set Word invisible than it does when I set Word visible. When visible it splits the cell where the cursor is at (which is what I want), but when invisible it makes a new column for that row tagged on at the end of the row. Why is this and how can I control it?

How Could I Create Cells Like Excel Cells
how could i create cells like excel cells

is there any tool do this , cause i want to make in my form a table like excel spread sheats

Storing Worksheets/many Cells To Allow Undo Of A Macro?
Hello all from a newb,

I did try searching on this topic, but didn't find anything easily - sorry if it's a common problem, as I don't really know this site!

I've written an excel macro to modify relatively large sets of data - up to thousands of rows and tens of columns. I recently came across a site describing how to add undo support to visual basic subs ( http://j-walk.com/ss/excel/tips/tip23.htm ), but that stores the values of a selection.

I'm guessing that stepping through every cell in a selection numbering thousands, and copying the value and formula of that cell, will take quite some time!

So is there any easy way to store an entire workbook in a public variable, so I can restore it if necessary? At the moment we save the doc before running the macro, and then revert if necessary, but that's not a great solution. There must be a way to copy the entire worksheet easily.... right?

Thanks in advance for any help anyone can give

Macro To Display A Named Range Of Cells
I have named a cell range on my worksheet but do not know the command in VB to display this cell range on a button click.

I understand how to make the button, it is mostly the VB functionality that I am confused with.

My named range of cells is "NecessaryCells" I want to create a macro that will filter out all cells other than the "NecessaryCells."

Any help would be very much appreciated, I assume this is a pretty simple question for most of you professionals to answer

Thanks in advance for any help you can give me.

-Mike
a.k.a The Ninja Coder

Need To Run A Macro To Convert Cells As Advised By Microsoft
When linking an excel spread sheet into access the first 8 rows of the excel colum determine the access datatype in the table.

so If you have
123
1235
2345
67856
5656
DB54

it takes the average of the first eight cols and make this the type in access so anything with text further down shows as an error in the access table.

so in access I would get
123
1235
2345
67856
5656
# NUM !

This took me ages to uncover.
MS suggest running a macro in excel before linking the spread sheet to access. this is the macro they suggest

sub addspace

dim cell as object
for each cell in selection
cell.value = " " & cell.value
cell.value = right(cell.value, len(cell.value)-1)
end sub

as you can see this is adding a black to every row in the colum so the colum is made into a text string instead of the number entry, this way when the link is done everything will now be read in allowing anything below the eigth row to be imported if it is a text.

My problem is the spreadsheet I wish to link will be different every month as it is produced monthly then requires linking to the data base. So if I create the macro in the January spreadsheet then when the febuary one gets sent it will not have this macro in !
I need to run this code above from the VB interface that will add the space to the spreadsheet before I link it !
I need a way to do this or run a macro on a spreadsheet from VB. How would I do this , any suggestions would be great.

SPLIT From: Split A String? VB6
Quote: Originally Posted by zynder Here is a simple version.


Code:

Private Sub Command1_Click()
Dim myStr As String
Dim xPos As Integer

myStr = "This:is:a:test:string"
xPos = InStr(1, myStr, ":")

Debug.Print Mid$(myStr, 1, xPos - 1)
Debug.Print Mid$(myStr, xPos + 1, Len(myStr))

End Sub



Hope it helps!

I found this thread searching for the split() function. Is there a way to create fields in a database (or a brand new dbase) based on the string being split?

Converting An Excel 2002 Macro To An Excel 95 Macro
Hi there, Iíve finally got my macro program to work (thanks everyone). Now I have to load the macro on a much older machine. I wrote the Macro on Excel 2002 and now Iím trying to run it on Excel for Windows 95 Version 7.0. Iíve loaded it up, but there are all sorts of errors. Does anyone know of a simple way to convert the macro code so it will run smoothly on Excel 7.0?

Protecting Cells With Password + Automaticly Remove The Macro's
Hi, I have a spreadsheet with a macro. The macro contains some sub's. On the last sub i have recorded to protect some cells and some not. When i run this macro it automaticly get some values. I must do the same handling 60 TIMES and sen this speadsheet to 60 consumers. What i want is that i will NOT send the MACRO, how can i realise this?, I need this macro to get values and then i want to send it to the consumers, only the MACRO's i will not send. How can i make when the macro has that this delete hisself or something. Another problem is that i want a password protection, how can i do this in VB-script, when i record a macro to password protect it will not work, the password is not setting. I must do this 60 times you know how much time it needs to set the password 60 times with the hand, could we also protect the cells with a password in VB.

Could somebody describes what i can do?

How Do I Get A Macro To Select A Cell Depending On Another Cells Data
this problem may be a simple one but as im not very good in visual basic i cannot think of a function to use.
here is an example of my problem.

I have a cell number in one cell.
Cell G1 has the data D5 in it.
And i need a macro to select cell D5 by using the info it found in cell G1.

Sorry if that isnt very clear but i cant think of any other way of explaining it. please help.

Protecting Cells With Password + Automaticly Remove The Macro's
Hi, I have a spreadsheet with a macro. The macro contains some sub's. On the last sub i have recorded to protect some cells and some not. When i run this macro it automaticly get some values. I must do the same handling 60 TIMES and sen this speadsheet to 60 consumers. What i want is that i will NOT send the MACRO, how can i realise this?, I need this macro to get values and then i want to send it to the consumers, only the MACRO's i will not send. How can i make when the macro has that this delete hisself or something. Another problem is that i want a password protection, how can i do this in VB-script, when i record a macro to password protect it will not work, the password is not setting. I must do this 60 times you know how much time it needs to set the password 60 times with the hand, could we also protect the cells with a password in VB.

Could somebody describes what i can do?

Copyright © 2005-08 www.BigResource.com, All rights reserved