VBA question

A co-worker, who has since moved on to greener pastures, coded the following 
for me.  I use it to explode the contents of partlists, where all the part 
numbers of a particular type are stored in a cell.  Now I am receiving the 
lists from new sources, and need to update the code a bit, something I am 
completely unfamiliar with.

In this statement   If InStr(strNewNumber1, " ") > 0 Then how would I also 
include (TAB) and other pseudo space charecters, so it will work on files 
produced by people who refuse to use the space bar?

Function Explode()

Dim strRow As String
Dim strNewNumber1 As String
Dim strNewNumber2 As String
Dim intPosition As Integer

strRow = 2

Do Until Range("B" & strRow).Value = ""
    
    Range("B" & strRow).Select
    strNewNumber1 = Trim(Range("B" & strRow).Value)
    If InStr(strNewNumber1, " ") > 0 Then
    
        intPosition = InStr(strNewNumber1, " ")
        strNewNumber2 = Left(strNewNumber1, intPosition - 1)
        strNewNumber1 = Trim(Mid(strNewNumber1, intPosition))
        
        Rows(strRow & ":" & strRow).Select
        Selection.Copy
        Rows(strRow + 1 & ":" & strRow + 1).Select
        Selection.Insert Shift:=xlDown
        
        Range("B" & strRow).Value = strNewNumber2
        Range("B" & strRow + 1).Value = strNewNumber1
        
    End If
    
    strRow = strRow + 1

Loop


End Function
0
Utf
11/17/2009 9:28:06 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
605 Views

Similar Articles

[PageSpeed] 27

You could try

InStr(strNewNumber1,chr(9))

for tabs.
-- 
HTH,

Barb Reinhardt



"Tony in Michigan" wrote:

> A co-worker, who has since moved on to greener pastures, coded the following 
> for me.  I use it to explode the contents of partlists, where all the part 
> numbers of a particular type are stored in a cell.  Now I am receiving the 
> lists from new sources, and need to update the code a bit, something I am 
> completely unfamiliar with.
> 
> In this statement   If InStr(strNewNumber1, " ") > 0 Then how would I also 
> include (TAB) and other pseudo space charecters, so it will work on files 
> produced by people who refuse to use the space bar?
> 
> Function Explode()
> 
> Dim strRow As String
> Dim strNewNumber1 As String
> Dim strNewNumber2 As String
> Dim intPosition As Integer
> 
> strRow = 2
> 
> Do Until Range("B" & strRow).Value = ""
>     
>     Range("B" & strRow).Select
>     strNewNumber1 = Trim(Range("B" & strRow).Value)
>     If InStr(strNewNumber1, " ") > 0 Then
>     
>         intPosition = InStr(strNewNumber1, " ")
>         strNewNumber2 = Left(strNewNumber1, intPosition - 1)
>         strNewNumber1 = Trim(Mid(strNewNumber1, intPosition))
>         
>         Rows(strRow & ":" & strRow).Select
>         Selection.Copy
>         Rows(strRow + 1 & ":" & strRow + 1).Select
>         Selection.Insert Shift:=xlDown
>         
>         Range("B" & strRow).Value = strNewNumber2
>         Range("B" & strRow + 1).Value = strNewNumber1
>         
>     End If
>     
>     strRow = strRow + 1
> 
> Loop
> 
> 
> End Function
0
Utf
11/17/2009 9:41:01 PM
 > Dim strRow As String
 > strRow = 2
 > strRow = strRow + 1

As a side note, you might want to reconsider changing this to a Long 
data type, and using Cells(Row,Column) instead.
= = = = =
Dana DeLouis



On 11/17/09 4:28 PM, Tony in Michigan wrote:
> A co-worker, who has since moved on to greener pastures, coded the following
> for me.  I use it to explode the contents of partlists, where all the part
> numbers of a particular type are stored in a cell.  Now I am receiving the
> lists from new sources, and need to update the code a bit, something I am
> completely unfamiliar with.
>
> In this statement   If InStr(strNewNumber1, " ")>  0 Then how would I also
> include (TAB) and other pseudo space charecters, so it will work on files
> produced by people who refuse to use the space bar?
>
> Function Explode()
>
> Dim strRow As String
> Dim strNewNumber1 As String
> Dim strNewNumber2 As String
> Dim intPosition As Integer
>
> strRow = 2
>
> Do Until Range("B"&  strRow).Value = ""
>
>      Range("B"&  strRow).Select
>      strNewNumber1 = Trim(Range("B"&  strRow).Value)
>      If InStr(strNewNumber1, " ")>  0 Then
>
>          intPosition = InStr(strNewNumber1, " ")
>          strNewNumber2 = Left(strNewNumber1, intPosition - 1)
>          strNewNumber1 = Trim(Mid(strNewNumber1, intPosition))
>
>          Rows(strRow&  ":"&  strRow).Select
>          Selection.Copy
>          Rows(strRow + 1&  ":"&  strRow + 1).Select
>          Selection.Insert Shift:=xlDown
>
>          Range("B"&  strRow).Value = strNewNumber2
>          Range("B"&  strRow + 1).Value = strNewNumber1
>
>      End If
>
>      strRow = strRow + 1
>
> Loop
>
>
> End Function
0
Dana
11/17/2009 10:09:07 PM
Reply:

Similar Artilces:

Document Properties in header / footer without VBA
Hello, I was looking for way to insert document properties, also maybe custom doc properties into the header or footer of Excel WITHOUT the usage of vba macros. e.g. something like if you would like to insert the date &[date] I would like to use &[Author] but excel doesnt accept stuff like this. is there a way?! somehow. thanks a lot hans Hi not possible without VBA. So no way to do this witout macros -- Regards Frank Kabel Frankfurt, Germany hans werner wrote: > Hello, > > I was looking for way to insert document properties, also maybe custom > doc properties into ...

Exchange 2003 Question #3
Dear all, I have upgraded from Exchange 5.5 and notice that I miss the feature where I can clean/delete mail based on certain criteria from users mailbox via the System Manager. Is this feature still available in Exchange 2003, If so, where is it? Thanks. Regards, Kueh. You can use ExMerge to remove emails based on certain criteria (ie. attachment name, subject, etc). http://www.msexchange.org/tutorials/MF013.html "KA Kueh" wrote: > Dear all, > > I have upgraded from Exchange 5.5 and notice that I miss the feature where I > can clean/delete mail based on ...

Question On Fields
What is the (Microsoft desciption)difference between the SIC field and the Industry Code field? To me they seem like a duplication. Thanks! Shauna Hi, According to the Deployment Manager descriptions of attributes: SIC - Standard Industrial Classification code for the Account industrycode - the type of industry with which the account is associated... Hope this helps! "Shauna Koppang" <anonymous@discussions.microsoft.com> wrote in message news:034101c48af4$62524a70$a401280a@phx.gbl... > What is the (Microsoft desciption)difference between the > SIC field and the...

newbie question
Excel help says it should be a tab under tools, but I can't see it in my version. I would appreciate any help. Thanks. You must first load the Solver Add-in through Tools>Add-ins. Gord Dibben MS Excel MVP On Sat, 03 Nov 2007 07:46:34 -0700, analyst41@hotmail.com wrote: >Excel help says it should be a tab under tools, but I can't see it in >my version. > >I would appreciate any help. > >Thanks. On Nov 3, 10:56 am, Gord Dibben <gorddibbATshawDOTca> wrote: > You must first load the Solver Add-in through Tools>Add-ins. > > Gord Dibben MS E...

REQ: Can Someone Help Me With This Outlook XP Question?
Hello All: I use Word to edit my e-mail msgs in Outlook XP. I had to reinstall Office the other day and now whenever I want to start a new e-mail or reply to an e-mail I get a warning that comes up: "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected it may be a virus and you should choose 'No'" There is a box that asks for the amount of time to allow the access: 1 to 10 minutes. Do I have to have specific settings for my Outlook address book? I use the Contact area in Outlook for addresses. I have ...

Baseball Stats question: How can I get the RBI's?
I have a data table that looks like this: Code ------------------- STR-S SNK-S STR-K STR-F SNK-S STR-3 STR-2 STR-F SNK-S SNK-4 SNK-S STR-3 SNK-F STR-4 STR-O ------------------- "STR", etc at the beginning are pitch types, SNK is Sinker for example The end character is the result of the pitch, a 4 would be a home run, 3 a triple, and so forth. O is out, F is foul, S is strike --- you ge it. How can I figure the amount of RBI's? I know that it's 5, but I can' think of any automated way to calculate this, anyone got any ideas? THANK YOU!! :confused -- AVER...

Formula Question #18
I have built a workbook in which I have inserted a formula to tell me whether the contents of a supply bin needs replenishment or not. The formula I used is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to see what parts have been used, which becomes a new sheet in the workbook. Now, I want to add a formula that, whenever it sees "REPLENISH!," it will back through the workbook to count whether that same part needed replenishment on consecutive previous days. If it has, then the latest worksheet will report the number of days that ...

question about using the correct schema namespace
I have a Access/VBA client that exports xml to the local drive, then posts it over http to an aspx page. The aspx page consumes it, then builds itself based on the xml data. This works find only if I first mannually change the root entry's namespace url as shown below. Does anyone know how I can get the two (cleint xml export and aspx xml consume) to work together with the appropriate namespace? Here is the top three lines of my xml export (prior to mannually changing it): <?xml version="1.0" encoding="UTF-8"?> <root xmlns:xsd="http://www.w3.org/200...

AD/Network design question
Hello all I have a 2 sites which users come and go from. These sites have 2 different network ip domains (192.168.1.x and 192.168.2.x). Users need to be authenticated using the same user id and password at both sites. DC/GC is at 192.168.1.x and is a SBS 2003. Server at 192.168.2.x is Server 2003. When the server at 192.168.2.x is connected via vpn to 192.168.1.x all is well. How do I get the server at 192.168.2.x to act as a AD/DC when it’s not connected to 192.168.1.x ? Thank you Hal I think you will run here into the limitations of SBS :-( @ SBS experts : ...

Mail merge & staple question
Does anyone know how to use mail merge in Publisher with a printer that folds and staples?? Publisher is sending it to the printer as "one" big file and trying to staples "all" my newsletters together instead of individual ones with the addresses on them. Or if anyone has any other programs or ideas on how I can accomplish this task, sure would be appreciated. Thanks While waiting for decisions from his 6 university choices, Ed sees a message from Parishsecretary <Parishsecretary@discussions.microsoft.com>. On it is written: > Does anyone know how to use mai...

Office 2010 Buying Question Assistance Needed
I've been looking through the MS Office 2010 web site to try to determine what my new small company would require, but I can't find the information I need. We for sure would need Office Pro Plus, but other than that I'm not sure. We want to run it on our own server. We will initially have 3-5 people using it and perhaps more later on. Would we need to purchase site licensing? Unfortunately, our programmers are MS haters (I'm not) and I can't get any assistance from them on this, but I have power of the pen. I would appreciate any assistance I can get. Th...

Lognormal in VBA
I'm trying to find a lognormal fuction for MS Access so I can . I can see the built-in one in Excel but I don't want to have to use an Excel app in my code just to use this worksheet function. I can find the VBA code for a normal distribution function but I want to find a coded version of the lognormal function. Does anybody have any ideas where I can find one? Cheers Lee DaviesL wrote: > I'm trying to find a lognormal fuction for MS Access so I can . I can > see the built-in one in Excel but I don't want to have to use an Excel > app in my code just to use this...

Question About Missing Data
So, this is probably really easy, but I just want to ask and see if I may be missing something here. Some data on vendor numbers changed. Let’s say IBM used to have a vendor number 12345 and now it’s vendor number is 56789. I can identify IBM as IBM, but I really want to use the number, not the name. Should I set up a table that ties the numbers together, so that Access knows 12345 = 56789? Or, should I do some kind of Update Query and change all incidences of 12345 to be 56789? Or, is there some other, method, like a ‘best practices for missing data’? Thanks! Ryan--- ...

Custom CRect Question
I have a custom rectangle class that inherits from CRect: class CCustomRect : public CRect { private: CPoint m_maxPt; COLORREF m_color; TCHAR text[50]; public: CCustomRect(RECT* source, CPoint pt, COLORREF rgb); RECT* RectBase(); void Update(RECT* r); } Inheritance has worked well until I found myself needing to create the RectBase function (above) to return the rectangle dimensions. CRect does not seem to have any methods that can be called to return the base class's RECT value. I could take CRect::Size and construct a rectangle to return, but this seems a bit much. My...

Excel link update question
I need to maintain an excel workbook which contains a lot of links to other workbooks. Since the linked workbooks change every week, I need to change all the links accordingly. For example, a cell with formula "='[aug_28.xls]sheet1' !A10" will be changed into "='[sep_4.xls]sheet1'!A10". I tried to do this with Find/Replace. However, the link is updated every time it is changed. The link updating takes a couple of seconds. So it may take a hour to finsh it for a workbook with thousands of links. Could anyone tell me how to shut down the link updating when...

VS 2005 beta question
All, I need to complie some code. I am going from 16bit to 64 bit. The job do not want to spend the money on VS 2005.net. If I compile the code with the beta version or trial version of VS 2005.NET will it time bomb on me? Thanks in advance The code won't, but eventually the compiler will stop working. Don't know if the trial will support 64 bit though. I guess you get what you pay for. Tom "karibbean" <sharp_mind@REMOVE.this.email.msn.dotcom> wrote in message news:%23OyZFNUFGHA.1124@TK2MSFTNGP10.phx.gbl... > All, > > I need to complie some cod...

Payables, Financial Batch and other questions
Please bear with me I have a lot of questions and the help of those who are experienced and knowledgeable would be well appreciated. Thanks. MISSING TABLES 1. I ran hotfix for GP9 but I decided not anymore to continue with it because there were other requirements. Thus, I reinstalled the applicaiton GP9 and restored my backup dynamics & company database. There were tables that were lost when I restored it. I just want to know how to copy the tables to my current database. Most of the tables that were lost were from the Cashbook (i.e. CB900045, CB200001) and other tables such as SY...

vba code to enable macros
Hi, Can we write a vba code to enable macros. In most of the sites it says we cant do that . The only thing we can do is change the macro setting or work around is hide the sheet which has macros. But none of them will work in my case. Can we change an excel security setting to from macro on open so that macros are enabled. No. If we can do that with code, then so could a hacker with bad intentions. Mike F "varsha12" <varsha12@discussions.microsoft.com> wrote in message news:DBEB1614-C956-491B-B351-02DD9F711891@microsoft.com... > Hi, > Can we w...

Why do my questions get deleted sometimes?
Apologies, I have posted this on the worksheet functions group too... I love the feedback I get from these groups, but every now and then, my messages get deleted. I'd like to know what I'm doing wrong. Many of my messages ARE posted and answered, and I'm grateful for that, but one or two get removed - regardless of how often I post them. I don't swear or say anything bad about Excel - I love it. So why don't they get posted are there some words or subjects that I should avoid? Will this one get deleted - it's a bit off subject? But I'd love to have an ans...

Named ranges
Excel 2003 I have a total sheet that gets data from three different shift sheets (all in the same workbook) for a monthly report. I have named ranges on each shift sheet for each month's production data (S1M1, S1M2, S1M3, S2M1...) where S1M1 =A2:AC147, S1M2 = A148:AC329, etc. The old formula was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147). I'm stumped as to what the new formula would be - how would I direct Excel to column L of S1M1 to search for "BB", then add the contents of column T? The range changes each month, and I tho...

VBA using an image as a toggle switch
Hi hope you can help. I know next to nothing about VBA and have adapted code below from stuff I had. I simply want to show hide portions of my page to cut down on screen clutter/information overload. I have assigned my macro to a screen image but was wondering if I could have a hide and unhide under the same button. Any help would be much appreciated Call Unl Application.ScreenUpdating = False Rows("25:75").EntireRow.Hidden = True Rows("98:148").EntireRow.Hidden = True Rows("171:221").EntireRow.Hidden = True Rows("244:294").EntireRow.Hidden = T...

question on rules on unattended mailbox
I have an unattended mailbox that sends emails to some customers. sometimes the email is not in the sytem correctly, and you get a NDR message. I have created a rule in the mailbox to forward the NDRs to a certain user, but was wondering if we have to open outlook for that unattended mailbox to process its rules? > I have an unattended mailbox that sends emails to some customers. > > sometimes the email is not in the sytem correctly, and you get a NDR > message. > > I have created a rule in the mailbox to forward the NDRs to a certain > user, but was wondering...

A question about CToolTipCtrl.
Hi All: Chinese English following! I add a CToolTipCtrl member in my dialog, and create it in OnInitDialog function, at last, I add a CEdit control to CToolTipCtrl member.When the CEdit control is enabled, tool tip can pop up normally, but when the CEdit control is disabled, tool tip can't pop up. How can I solve this problem? Thanks. Fiveight Take a look at http://www.codeguru.com/forum/showthread.php?t=309050&highlight=CToolTipCtrl+disable -- If the message was helpful to you, click Yes next to Was this post helpful to you? Regards, Goldbach "fiveight" wrot...

VBA code (rules) won't work after reboot
First off I am not very knowlegeable about Outook or VBA coding so I apologize in advance for missing the obvious. I have created some rules in outlook (actually exported/imported from a functioning account). The rules basically: -look for an email with an attachment and with a specific word in the subject line -move the email to a different folder -copy the attachment and place it in a shared system folder (done with VBA code) The problem I have is the rules will only work until Outlook is closed down or the server is rebooted. After that the rules partially work (the VBA...

VBA Reference Book?
I'm happily using Excel 97 and see no compelling reason to upgrade it. For some time I've been creating a few VBA functions just from kind of hacking at it and using my knowledge of Basic from 25 years ago and Excel's help file and some persistence. The time has come to break down and buy a book though. Toward that end I have two questions: 1) What VBA reference book do you folks find most useful? With listings of all the VBA commands, some examples, etc. 2) Most of the books available today are based on VBA for Excel 2003 of course. Is that significantly different from...