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
384 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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 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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now