Solved

Need Excel VBA Code to parse a short string of values from one column conditionally into other columns based on string value

Posted on 2015-02-16
8
392 Views
Last Modified: 2016-02-11
Using:  Excel 2010

I have a large worksheet (see attached excerpt) with strings in one column which look like:

1,2,3
0,1,4
5,6,7,8

Let's assume these strings define whether some modification needs to be applied to a particular building number like B0, B1, through B8.

I'm needing to apply a 'Y' (for 'Yes') into a corresponding column (there is a column for B0 through B8) which will make it easier to subsequently to sorts on those columns by building number.

I'm not familiar enough with VBA yet to write this code quickly and could use some help.

Thx


A look at the attached spreadsheet sample should make the above goal/need clear.
Conditional-Parser-VBA-Needed.xls
0
Comment
Question by:qeng
  • 4
  • 4
8 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 375 total points
ID: 40613264
Hi, this procedure should work for you.

Regards,

Rob.

Sub ParseValues()
    For intRow = 8 To Cells(65536, "A").End(xlUp).Row
        strValue = Trim(Cells(intRow, "A").Value)
        If strValue <> "" Then
            arrCols = Split(strValue, ",")
            For Each intCol In arrCols
                intCol = Int(intCol)
                Cells(intRow, intCol + 2).Value = "Y"
            Next
        Else
            For intCol = 0 To 8
                Cells(intRow, intCol + 2).Value = ""
            Next
        End If
    Next
End Sub

Open in new window

0
 

Author Comment

by:qeng
ID: 40613295
Thanks so much Rob.  Worked like a charm (saved me hours of work).

How would I modify this script to be used in workbook with many worksheets with this same layout (the source string column is always followed by the target 8 columns) but request the user to click on the starting cell.

This arrangement (string in one column, followed by the 8 target columns) is common to many of the worksheets but the starting column (which I made as 'A' in the excerpt I attached previously) could be column G in one worksheet or column X in another depending on which columns users may have added to a particular worksheet.

Maybe some sort of a 'Select Range to Parse' message to the user which requires the user to go to the desired worksheet (which may not be the active one from which the Sub ParseValues() is called) and click-drag the range to parse (probably a good idea to add a 'range confirmation' request to the user also, prior triggering execution, so it doesn't start parsing on the wrong range).

Thx.
0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 375 total points
ID: 40613339
OK, see how this goes for you.  Note that you need to select the cells in the Building column for this to work.

Sub ParseValues()
    Set MyRange = Selection
    intReturn = MsgBox("Are you sure you want to run the parser on the selected range of " & Replace(MyRange.Address, "$", "") & "?", vbYesNo)
    If intReturn = vbYes Then
        For Each objCell In MyRange
            strValue = Trim(objCell.Value)
            If strValue <> "" Then
                arrCols = Split(strValue, ",")
                For Each intCol In arrCols
                    intCol = Int(intCol)
                    objCell.Offset(0, intCol + 1).Value = "Y"
                Next
            Else
                For intCol = 0 To 8
                    objCell.Offset(0, intCol + 1).Value = ""
                Next
            End If
        Next
    End If
End Sub

Open in new window


Regards,

Rob.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Assisted Solution

by:qeng
qeng earned 0 total points
ID: 40623586
Hey Rob,

Either I'm doing something wrong in how I'm selecting the Building Range (I've tried it a few different ways) or we need an offset to the right by 1.

I first select the range to parse immediately under the Table Heading (in our case from A8:A205) then when I run the second VBA script you gave me, what ends up happening is it parses the text in A8:A205 but it parses it one column to the left of where it should go.  

So what happens is no data appears in the B8 column, the B8 data is instead in the B7 column, and the data which should have gone into the B0 column ends up overwriting the original text string in the A column (this text string should remain intact).

Looks like I was able to fix it by changing:

If strValue <> "" Then
                ...
                    objCell.Offset(0, intCol).Value = "Y"
                Next
            Else
                For intCol = 0 To 8
                    objCell.Offset(0, intCol).Value = ""
                Next
            End If


to:

If strValue <> "" Then
               ...
                    objCell.Offset(0, intCol + 1).Value = "Y"
                Next
            Else
                For intCol = 0 To 8
                    objCell.Offset(0, intCol + 1).Value = ""
                Next
            End If

Was this the correct way to fix it?  It works but I wanted to check just in case there is a more correct way.
0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 375 total points
ID: 40623946
Hi qeng, you are exactly right.  My apologies, I didn't notice I had forgotten to increment the offset, because your values start at zero.

That should work fine for you.

FYI, I have updated my code snippet to reflect your modification.

Regards,

Rob.
0
 

Author Comment

by:qeng
ID: 40624348
All good Rob.  Thanks a lot for your help.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 40624577
No problem. Glad to help.

Rob.
0
 

Author Closing Comment

by:qeng
ID: 40641455
Great support.  Rob was quick and stayed tuned in through the end.  Many thanks.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question