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
401 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

690 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